Redshift Research Project

System Table Tracker

System Table Version 1.0.41465 / 2022-09-10

schema name column data type
information_schema element_types array_type_identifier sql_identifier
information_schema element_types character_maximum_length cardinal_number
information_schema element_types character_octet_length cardinal_number
information_schema element_types character_set_catalog sql_identifier
information_schema element_types character_set_name sql_identifier
information_schema element_types character_set_schema sql_identifier
information_schema element_types collation_catalog sql_identifier
information_schema element_types collation_name sql_identifier
information_schema element_types collation_schema sql_identifier
information_schema element_types data_type character_data
information_schema element_types datetime_precision cardinal_number
information_schema element_types domain_default character_data
information_schema element_types dtd_identifier sql_identifier
information_schema element_types interval_precision character_data
information_schema element_types interval_type character_data
information_schema element_types maximum_cardinality cardinal_number
information_schema element_types numeric_precision cardinal_number
information_schema element_types numeric_precision_radix cardinal_number
information_schema element_types numeric_scale cardinal_number
information_schema element_types object_catalog sql_identifier
information_schema element_types object_name sql_identifier
information_schema element_types object_schema sql_identifier
information_schema element_types object_type character_data
information_schema element_types scope_catalog sql_identifier
information_schema element_types scope_name sql_identifier
information_schema element_types scope_schema sql_identifier
information_schema element_types udt_catalog sql_identifier
information_schema element_types udt_name sql_identifier
information_schema element_types udt_schema sql_identifier

View Text

SELECT CAST(current_database() AS information_schema.sql_identifier) AS object_catalog,
       CAST(n.nspname AS information_schema.sql_identifier) AS object_schema,
       CAST(x.objname AS information_schema.sql_identifier) AS object_name,
       CAST(x.objtype AS information_schema.character_data) AS object_type,
       CAST(x.objdtdid AS information_schema.sql_identifier) AS array_type_identifier,
       CAST(CASE
              WHEN nbt.nspname = CAST('pg_catalog' AS name)
                THEN format_type(bt.oid,
                                 CAST(NULL AS integer))
              ELSE CAST('USER-DEFINED' AS text)
            END AS information_schema.character_data) AS data_type,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS character_maximum_length,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS character_octet_length,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_name,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_name,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS numeric_precision,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS numeric_precision_radix,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS numeric_scale,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS datetime_precision,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS interval_type,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS interval_precision,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS domain_default,
       CAST(current_database() AS information_schema.sql_identifier) AS udt_catalog,
       CAST(nbt.nspname AS information_schema.sql_identifier) AS udt_schema,
       CAST(bt.typname AS information_schema.sql_identifier) AS udt_name,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_name,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS maximum_cardinality,
       CAST(CAST('a' AS text) || CAST(x.objdtdid AS text) AS information_schema.sql_identifier) AS dtd_identifier
FROM pg_namespace AS n,
     pg_type AS at,
     pg_namespace AS nbt,
     pg_type AS bt,
     (((SELECT c.relnamespace,
               CAST(c.relname AS information_schema.sql_identifier) AS relname,
               CAST('TABLE' AS varchar) AS varchar,
               a.attnum,
               a.atttypid
        FROM pg_class AS c, pg_attribute AS a
        WHERE c.oid = a.attrelid
          AND (c.relkind = CAST('r' AS "char")
            OR c.relkind = CAST('v' AS "char"))
          AND a.attnum > 0
          AND NOT a.attisdropped

        UNION ALL

        SELECT t.typnamespace,
               CAST(t.typname AS information_schema.sql_identifier) AS typname,
               CAST('DOMAIN' AS varchar) AS varchar,
               1,
               t.typbasetype
        FROM pg_type AS t
        WHERE t.typtype = CAST('d' AS "char"))

       UNION ALL

       SELECT p.pronamespace,
              CAST((CAST(p.proname AS text) || CAST('_' AS text)) || CAST(CAST(p.oid AS varchar) AS text) AS information_schema.sql_identifier) AS sql_identifier,
              CAST('ROUTINE' AS varchar) AS varchar,
              pos.n,
              (p.proargtypes)[pos.n - 1] AS proargtypes
       FROM pg_proc AS p,
            information_schema._pg_keypositions() AS pos (n)
       WHERE p.pronargs >= pos.n)

      UNION ALL

      SELECT p.pronamespace,
             CAST((CAST(p.proname AS text) || CAST('_' AS text)) || CAST(CAST(p.oid AS varchar) AS text) AS information_schema.sql_identifier) AS sql_identifier,
             CAST('ROUTINE' AS varchar) AS varchar,
             0,
             p.prorettype
      FROM pg_proc AS p) AS x (objschema, objname, objtype, objdtdid, objtypeid)
WHERE n.oid = x.objschema
  AND at.oid = x.objtypeid
  AND (at.typelem <> CAST(0 AS oid)
   AND at.typlen = -1)
  AND at.typelem = bt.oid
  AND nbt.oid = bt.typnamespace
  AND (CAST(n.nspname AS information_schema.sql_identifier),
       CAST(x.objname AS information_schema.sql_identifier),
       CAST(x.objtype AS information_schema.character_data),
       CAST(x.objdtdid AS information_schema.sql_identifier)) IN (SELECT data_type_privileges.object_schema,
                                                                         data_type_privileges.object_name,
                                                                         data_type_privileges.object_type,
                                                                         data_type_privileges.dtd_identifier
                                                                  FROM information_schema.data_type_privileges)


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