> For the complete documentation index, see [llms.txt](https://docs.ovaledge.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.ovaledge.com/release8.1/connectors/connector-repositories/rdbms/postgresql/postgresql-lineage.md).

# PostgreSQL - Lineage

This article outlines the lineage coverage, configuration settings, metadata handling, supported scenarios, component behaviors, process flow, and known limitations for lineage extraction in PostgreSQL.

The PostgreSQL connector provides automated, production-ready lineage extraction from SQL-based datasets and query logs. It enables end-to-end visibility into data flow by tracing relationships from source tables through SQL transformations to target objects. The connector supports both table-level and column-level lineage, helping users understand data movement, perform impact analysis, troubleshoot dependencies, and support governance initiatives.

## Lineage Configuration Requirements

Accurate lineage extraction depends on specific configuration settings, access permissions, and metadata availability. Configure the following settings correctly to ensure successful lineage extraction and relationship mapping. 

### Configuration Requirements Table

<table><thead><tr><th width="219">Configuration Area</th><th>Required Detail</th></tr></thead><tbody><tr><td>Lineage Build</td><td>Enable lineage generation through the Build Lineage job setting.</td></tr><tr><td>Authentication</td><td>Configure valid PostgreSQL database connection credentials.</td></tr><tr><td>Access Requirements</td><td>Ensure read access to tables, views, stored procedures, functions, and query logs. Access to dataset SQL or query text is required for lineage extraction.</td></tr><tr><td>Function Lineage Processing</td><td>Configure function and procedure lineage expansion using OEConfig.PROCESS_FUNCTION_RELATED_LINEAGE.</td></tr><tr><td>Relationship Extraction</td><td>Configure join and relationship extraction using OEConfig.LINEAGE_BUILD_RELEATIONSHIPS.</td></tr><tr><td>Optional Lineage Settings</td><td>Use OEConfig.LINEAGE_SHALLOW for fallback lineage generation and OEConfig.HANDLE_TEMP_TABLE_WITH_PREFIX for temporary table handling.</td></tr></tbody></table>

{% hint style="info" %}
Stored procedure and function lineage require access to the corresponding source definitions. Query-log lineage requires access to PostgreSQL query logs.
{% endhint %}

{% hint style="warning" %}
Missing SQL definitions, insufficient permissions, or disabled lineage settings may prevent the connector from building complete lineage relationships.
{% endhint %}

## Lineage Components

| Component               | Availability |
| ----------------------- | :----------: |
| Tables                  |       ✅      |
| Columns                 |      ⚠️      |
| Views                   |       ✅      |
| SELECT Queries          |       ✅      |
| INSERT Queries          |       ✅      |
| UPDATE Queries          |      ⚠️      |
| DELETE Queries          |      ⚠️      |
| MERGE / UPSERT          |      ⚠️      |
| Stored Procedures       |      ⚠️      |
| Functions               |      ⚠️      |
| Query Logs              |       ✅      |
| Join Relationships      |      ⚠️      |
| Temp Tables             |      ⚠️      |
| Materialized Views      |       ❌      |
| Reports / BI Objects    |       ❌      |
| Expressions             |      ⚠️      |
| Aggregations            |      ⚠️      |
| Column Aliases          |       ✅      |
| Derived Columns         |      ⚠️      |
| Renamed Columns         |       ✅      |
| Temp Columns (fallback) |       ✅      |
| Star (\*) Expansion     |      ⚠️      |
| Function-based Columns  |      ⚠️      |

{% hint style="info" %}
The ⚠️ icon indicates partially supported functionality with limited lineage coverage in applicable scenarios.
{% endhint %}

## Supported Use Cases

The PostgreSQL connector supports lineage extraction across SQL-based datasets, query execution patterns, views, and data movement operations. These scenarios represent the areas where lineage extraction functions as expected and produces reliable lineage relationships.&#x20;

### Supported Lineage Scenarios

<table><thead><tr><th width="220.3333740234375">Supported Scenario</th><th>Details</th></tr></thead><tbody><tr><td>Table-to-Table Lineage</td><td>Supports lineage extraction from SQL statements that read data from source tables and write to target tables through SELECT and JOIN operations.</td></tr><tr><td>INSERT INTO > SELECT Lineage</td><td>Tracks data movement from source tables to target tables through INSERT INTO >SELECT statements.</td></tr><tr><td>View Lineage</td><td>Extracts lineage from CREATE VIEW statements and maps views to their underlying source tables and columns.</td></tr><tr><td>Column-Level Lineage</td><td>Supports direct column mappings, aliases, and source-to-target column relationships.</td></tr><tr><td>Join Relationship Extraction</td><td>Identifies and maps relationships between tables used in JOIN operations.</td></tr><tr><td>Query-Log Lineage</td><td>Extracts lineage from PostgreSQL query logs to trace database query activity and data flow.</td></tr><tr><td>Expression-Based Transformations</td><td>Supports lineage extraction for basic SQL expressions and straightforward column transformations.</td></tr></tbody></table>

{% hint style="info" %}
Column-level lineage accuracy improves when SQL statements use explicit table aliases and fully qualified column references.
{% endhint %}

{% hint style="warning" %}
Lineage extraction requires access to dataset SQL definitions, query text, and related metadata. Missing source definitions may result in incomplete lineage relationships.
{% endhint %}

## Partial or Limited Coverage

Certain lineage scenarios have partial coverage due to SQL complexity, metadata availability, or limitations in source object definitions. These scenarios may produce incomplete lineage relationships or reduced column-level mapping accuracy.

### Scenarios

<table><thead><tr><th width="219.6666259765625">Scenario</th><th>Limitation Description</th></tr></thead><tbody><tr><td>Complex Expressions</td><td>Column-level lineage for complex expressions may degrade to partial lineage or fixed-value mappings.</td></tr><tr><td>Functions in SQL</td><td>Function-based lineage depends on OEConfig.PROCESS_FUNCTION_RELATED_LINEAGE. Without this configuration, function lineage may be incomplete.</td></tr><tr><td>UPDATE Statements</td><td>Lineage extraction depends on the ability to resolve source and target column mappings within the SET clause.</td></tr><tr><td>DELETE Statements</td><td>Supports limited lineage refinement and dependency tracking.</td></tr><tr><td>MERGE / UPSERT Statements</td><td>Lineage coverage is partial and depends on the complexity of the SQL statement.</td></tr><tr><td>Common Table Expressions (CTEs) and Subqueries</td><td>Supports lineage extraction, but column-level mappings may be incomplete in complex scenarios.</td></tr><tr><td>Stored Procedures and Functions</td><td>Lineage extraction depends on the availability of procedure and function definitions. Missing source text may result in incomplete lineage.</td></tr><tr><td>Wildcard (*) Columns</td><td>Wildcard column references may not fully resolve to individual source columns.</td></tr><tr><td>Temporary Tables</td><td>Temporary tables may result in partial lineage when source-to-target relationships cannot be fully resolved.</td></tr></tbody></table>

{% hint style="info" %}
Using explicit table aliases, fully qualified column names, and accessible source definitions can improve lineage accuracy and column-level mapping.
{% endhint %}

{% hint style="warning" %}
Complex SQL logic, unavailable procedure definitions, and dynamically generated SQL statements may prevent the connector from building complete lineage relationships.
{% endhint %}

## Unsupported Scenarios

The PostgreSQL connector does not support lineage extraction for certain SQL constructs, runtime-generated queries, external execution mechanisms, and non-database assets due to limitations in SQL parsing and metadata availability.

### Unsupported Lineage

<table><thead><tr><th width="221">Not Supported</th><th>Description</th></tr></thead><tbody><tr><td>OPENQUERY and External Query Execution</td><td>Queries executed through external query mechanisms are not supported for lineage extraction.</td></tr><tr><td>Dynamic SQL</td><td>Runtime-generated SQL statements cannot be resolved and are not included in lineage.</td></tr><tr><td>EXEC Procedure Calls</td><td>Single-word EXEC procedure calls without accessible SQL definitions are not supported.</td></tr><tr><td>BEGIN...END Procedural Blocks</td><td>Simple procedural blocks and procedural execution flows are not fully processed for lineage extraction.</td></tr><tr><td>REFRESH MATERIALIZED VIEW</td><td>Materialized view refresh operations are not included in lineage generation.</td></tr><tr><td>MDX and Non-SQL Constructs</td><td>MDX statements and non-SQL query languages are outside the scope of lineage extraction.</td></tr><tr><td>OPTIMIZE and DESC Statements</td><td>Administrative and metadata inspection commands are not processed for lineage.</td></tr><tr><td>SSIS Package Lineage</td><td>Lineage extraction from SSIS (.dtsx) packages is not supported by the PostgreSQL connector.</td></tr><tr><td>Report and BI Lineage</td><td>Lineage for reporting, dashboard, and business intelligence assets is outside the scope of the database connector.</td></tr></tbody></table>

{% hint style="info" %}
Unsupported scenarios do not generate lineage relationships and may appear disconnected or absent in lineage visualizations.
{% endhint %}

{% hint style="warning" %}
For lineage involving reporting tools, ETL platforms, or external processing frameworks, use the corresponding connector that supports those assets.
{% endhint %}

## Current Functional Status

This section describes the current lineage coverage and functional behavior supported by the PostgreSQL connector.

<table><thead><tr><th width="220.33331298828125">Status Area</th><th>Details</th></tr></thead><tbody><tr><td>Overall Coverage</td><td>Partial coverage across PostgreSQL datasets, query logs, views, stored procedures, and functions.</td></tr><tr><td>Lineage Depth</td><td>Supports both table-level and column-level lineage through SQL parsing and relationship mapping.</td></tr><tr><td>Supported Inputs</td><td>Tables, views, query logs, SQL datasets, and supported SQL transformation statements.</td></tr><tr><td>Functional Scope</td><td>Lineage extraction works reliably for standard SQL operations, including SELECT, INSERT, JOIN, and VIEW definitions.</td></tr><tr><td>Limitation Areas</td><td>Complex expressions, function-based transformations, stored procedures, dynamic SQL, temporary tables, and certain advanced SQL patterns.</td></tr><tr><td>Resulting Output</td><td>Produces reliable lineage for most standard SQL-based scenarios, with partial mapping in complex or unsupported cases.</td></tr></tbody></table>

{% hint style="warning" %}
The PostgreSQL connector is production-ready for standard SQL-based lineage extraction and provides strong table-level and column-level lineage capabilities. However, complex expressions, functions, stored procedures, and unsupported SQL patterns may result in incomplete lineage and could require manual validation or lineage supplementation.
{% endhint %}

***

&#x20;Copyright © 2026, OvalEdge LLC, Peachtree Corners GA USA


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.ovaledge.com/release8.1/connectors/connector-repositories/rdbms/postgresql/postgresql-lineage.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.