Extraction Queries for PostgreSQL

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

The default extraction queries for the PostgreSQL data source are provided below. You can customize them to better suit your catalog needs. Custom queries can be specified on the Metadata Extraction tab of the data source Settings page under Metadata Extraction Queries.

You can customize all or some of the queries.

Schema

Ensure that your query has columns labeled as CATALOG, SCHEMA in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS CATALOG,
  nspname AS SCHEMA
FROM
  PG_CATALOG.PG_NAMESPACE
WHERE
  nspname NOT LIKE 'pg_%'
  AND nspname NOT IN ('''')
  AND nspname NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )

Table

Ensure that your query has columns labeled as CATALOG, SCHEMA, TABLE_NAME, TABLE_OWNER, TABLE_TYPE, and REMARKS in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS CATALOG,
  A.TABLE_SCHEMA as SCHEMA,
  A.TABLE_NAME,
  A.OWNER AS TABLE_OWNER,
  A.TABLE_TYPE,
  PGDC.DESCRIPTION AS REMARKS
FROM
  (
    SELECT
      T.SCHEMANAME AS TABLE_SCHEMA,
      T.TABLENAME AS TABLE_NAME,
      T.TABLEOWNER AS OWNER,
      'TABLE' AS TABLE_TYPE
    FROM
      PG_TABLES T
    where
      (T.schemaname || '.' || T.tablename) NOT IN (
        SELECT
          ST.SCHEMANAME || '.' || ST.RELNAME
        FROM
          PG_INHERITS,
          PG_CATALOG.PG_STAT_ALL_TABLES ST
        WHERE
          PG_INHERITS.INHRELID = ST.RELID
        UNION
        SELECT
          nmsp.nspname || '.' || pgc.relname
        from
          pg_inherits
          join pg_class pgc on pg_inherits.inhrelid = pgc.oid
          join pg_namespace nmsp on nmsp.oid = pgc.relnamespace
      )
  ) A
  LEFT JOIN(
    SELECT
      C.SCHEMANAME,
      C.RELNAME,
      PGD.DESCRIPTION
    FROM
      PG_CATALOG.PG_STAT_ALL_TABLES C
      INNER JOIN PG_CATALOG.PG_DESCRIPTION PGD ON (PGD.OBJOID = C.RELID)
    WHERE
      PGD.OBJSUBID IS NULL
      OR PGD.OBJSUBID = 0
  ) PGDC ON (
    PGDC.RELNAME = A.TABLE_NAME
    AND PGDC.SCHEMANAME = A.TABLE_SCHEMA
  )
WHERE
  A.TABLE_SCHEMA NOT LIKE 'pg\\\\_%'
  AND A.TABLE_SCHEMA NOT IN ('''')
  AND A.TABLE_SCHEMA NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )

View

Ensure that your query has columns labeled as CATALOG, SCHEMA, VIEW_NAME, VIEW_CREATE_STATEMENT, ‘VIEW’ AS VIEW_TYPE, and REMARKS in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS CATALOG,
  schemaname AS SCHEMA,
  viewname AS VIEW_NAME,
  ' CREATE OR REPLACE VIEW  ' || schemaname || '.' || viewname || ' AS ' || definition AS VIEW_CREATE_STATEMENT,
  'VIEW' AS VIEW_TYPE,
  '' AS REMARKS
FROM
  PG_VIEWS
WHERE
  schemaname NOT LIKE 'pg\\\\_%'
  AND schemaname NOT IN ('''')
  AND schemaname NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )
UNION
select
  CURRENT_DATABASE() AS CATALOG,
  schemaname AS SCHEMA,
  matviewname AS VIEW_NAME,
  ' CREATE MATERIALIZED VIEW  ' || schemaname || '.' || matviewname || ' AS ' || definition AS VIEW_CREATE_STATEMENT,
  'MAT_VIEW' AS VIEW_TYPE,
  '' AS REMARKS
FROM
  PG_MATVIEWS
WHERE
  schemaname NOT LIKE 'pg\\_%'
  AND schemaname NOT IN ('''')
  AND schemaname NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )

Column

Ensure that your query has columns labeled as CATALOG, SCHEMA, TABLE_NAME, COLUMN_NAME, TYPE_NAME, DATA_TYPE, ORDINAL_POSITION, IS_NULLABLE, REMARKS, and COLUMN_DEFAULT in the SELECT statement.

Example:

SELECT
  *
FROM
  (
    SELECT
      CURRENT_DATABASE() AS CATALOG,
      S.NSPNAME AS SCHEMA,
      O.RELNAME AS TABLE_NAME,
      C.ATTNAME AS COLUMN_NAME,
      CASE WHEN (
        IC.DATA_TYPE = 'numeric'
        OR IC.DATA_TYPE = 'integer'
        OR IC.DATA_TYPE = 'bigint'
        OR IC.DATA_TYPE = 'smallint'
        OR IC.DATA_TYPE = 'real'
        OR IC.DATA_TYPE = 'double precision'
      )
      AND IC.NUMERIC_SCALE IS NOT NULL
      AND IC.NUMERIC_PRECISION IS NOT NULL THEN CONCAT(
        TYP.TYPNAME,
        '(',
        CAST(
          IC.NUMERIC_PRECISION AS character varying
        ),
        ',',
        CAST(
          IC.NUMERIC_SCALE AS character varying
        ),
        ')'
      ) WHEN (
        IC.DATA_TYPE = 'numeric'
        OR IC.DATA_TYPE = 'integer'
        OR IC.DATA_TYPE = 'bigint'
        OR IC.DATA_TYPE = 'smallint'
        OR IC.DATA_TYPE = 'real'
        OR IC.DATA_TYPE = 'double precision'
      )
      AND IC.NUMERIC_SCALE IS NULL
      AND IC.NUMERIC_PRECISION IS NULL THEN TYP.TYPNAME WHEN (
        IC.DATA_TYPE = 'character varying'
        OR IC.DATA_TYPE = 'character'
      )
      AND IC.CHARACTER_MAXIMUM_LENGTH IS NULL THEN TYP.TYPNAME WHEN (
        IC.DATA_TYPE = 'character varying'
        OR IC.DATA_TYPE = 'character'
      )
      AND IC.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CONCAT(
        TYP.TYPNAME,
        '(',
        CAST(
          IC.CHARACTER_MAXIMUM_LENGTH as character varying
        ),
        ')'
      ) WHEN IC.DATA_TYPE = 'interval'
      and IC.DATETIME_PRECISION IS NOT NULL THEN CONCAT(
        TYP.TYPNAME,
        '(',
        CAST(
          IC.DATETIME_PRECISION as character varying
        ),
        ', ',
        CAST(
          IC.INTERVAL_TYPE as character varying
        ),
        ')'
      ) WHEN (
        IC.DATA_TYPE = 'time without time zone'
        OR IC.DATA_TYPE = 'timestamptz'
        OR IC.DATA_TYPE = 'date'
        OR IC.DATA_TYPE = 'timestamp with time zone'
        OR IC.DATA_TYPE = 'time with time zone'
      )
      AND IC.DATETIME_PRECISION IS NOT NULL THEN CONCAT(
        TYP.TYPNAME,
        '(',
        CAST(
          IC.DATETIME_PRECISION as character varying
        ),
        ')'
      ) WHEN IC.DATA_TYPE = 'time without time zone'
      AND IC.DATETIME_PRECISION IS NULL THEN TYP.TYPNAME else TYP.TYPNAME END as TYPE_NAME,
      TYP.TYPNAME AS DATA_TYPE,
      C.ATTNUM AS ORDINAL_POSITION,
      NOT C.ATTNOTNULL AS IS_NULLABLE,
      pgd.description AS REMARKS,
      null AS COLUMN_DEFAULT
    FROM
      PG_ATTRIBUTE C
      INNER JOIN PG_CLASS O ON C.ATTRELID = O.OID
      INNER JOIN PG_NAMESPACE S ON O.RELNAMESPACE = S.OID
      INNER JOIN PG_TYPE TYP ON TYP.OID = C.ATTTYPID
      LEFT JOIN PG_CATALOG.PG_STAT_ALL_TABLES ca on(
        ca.relname = O.RELNAME
        and ca.schemaname = S.NSPNAME
      )
      LEFT JOIN PG_CATALOG.PG_DESCRIPTION pgd on (
        pgd.objoid = ca.relid
        and pgd.objsubid = C.ATTNUM
      )
      LEFT JOIN INFORMATION_SCHEMA.columns IC on (
        IC.table_schema = S.NSPNAME
        and IC.table_name = O.RELNAME
        and IC.column_name = C.ATTNAME
      )
    WHERE
      C.ATTNUM > 0
      AND nspname NOT LIKE 'pg\\\\_%'
      AND nspname NOT IN ('''')
      AND nspname NOT IN (
        'information_schema', 'pg_toast',
        'pg_catalog', 'pg_internal'
      )
  ) T
WHERE
  (T.SCHEMA || '.' || T.TABLE_NAME) NOT IN (
    SELECT
      ST.SCHEMANAME || '.' || ST.RELNAME
    FROM
      PG_INHERITS,
      PG_CATALOG.PG_STAT_ALL_TABLES ST
    WHERE
      PG_INHERITS.INHRELID = ST.RELID
  )

Primary Key

Ensure that your query has columns labeled as CATALOG, SCHEMA, TABLE_NAME, PK_NAME, COLUMN_NAME, and KEY_SEQ in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS CATALOG,
  S.NSPNAME AS SCHEMA,
  C.RELNAME AS TABLE_NAME,
  CONSTR.CONNAME AS PK_NAME,
  ATTR.ATTNAME AS COLUMN_NAME,
  ATTR.ATTNUM AS KEY_SEQ
FROM
  PG_CONSTRAINT CONSTR
  INNER JOIN PG_ATTRIBUTE ATTR ON ATTR.ATTNUM = ANY (CONSTR.CONKEY)
  AND ATTR.ATTRELID = CONSTR.CONRELID
  INNER JOIN PG_CLASS C ON CONSTR.CONRELID = C.OID
  INNER JOIN PG_NAMESPACE S ON C.RELNAMESPACE = S.OID
WHERE
  nspname NOT LIKE 'pg\\\\_%'
  AND CONSTR.CONTYPE = 'p'
  AND nspname NOT IN ('''')
  AND nspname NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )

Foreign Key

Ensure that your query has columns labeled as CATALOG, FK_SCHEMA, FK_TABLE, FK_NAME, FK_COLUMN, FK_CATALOG, KEY_SEQ, PK_CATALOG, PK_SCHEMA, PK_TABLE, PK_NAME, and PK_COLUMN in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS CATALOG,
  F.FKTABLE_SCHEM as FK_SCHEMA,
  F.FKTABLE_NAME AS FK_TABLE,
  F.FK_NAME,
  F.FKCOLUMN_NAME AS FK_COLUMN,
  null AS FK_CATALOG,
  F.KEY_SEQ,
  null AS PK_CATALOG,
  P.PKTABLE_SCHEM AS PK_SCHEMA,
  P.PKTABLE_NAME AS PK_TABLE,
  P.PK_NAME,
  P.PKCOLUMN_NAME AS PK_COLUMN
FROM
  (
    SELECT
      S.NSPNAME AS FKTABLE_SCHEM,
      C.RELNAME AS FKTABLE_NAME,
      CONSTR.CONNAME AS FK_NAME,
      ATTR.ATTNAME AS FKCOLUMN_NAME,
      ATTR.ATTNUM AS KEY_SEQ,
      CONSTR.CONFKEY,
      CONSTR.CONFRELID
    FROM
      PG_CONSTRAINT CONSTR
      INNER JOIN PG_ATTRIBUTE ATTR ON ATTR.ATTNUM = ANY (CONSTR.CONKEY)
      AND ATTR.ATTRELID = CONSTR.CONRELID
      INNER JOIN PG_CLASS C ON CONSTR.CONRELID = C.OID
      INNER JOIN PG_NAMESPACE S ON C.RELNAMESPACE = S.OID
      AND CONSTR.CONTYPE = 'f'
    WHERE
      nspname NOT LIKE 'pg\\\\_%'
      AND nspname NOT IN ('''')
      AND nspname NOT IN (
        'information_schema', 'pg_toast',
        'pg_catalog', 'pg_internal'
      )
  ) F
  INNER JOIN (
    SELECT
      S.NSPNAME AS PKTABLE_SCHEM,
      C.RELNAME AS PKTABLE_NAME,
      CONSTR.CONNAME AS PK_NAME,
      ATTR.ATTNAME AS PKCOLUMN_NAME,
      ATTR.ATTNUM AS PKEY_SEQ,
      C.OID
    FROM
      PG_CONSTRAINT CONSTR
      INNER JOIN PG_ATTRIBUTE ATTR ON ATTR.ATTNUM = ANY (CONSTR.CONKEY)
      AND ATTR.ATTRELID = CONSTR.CONRELID
      INNER JOIN PG_CLASS C ON CONSTR.CONRELID = C.OID
      INNER JOIN PG_NAMESPACE S ON C.RELNAMESPACE = S.OID
      AND (
        CONSTR.CONTYPE = 'p'
        OR CONSTR.CONTYPE = 'u'
      )
    WHERE
      nspname NOT LIKE 'pg\\\\_%'
      AND nspname NOT IN ('''')
      AND nspname NOT IN (
        'information_schema', 'pg_toast',
        'pg_catalog', 'pg_internal'
      )
  ) P ON P.PKEY_SEQ = ANY (F.CONFKEY)
  AND P.OID = F.CONFRELID
ORDER BY
  FKTABLE_SCHEM,
  FKTABLE_NAME,
  FK_NAME,
  KEY_SEQ

Index

Ensure that your query has columns labeled as CATALOG, SCHEMA, TABLE_NAME, COLUMN_NAME, INDEX_NAME, POSITION, and INDEX_TYPE in the SELECT statement.

Example:

SELECT
  current_database() AS CATALOG,
  stable.schemaname AS SCHEMA,
  ind_parent.relname AS TABLE_NAME,
  ind_col.attname AS COLUMN_NAME,
  ind_child.relname AS INDEX_NAME,
  ind_col.attnum AS POSITION,
  acc_meth.amname AS INDEX_TYPE
FROM
  PG_INDEX index
  JOIN PG_CLASS ind_parent ON index.indrelid = ind_parent.oid
  JOIN PG_CLASS ind_child ON index.indexrelid = ind_child.oid
  JOIN PG_ATTRIBUTE ind_col ON ind_parent.oid = ind_col.attrelid
  JOIN PG_AM acc_meth ON ind_child.relam = acc_meth.oid
  JOIN PG_TABLES stable ON ind_parent.relname = stable.tablename
WHERE
  ind_parent.relkind = 'r'
  AND ind_col.attnum = ANY (index.indkey)
  AND schemaname NOT LIKE 'pg\\\\_%'
  AND schemaname NOT IN ('''')
  AND schemaname NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )

Function

Ensure that your query has columns labeled as FUNCTION_CAT, SCHEMA, FUNCTION_NAME, SPECIFIC_NAME, REMARKS, and FUNCTION_DEFINITION in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS FUNCTION_CAT,
  S.NSPNAME AS SCHEMA,
  P.PRONAME AS FUNCTION_NAME,
  NULL AS SPECIFIC_NAME,
  NULL AS REMARKS,
  Pg_get_functiondef(P.oid) AS FUNCTION_DEFINITION
FROM
  PG_PROC P
  INNER JOIN PG_NAMESPACE S ON P.PRONAMESPACE = S.OID
  INNER JOIN PG_LANGUAGE L ON P.PROLANG = L.OID
WHERE
  (
    L.LANNAME IN ('internal', 'sql')
    AND p.prorettype <> 'pg_catalog.trigger' :: pg_catalog.regtype
    OR L.LANNAME IN ('plpgsql')
  )
  AND S.NSPNAME NOT LIKE 'pg\\\\_%'
  AND p.prokind NOT IN ('a')
  AND S.NSPNAME NOT IN ('''')
  AND S.NSPNAME NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )

Function Definition

Ensure that your query has columns labeled as FUNCTION_CAT, SCHEMA, FUNCTION_NAME, ARG_NAME, FUNCTION_ARGS, NUM_OF_FUNCTION_ARGS, ARG_TYPE, TYPE_NAME, ARG_DEF, and REMARKS in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS FUNCTION_CAT,
  N.NSPNAME AS SCHEMA,
  P.PRONAME AS FUNCTION_NAME,
  P.PRONAME AS ARG_NAME,
  P.PROARGNAMES AS FUNCTION_ARGS,
  P.PRONARGS AS NUM_OF_FUNCTION_ARGS,
  STRING_TO_ARRAY(
    REPLACE(
      OIDVECTORTYPES(P.PROARGTYPES),
      '\"',
      ''
    ),
    ','
  ) AS ARG_TYPE,
  FORMAT_TYPE(P.PRORETTYPE, NULL) AS TYPE_NAME,
  P.PROSRC AS ARG_DEF,
  NULL AS REMARKS
FROM
  PG_PROC P
  INNER JOIN PG_NAMESPACE N ON P.PRONAMESPACE = N.OID
  INNER JOIN PG_LANGUAGE L ON P.PROLANG = L.OID
WHERE
  (
    L.LANNAME IN ('internal', 'sql')
    AND p.prorettype <> 'pg_catalog.trigger' :: pg_catalog.regtype
    OR L.LANNAME IN ('plpgsql')
  )
  and N.NSPNAME NOT LIKE 'pg\\\\_%'
  AND N.NSPNAME NOT IN ('''')
  AND N.NSPNAME NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )