PostgreSQL support
Supported versions
Versions: 9.x, 10.x, 11.x, 12.x, 13.x, 14.x, 15.x, 16.x
Specification
Imported metadata
Tables
| Imported | Editable | |
|---|---|---|
| Tables | ✅ | ✅ |
| Columns | ✅ | ✅ |
| Data types | ✅ | |
| Nullability | ✅ | |
| Default value | ✅ | |
| Column comments | ✅ | ✅ |
| Data lineage | ✅ | ✅ |
| Table comments | ✅ | ✅ |
| Foreign keys | ✅ | ✅ |
| Primary keys | ✅ | ✅ |
| Unique keys | ✅ | ✅ |
| Triggers | ✅ | |
| When triggered | ✅ | |
| Script | ✅ |
Views, Materialized views
| Imported | Editable | |
|---|---|---|
| Views, Materialized views | ✅ | ✅ |
| Script | ✅ | ✅ |
| Columns | ✅ | ✅ |
| Data types | ✅ | |
| Nullability | ✅ | |
| Default value | ✅ | |
| Column comments | ✅ | ✅ |
| View comments | ✅ | ✅ |
Stored procedures
| Imported | Editable | |
|---|---|---|
| Stored procedures | ✅ | ✅ |
| Script | ✅ | ✅ |
| Procedures comments | ✅ | ✅ |
User-defined Functions
| Imported | Editable | |
|---|---|---|
| User-defined Functions | ✅ | ✅ |
| Script | ✅ | ✅ |
| Input arguments | ✅ | ✅ |
| Output results | ✅ | ✅ |
| Function comments | ✅ | ✅ |
SQL Queries (SELECTs in functions)
If a Function returns a table, a SQL Query object will be created in the documentation to represent the output.
For example, this function returns a table with seven columns:
Dataedo will create a SQL Query object in documentation, this object is in a parent-child relationship with the function from which it is derived, and contain columns and script that are used in the lineage - read more about automatic lineage for PostgreSQL.
| Imported | Editable | |
|---|---|---|
| SQL Queries | ✅ | ✅ |
| Script | ✅ | ✅ |
| Input arguments | ✅ | ✅ |
| Columns | ✅ | ✅ |
| Data types | ✅ |
Shared metadata
| Imported | Editable | |
|---|---|---|
| Shared metadata | ||
| Dependencies | ✅ | ✅ |
| Created time | ✅ | |
| Last updated time | ✅ |
Supported features
| Feature | Imported |
|---|---|
| Import comments | ✅ |
| Write comments back | ✅ |
| Data profiling | ✅ |
| Reference data (import lookups) | ✅ |
| Importing from DDL | ✅ |
| Generating DDL | ✅ |
| FK relationship tester |
Comments
Dataedo reads and writes PostgreSQL comments from/to the following objects:
| Object | Read | Write back |
|---|---|---|
| Table comments | ✅ | ✅ |
| Column comments | ✅ | ✅ |
| View comments | ✅ | ✅ |
| Columns | ✅ | ✅ |
| Function comments | ✅ | |
| Stored procedures | ✅ |
Data profiling
Dataedo supports the following data profiling in PostgreSQL:
| Profile | Support |
|---|---|
| Table row count | ✅ |
| Table sample data | ✅ |
| Column distribution (unique, non-unique, null, empty values) | ✅ |
| Min, max values | ✅ |
| Average | ✅ |
| Variance | ✅ |
| Standard deviation | ✅ |
| Min-max span | ✅ |
| Number of distinct values | ✅ |
| Top 10/100/1000 values | ✅ |
| 10 random values | ✅ |
Read more about profiling in the Data Profiling documentation.
Data Quality
Users will be able to check if data in PostgreSQL tables is accurate, consistent, complete, and reliable using Data Quality functionality. Data Quality requires SELECT permission over the tested object.
Data Lineage
| Source | Method | Version |
|---|---|---|
| Views - object level | From dependencies | ✅ |
| Views - object level | From SQL parsing | ✅ |
| Views - column level | From SQL parsing | ✅ |
| dbt | dbt connector | ✅ |
Data lineage is retrieved using the Dataedo SQL parser. Read more about the capabilities of the PostgreSQL SQL parser.
See also: dbt
Known Issues
- Rules are not imported.
- Trigger functions are grouped with standard functions.
- Dependencies are imported only from views.
Limitations
The following schema elements currently are not supported:
- Check constraints
- Non-unique indexes
- Sequences
- User-defined types
- Domains
- Extensions
- Event triggers
- Casts
Supported cloud databases
Dataedo should work with all cloud instances of PostgreSQL database. We have tested it with the following services:
