Redshift Observatory

System Table Tracker

System view pg_catalog.svv_redshift_columns version 1.0.52931 / 2023-07-07

View Text

(SELECT CAST(current_database() AS varchar(128)) AS database_name,
        CAST(pns.nspname AS varchar(128)) AS schema_name,
        CAST(pgc.relname AS varchar(128)) AS table_name,
        CAST(pga.attname AS varchar(128)) AS column_name,
        pga.attnum AS ordinal_position,
        CAST(format_type(pga.atttypid, pga.atttypmod) AS varchar) AS data_type,
        CAST(ad.adsrc AS varchar(4000)) AS column_default,
        CAST(CASE
               WHEN pga.attnotnull
                 THEN CAST('NO' AS text)
               ELSE CAST('YES' AS text)
             END AS varchar(3)) AS is_nullable,
        CAST(format_encoding(CAST(pga.attencodingtype AS integer)) AS varchar(128)) AS encoding,
        pga.attisdistkey AS distkey,
        pga.attsortkeyord AS sortkey,
        CAST(array_to_string(pa.attacl,
                             CAST('~' AS text)) AS varchar(128)) AS column_acl,
        CAST(d.description AS varchar(256)) AS remarks
 FROM pg_attribute AS pga
      INNER JOIN pg_class AS pgc ON pga.attrelid = pgc.oid
      INNER JOIN pg_namespace AS pns ON pns.oid = pgc.relnamespace
      LEFT JOIN pg_attribute_acl AS pa ON pa.attrelid = pgc.oid
                                      AND pa.attnum = pga.attnum
      LEFT JOIN pg_attrdef AS ad ON pga.attrelid = ad.adrelid
                                AND pga.attnum = ad.adnum
      LEFT JOIN pg_description AS d ON pgc.oid = d.objoid
                                   AND pga.attnum = d.objsubid
 WHERE pga.attnum > 0
   AND NOT pga.attisdropped
   AND (pgc.relkind = CAST('r' AS "char")
     OR pgc.relkind = CAST('v' AS "char"))
   AND has_schema_privilege(CAST("current_user"() AS name),
                            CAST(pns.nspname AS text),
                            CAST('USAGE' AS text))
   AND (has_table_privilege(CAST("current_user"() AS name),
                            pgc.oid,
                            CAST('SELECT' AS text))
     OR has_column_privilege(CAST("current_user"() AS name),
                             pgc.oid,
                             pga.attnum,
                             CAST('SELECT' AS text)))
   AND (pns.nspname <> CAST('catalog_history' AS name)
    AND pns.nspname <> CAST('pg_toast' AS name)
    AND pns.nspname <> CAST('pg_internal' AS name))
   AND pns.nspname !~~ CAST('pg_temp%' AS text)

 UNION ALL

 SELECT CAST(current_database() AS varchar(128)) AS database_name,
        CAST(lbv_cols.schema AS varchar(128)) AS schema_name,
        CAST(lbv_cols.viewname AS varchar(128)) AS table_name,
        CAST(lbv_cols.colname AS varchar(128)) AS column_name,
        lbv_cols.colnum AS ordinal_position,
        CAST(lbv_cols.type AS varchar(128)) AS data_type,
        CAST(CAST('' AS varchar) AS varchar(4000)) AS column_default,
        CAST(CAST('YES' AS varchar) AS varchar(3)) AS is_nullable,
        '' AS encoding,
        'f' AS distkey,
        0 AS sortkey,
        '' AS column_acl,
        '' AS remarks
 FROM pg_get_late_binding_view_cols() AS lbv_cols(schema name,
                                                  viewname name,
                                                  colname name,
                                                  type varchar,
                                                  colnum integer)
      INNER JOIN pg_class AS pgc ON pgc.relname = lbv_cols.viewname
      INNER JOIN pg_namespace AS pns ON lbv_cols.schema = pns.nspname
 WHERE pns.oid = pgc.relnamespace
   AND has_schema_privilege(CAST("current_user"() AS name),
                            CAST(pns.nspname AS text),
                            CAST('USAGE' AS text))
   AND has_table_privilege(CAST("current_user"() AS name),
                           pgc.oid,
                           CAST('SELECT' AS text))
   AND (lbv_cols.schema <> CAST('catalog_history' AS name)
    AND lbv_cols.schema <> CAST('pg_internal' AS name)
    AND lbv_cols.schema <> CAST('pg_toast' AS name))
   AND lbv_cols.schema !~~ CAST('pg_temp%' AS text))

UNION ALL

SELECT CAST(btrim(CAST(rs_cols.database_name AS text)) AS varchar(128)) AS database_name,
       CAST(btrim(CAST(rs_cols.schema_name AS text)) AS varchar(128)) AS schema_name,
       CAST(btrim(CAST(rs_cols.table_name AS text)) AS varchar(128)) AS table_name,
       CAST(btrim(CAST(rs_cols.column_name AS text)) AS varchar(128)) AS column_name,
       rs_cols.column_number AS ordinal_position,
       CAST(btrim(CAST(rs_cols.data_type AS text)) AS varchar(128)) AS data_type,
       CAST(btrim(CAST(rs_cols.column_default AS text)) AS varchar(4000)) AS column_default,
       CAST(CASE
              WHEN rs_cols.is_nullable
                THEN CAST('YES' AS text)
              ELSE CAST('NO' AS text)
            END AS varchar(3)) AS is_nullable,
       CAST(btrim(CAST(rs_cols.compression AS text)) AS varchar(128)) AS encoding,
       rs_cols.is_dist_key AS distkey,
       rs_cols.sort_key AS sortkey,
       CAST(btrim(CAST(rs_cols.column_acl AS text)) AS varchar(128)) AS column_acl,
       CAST(btrim(CAST(rs_cols.remarks AS text)) AS varchar(256)) AS remarks
FROM pg_get_shared_redshift_columns() AS rs_cols(database_name varchar,
                                                 schema_name varchar,
                                                 table_name varchar,
                                                 column_name varchar,
                                                 column_number integer,
                                                 data_type varchar,
                                                 column_default varchar,
                                                 is_nullable boolean,
                                                 compression varchar,
                                                 is_dist_key boolean,
                                                 sort_key integer,
                                                 column_acl varchar,
                                                 remarks varchar)
ORDER BY 1,
         2,
         3,
         5

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