Extraction Queries for PostgreSQL

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

Make sure that your query has a column labeled as CATALOG in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS "CATALOG",
  nspname AS "SCHEMA"
FROM
  PG_NAMESPACE
WHERE
  nspname NOT LIKE 'pg\\_%'
  AND nspname NOT IN ('''')
  AND nspname NOT IN (
  'information_schema', 'pg_toast',
  'pg_catalog', 'pg_internal'
  );

Table

Make sure that your query has columns labeled as CATALOG, TABLE_NAME, TABLE_TYPE, and REMARKS in the SELECT statement.

Example:

SELECT
  CURRENT_DATABASE() AS "CATALOG",
  A.TABLE_SCHEM as "SCHEMA",
  A.TABLE_NAME,
  A.OWNER as "TABLE_OWNER",
  A.TABLE_TYPE,
  PGDC.DESCRIPTION AS "REMARKS"
FROM
  (
    SELECT
      T.SCHEMANAME AS "TABLE_SCHEM",
      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
    )
  ) 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_SCHEM
  )
WHERE
  A.TABLE_SCHEM NOT LIKE 'pg\\_%'
  AND A.TABLE_SCHEM NOT IN ('''')
  AND A.TABLE_SCHEM NOT IN (
  'information_schema', 'pg_toast',
  'pg_catalog', 'pg_internal'
  )
ORDER BY
  "TABLE_SCHEM",
  "TABLE_NAME";

View

Make sure that your query has columns labeled as CATALOG, 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 || ' TABLESPACE PG_DEFAULT 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'
  )
ORDER BY
  "SCHEMA",
  "VIEW_NAME";

Column

Make sure that your query has columns labeled as CATALOG, TABLE_NAME, TYPE_NAME, DATA_TYPE, COLUMN_NAME, 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",
      TYP.TYPNAME 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
      )
    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
  )
ORDER BY
  "SCHEMA",
  "TABLE_NAME",
  "ORDINAL_POSITION";

Primary Key

Make sure that your query has columns labeled as CATALOG, TABLE_NAME, and COLUMN_NAME 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'
  )
ORDER BY
  "SCHEMA",
  "TABLE_NAME",
  "PK_NAME",
  "KEY_SEQ";

Foreign Key

Make sure that your query has columns labeled as CATALOG, PK_SCHEMA, PK_TABLE, PK_COLUMN, FK_CATALOG, FK_SCHEMA, FK_TABLE, and FK_COLUMN in the select list.

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

Make sure that your query has columns labeled as CATALOG, TABLE_NAME, and COLUMN_NAME in the select list.

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'
  )
ORDER BY
  "SCHEMA",
  "TABLE_NAME",
  "COLUMN_NAME";

Function

Make sure that your query has columns labeled as FUNCTION_CAT, FUNCTION_NAME, and REMARKS in the select list.

Example:

SELECT
  CURRENT_DATABASE() AS "FUNCTION_CAT",
  S.NSPNAME AS "SCHEMA",
  P.PRONAME AS "FUNCTION_NAME",
  NULL AS "SPECIFIC_NAME",
  NULL AS "REMARKS"
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 S.NSPNAME NOT IN ('''')
    AND S.NSPNAME NOT IN (
    'information_schema', 'pg_toast',
    'pg_catalog', 'pg_internal'
  )
ORDER BY
  "FUNCTION_CAT",
  "SCHEMA",
  "FUNCTION_NAME";

Function Definition

Make sure that your query has columns labeled as FUNCTION_CAT, FUNCTION_NAME, ARG_NAME, TYPE_NAME, ARG_TYPE, and ARG_DEF in the select list.

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'
  )
ORDER BY
  "FUNCTION_CAT",
  "SCHEMA",
  "FUNCTION_NAME";