Redshift Research Project

System Table Tracker

System view information_schema.constraint_column_usage version 1.0.51973 / 2023-06-17

View Text

SELECT CAST(current_database() AS information_schema.sql_identifier) AS table_catalog,
       CAST(x.tblschema AS information_schema.sql_identifier) AS table_schema,
       CAST(x.tblname AS information_schema.sql_identifier) AS table_name,
       CAST(x.colname AS information_schema.sql_identifier) AS column_name,
       CAST(current_database() AS information_schema.sql_identifier) AS constraint_catalog,
       CAST(x.cstrschema AS information_schema.sql_identifier) AS constraint_schema,
       CAST(x.cstrname AS information_schema.sql_identifier) AS constraint_name
FROM ((SELECT DISTINCT nr.nspname,
                       r.relname,
                       a.attname,
                       nc.nspname,
                       c.conname
       FROM pg_namespace AS nr, pg_class AS r, pg_attribute AS a, pg_depend AS d, pg_namespace AS nc, pg_constraint AS c, pg_user AS u
       WHERE nr.oid = r.relnamespace
         AND r.oid = a.attrelid
         AND d.refclassid = CAST(CAST('pg_class' AS regclass) AS oid)
         AND d.refobjid = r.oid
         AND d.refobjsubid = a.attnum
         AND d.classid = CAST(CAST('pg_constraint' AS regclass) AS oid)
         AND d.objid = c.oid
         AND c.connamespace = nc.oid
         AND c.contype = CAST('c' AS "char")
         AND r.relkind = CAST('r' AS "char")
         AND NOT a.attisdropped
         AND r.relowner = u.usesysid
         AND u.usename = CAST("current_user"() AS name)

       UNION ALL

       SELECT nr.nspname,
              r.relname,
              a.attname,
              nc.nspname,
              c.conname
       FROM pg_namespace AS nr, pg_class AS r, pg_attribute AS a, pg_namespace AS nc, pg_constraint AS c, pg_user AS u
       WHERE nr.oid = r.relnamespace
         AND r.oid = a.attrelid
         AND nc.oid = c.connamespace
         AND r.oid = c.confrelid
         AND a.attnum = ANY(c.confkey)
         AND NOT a.attisdropped
         AND c.contype = CAST('f' AS "char")
         AND r.relkind = CAST('r' AS "char")
         AND r.relowner = u.usesysid
         AND u.usename = CAST("current_user"() AS name))

      UNION ALL

      SELECT nr.nspname,
             r.relname,
             a.attname,
             nc.nspname,
             c.conname
      FROM pg_namespace AS nr, pg_class AS r, pg_attribute AS a, pg_namespace AS nc, pg_constraint AS c, pg_user AS u
      WHERE nr.oid = r.relnamespace
        AND r.oid = a.attrelid
        AND nc.oid = c.connamespace
        AND r.oid = c.conrelid
        AND a.attnum = ANY(c.conkey)
        AND NOT a.attisdropped
        AND (c.contype = CAST('p' AS "char")
          OR c.contype = CAST('u' AS "char"))
        AND r.relkind = CAST('r' AS "char")
        AND r.relowner = u.usesysid
        AND u.usename = CAST("current_user"() AS name)) AS x (tblschema, tblname, colname, cstrschema, cstrname)

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers