Redshift Research Project

System Table Tracker

System Table Version 1.0.41465 / 2022-09-10

schema name column data type
pg_catalog svv_all_columns character_maximum_length int4
pg_catalog svv_all_columns column_default varchar(4000)
pg_catalog svv_all_columns column_name varchar(128)
pg_catalog svv_all_columns database_name varchar(128)
pg_catalog svv_all_columns data_type varchar(128)
pg_catalog svv_all_columns is_nullable varchar(3)
pg_catalog svv_all_columns numeric_precision int4
pg_catalog svv_all_columns numeric_scale int4
pg_catalog svv_all_columns ordinal_position int4
pg_catalog svv_all_columns remarks varchar(256)
pg_catalog svv_all_columns schema_name varchar(128)
pg_catalog svv_all_columns table_name varchar(128)

View Text

(SELECT svv_redshift_columns.database_name,
        svv_redshift_columns.schema_name,
        svv_redshift_columns.table_name,
        svv_redshift_columns.column_name,
        svv_redshift_columns.ordinal_position,
        svv_redshift_columns.column_default,
        svv_redshift_columns.is_nullable,
        CAST(CASE
               WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                           7) = CAST('numeric' AS text)
                 OR "left"(CAST(svv_redshift_columns.data_type AS text),
                           7) = CAST('decimal' AS text)
                 THEN CAST('numeric' AS varchar)
               WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                           7) = CAST('varchar' AS text)
                 OR "left"(CAST(svv_redshift_columns.data_type AS text),
                           17) = CAST('character varying' AS text)
                 THEN CAST('character varying' AS varchar)
               WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                           7) = CAST('varbyte' AS text)
                 OR "left"(CAST(svv_redshift_columns.data_type AS text),
                           14) = CAST('binary varying' AS text)
                 THEN CAST('binary varying' AS varchar)
               WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                           4) = CAST('char' AS text)
                 OR "left"(CAST(svv_redshift_columns.data_type AS text),
                           9) = CAST('character' AS text)
                 THEN CAST('character' AS varchar)
               WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('information_schema.sql_identifier' AS text)
                 THEN CAST('sql_identifier' AS varchar)
               WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('information_schema.character_data' AS text)
                 THEN CAST('character_data' AS varchar)
               WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('information_schema.cardinal_number' AS text)
                 THEN CAST('cardinal_number' AS varchar)
               ELSE svv_redshift_columns.data_type
             END AS varchar(128)) AS data_type,
        CASE
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('varchar' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('character varying' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('char' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('varbyte' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('binary varying' AS text)
            THEN -1
          WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                      7) = CAST('varchar' AS text)
            THEN CAST(regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                      4) = CAST('char' AS text)
            THEN CAST(regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    4) AS integer)
          WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                      7) = CAST('varbyte' AS text)
            OR "left"(CAST(svv_redshift_columns.data_type AS text),
                      14) = CAST('binary varying' AS text)
            THEN CAST(regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('string' AS text)
            THEN 16383
          ELSE CAST(NULL AS integer)
        END AS character_maximum_length,
        CASE
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('int2' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('smallint' AS text)
            THEN 16
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('int' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('int4' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('integer' AS text)
            THEN 32
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('int8' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('bigint' AS text)
            THEN 64
          WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                      7) = CAST('decimal' AS text)
            THEN CAST(regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('float' AS text)
            THEN 24
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('double' AS text)
            THEN 53
          WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('numeric' AS text)
            OR CAST(svv_redshift_columns.data_type AS text) = CAST('decimal' AS text)
            THEN CAST(NULL AS integer)
          WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                      7) = CAST('numeric' AS text)
            THEN CAST(regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    0,
                                    1) AS integer)
          WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                      7) = CAST('decimal' AS text)
            THEN CAST(regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    0,
                                    1) AS integer)
          ELSE CAST(NULL AS integer)
        END AS numeric_precision,
        CAST(CASE
               WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('int2' AS text)
                 OR CAST(svv_redshift_columns.data_type AS text) = CAST('smallint' AS text)
                 OR CAST(svv_redshift_columns.data_type AS text) = CAST('int4' AS text)
                 OR CAST(svv_redshift_columns.data_type AS text) = CAST('int' AS text)
                 OR CAST(svv_redshift_columns.data_type AS text) = CAST('integer' AS text)
                 OR CAST(svv_redshift_columns.data_type AS text) = CAST('int8' AS text)
                 OR CAST(svv_redshift_columns.data_type AS text) = CAST('bigint' AS text)
                 THEN CAST('0' AS text)
               WHEN CAST(svv_redshift_columns.data_type AS text) = CAST('decimal' AS text)
                 OR CAST(svv_redshift_columns.data_type AS text) = CAST('numeric' AS text)
                 THEN CAST(NULL AS text)
               WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                           7) = CAST('decimal' AS text)
                 THEN regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    regexp_instr(CAST(svv_redshift_columns.data_type AS text),
                                                 CAST(',' AS text),
                                                 7))
               WHEN "left"(CAST(svv_redshift_columns.data_type AS text),
                           7) = CAST('numeric' AS text)
                 THEN regexp_substr(CAST(svv_redshift_columns.data_type AS text),
                                    CAST('[0-9]+' AS text),
                                    regexp_instr(CAST(svv_redshift_columns.data_type AS text),
                                                 CAST(',' AS text),
                                                 7))
               ELSE CAST(NULL AS text)
             END AS integer) AS numeric_scale,
        svv_redshift_columns.remarks
 FROM svv_redshift_columns

 UNION ALL

 SELECT CAST(current_database() AS varchar(128)) AS database_name,
        CAST(btrim(CAST(ext_columns.schemaname AS text)) AS varchar(128)) AS schema_name,
        CAST(btrim(CAST(ext_columns.tablename AS text)) AS varchar(128)) AS table_name,
        CAST(btrim(CAST(ext_columns.columnname AS text)) AS varchar(128)) AS column_name,
        ext_columns.columnnum AS ordinal_position,
        CAST(NULL AS unknown) AS column_default,
        CAST(CASE
               WHEN CAST(ext_columns.is_nullable AS text) = CAST('true' AS text)
                 THEN CAST('YES' AS text)
               WHEN CAST(ext_columns.is_nullable AS text) = CAST('false' AS text)
                 THEN CAST('NO' AS text)
               ELSE CAST('' AS text)
             END AS varchar(3)) AS is_nullable,
        CAST(CASE
               WHEN "left"(CAST(ext_columns.external_type AS text),
                           7) = CAST('varchar' AS text)
                 OR "left"(CAST(ext_columns.external_type AS text),
                           17) = CAST('character varying' AS text)
                 THEN CAST('character varying' AS varchar)
               WHEN "left"(CAST(ext_columns.external_type AS text),
                           7) = CAST('varbyte' AS text)
                 OR "left"(CAST(ext_columns.external_type AS text),
                           14) = CAST('binary varying' AS text)
                 THEN CAST('binary varying' AS varchar)
               WHEN "left"(CAST(ext_columns.external_type AS text),
                           4) = CAST('char' AS text)
                 THEN CAST('character' AS varchar)
               WHEN "left"(CAST(ext_columns.external_type AS text),
                           7) = CAST('decimal' AS text)
                 THEN CAST('numeric' AS varchar)
               WHEN "left"(CAST(ext_columns.external_type AS text),
                           7) = CAST('numeric' AS text)
                 THEN CAST('numeric' AS varchar)
               WHEN CAST(ext_columns.external_type AS text) = CAST('float' AS text)
                 THEN CAST('real' AS varchar)
               WHEN CAST(ext_columns.external_type AS text) = CAST('double' AS text)
                 THEN CAST('double precision' AS varchar)
               WHEN CAST(ext_columns.external_type AS text) = CAST('int' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('int4' AS text)
                 THEN CAST('integer' AS varchar)
               WHEN CAST(ext_columns.external_type AS text) = CAST('int2' AS text)
                 THEN CAST('smallint' AS varchar)
               ELSE ext_columns.external_type
             END AS varchar(128)) AS data_type,
        CASE
          WHEN CAST(ext_columns.external_type AS text) = CAST('varchar' AS text)
            OR CAST(ext_columns.external_type AS text) = CAST('character varying' AS text)
            THEN -1
          WHEN CAST(ext_columns.external_type AS text) = CAST('varbyte' AS text)
            OR CAST(ext_columns.external_type AS text) = CAST('binary varying' AS text)
            THEN -1
          WHEN "left"(CAST(ext_columns.external_type AS text),
                      7) = CAST('varchar' AS text)
            OR "left"(CAST(ext_columns.external_type AS text),
                      17) = CAST('character varying' AS text)
            THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN "left"(CAST(ext_columns.external_type AS text),
                      7) = CAST('varbyte' AS text)
            OR "left"(CAST(ext_columns.external_type AS text),
                      14) = CAST('binary varying' AS text)
            THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN CAST(ext_columns.external_type AS text) = CAST('char' AS text)
            THEN -1
          WHEN "left"(CAST(ext_columns.external_type AS text),
                      4) = CAST('char' AS text)
            THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    4) AS integer)
          WHEN CAST(ext_columns.external_type AS text) = CAST('string' AS text)
            THEN 16383
          ELSE CAST(NULL AS integer)
        END AS character_maximum_length,
        CASE
          WHEN CAST(ext_columns.external_type AS text) = CAST('numeric' AS text)
            OR CAST(ext_columns.external_type AS text) = CAST('decimal' AS text)
            THEN CAST(NULL AS integer)
          WHEN "left"(CAST(ext_columns.external_type AS text),
                      7) = CAST('decimal' AS text)
            THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN "left"(CAST(ext_columns.external_type AS text),
                      7) = CAST('numeric' AS text)
            THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN CAST(ext_columns.external_type AS text) = CAST('int2' AS text)
            THEN 16
          WHEN CAST(ext_columns.external_type AS text) = CAST('int' AS text)
            OR CAST(ext_columns.external_type AS text) = CAST('int4' AS text)
            OR CAST(ext_columns.external_type AS text) = CAST('integer' AS text)
            THEN 32
          WHEN CAST(ext_columns.external_type AS text) = CAST('bigint' AS text)
            THEN 64
          WHEN CAST(ext_columns.external_type AS text) = CAST('float' AS text)
            THEN 24
          WHEN CAST(ext_columns.external_type AS text) = CAST('double' AS text)
            THEN 53
          ELSE CAST(NULL AS integer)
        END AS numeric_precision,
        CAST(CASE
               WHEN CAST(ext_columns.external_type AS text) = CAST('decimal' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('numeric' AS text)
                 THEN CAST(NULL AS text)
               WHEN "left"(CAST(ext_columns.external_type AS text),
                           7) = CAST('decimal' AS text)
                 THEN regexp_substr(CAST(ext_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    regexp_instr(CAST(ext_columns.external_type AS text),
                                                 CAST(',' AS text),
                                                 7))
               WHEN "left"(CAST(ext_columns.external_type AS text),
                           7) = CAST('numeric' AS text)
                 THEN regexp_substr(CAST(ext_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    regexp_instr(CAST(ext_columns.external_type AS text),
                                                 CAST(',' AS text),
                                                 7))
               WHEN CAST(ext_columns.external_type AS text) = CAST('int2' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('smallint' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('int4' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('int' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('integer' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('int8' AS text)
                 OR CAST(ext_columns.external_type AS text) = CAST('bigint' AS text)
                 THEN CAST('0' AS text)
               ELSE CAST(NULL AS text)
             END AS integer) AS numeric_scale,
        CAST(NULL AS unknown) AS remarks
 FROM pg_get_external_columns() AS ext_columns(esoid integer,
                                               schemaname varchar,
                                               tablename varchar,
                                               columnname varchar,
                                               external_type varchar,
                                               columnnum integer,
                                               part_key integer,
                                               is_nullable varchar))

UNION ALL

SELECT CAST(btrim(CAST(ext_columns.databasename AS text)) AS varchar(128)) AS database_name,
       CAST(btrim(CAST(ext_columns.schemaname AS text)) AS varchar(128)) AS schema_name,
       CAST(btrim(CAST(ext_columns.tablename AS text)) AS varchar(128)) AS table_name,
       CAST(btrim(CAST(ext_columns.columnname AS text)) AS varchar(128)) AS column_name,
       ext_columns.columnnum AS ordinal_position,
       CAST(NULL AS unknown) AS column_default,
       CAST(CASE
              WHEN CAST(ext_columns.is_nullable AS text) = CAST('true' AS text)
                THEN CAST('YES' AS text)
              WHEN CAST(ext_columns.is_nullable AS text) = CAST('false' AS text)
                THEN CAST('NO' AS text)
              ELSE CAST('' AS text)
            END AS varchar(3)) AS is_nullable,
       CAST(CASE
              WHEN "left"(CAST(ext_columns.external_type AS text),
                          7) = CAST('varchar' AS text)
                OR "left"(CAST(ext_columns.external_type AS text),
                          17) = CAST('character varying' AS text)
                THEN CAST('character varying' AS varchar)
              WHEN "left"(CAST(ext_columns.external_type AS text),
                          7) = CAST('varbyte' AS text)
                OR "left"(CAST(ext_columns.external_type AS text),
                          14) = CAST('binary varying' AS text)
                THEN CAST('binary varying' AS varchar)
              WHEN "left"(CAST(ext_columns.external_type AS text),
                          4) = CAST('char' AS text)
                THEN CAST('character' AS varchar)
              WHEN "left"(CAST(ext_columns.external_type AS text),
                          7) = CAST('decimal' AS text)
                THEN CAST('numeric' AS varchar)
              WHEN "left"(CAST(ext_columns.external_type AS text),
                          7) = CAST('numeric' AS text)
                THEN CAST('numeric' AS varchar)
              WHEN CAST(ext_columns.external_type AS text) = CAST('float' AS text)
                THEN CAST('real' AS varchar)
              WHEN CAST(ext_columns.external_type AS text) = CAST('double' AS text)
                THEN CAST('double precision' AS varchar)
              WHEN CAST(ext_columns.external_type AS text) = CAST('int' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('int4' AS text)
                THEN CAST('integer' AS varchar)
              WHEN CAST(ext_columns.external_type AS text) = CAST('int2' AS text)
                THEN CAST('smallint' AS varchar)
              ELSE ext_columns.external_type
            END AS varchar(128)) AS data_type,
       CASE
         WHEN CAST(ext_columns.external_type AS text) = CAST('varchar' AS text)
           OR CAST(ext_columns.external_type AS text) = CAST('character varying' AS text)
           THEN -1
         WHEN CAST(ext_columns.external_type AS text) = CAST('varbyte' AS text)
           OR CAST(ext_columns.external_type AS text) = CAST('binary varying' AS text)
           THEN -1
         WHEN "left"(CAST(ext_columns.external_type AS text),
                     7) = CAST('varchar' AS text)
           OR "left"(CAST(ext_columns.external_type AS text),
                     17) = CAST('character varying' AS text)
           THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                   CAST('[0-9]+' AS text),
                                   7) AS integer)
         WHEN "left"(CAST(ext_columns.external_type AS text),
                     7) = CAST('varbyte' AS text)
           OR "left"(CAST(ext_columns.external_type AS text),
                     14) = CAST('binary varying' AS text)
           THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                   CAST('[0-9]+' AS text),
                                   7) AS integer)
         WHEN CAST(ext_columns.external_type AS text) = CAST('char' AS text)
           THEN -1
         WHEN "left"(CAST(ext_columns.external_type AS text),
                     4) = CAST('char' AS text)
           THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                   CAST('[0-9]+' AS text),
                                   4) AS integer)
         WHEN CAST(ext_columns.external_type AS text) = CAST('string' AS text)
           THEN 16383
         ELSE CAST(NULL AS integer)
       END AS character_maximum_length,
       CASE
         WHEN CAST(ext_columns.external_type AS text) = CAST('numeric' AS text)
           OR CAST(ext_columns.external_type AS text) = CAST('decimal' AS text)
           THEN CAST(NULL AS integer)
         WHEN "left"(CAST(ext_columns.external_type AS text),
                     7) = CAST('decimal' AS text)
           THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                   CAST('[0-9]+' AS text),
                                   7) AS integer)
         WHEN "left"(CAST(ext_columns.external_type AS text),
                     7) = CAST('numeric' AS text)
           THEN CAST(regexp_substr(CAST(ext_columns.external_type AS text),
                                   CAST('[0-9]+' AS text),
                                   7) AS integer)
         WHEN CAST(ext_columns.external_type AS text) = CAST('int2' AS text)
           THEN 16
         WHEN CAST(ext_columns.external_type AS text) = CAST('int' AS text)
           OR CAST(ext_columns.external_type AS text) = CAST('int4' AS text)
           OR CAST(ext_columns.external_type AS text) = CAST('integer' AS text)
           THEN 32
         WHEN CAST(ext_columns.external_type AS text) = CAST('bigint' AS text)
           THEN 64
         WHEN CAST(ext_columns.external_type AS text) = CAST('float' AS text)
           THEN 24
         WHEN CAST(ext_columns.external_type AS text) = CAST('double' AS text)
           THEN 53
         ELSE CAST(NULL AS integer)
       END AS numeric_precision,
       CAST(CASE
              WHEN CAST(ext_columns.external_type AS text) = CAST('decimal' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('numeric' AS text)
                THEN CAST(NULL AS text)
              WHEN "left"(CAST(ext_columns.external_type AS text),
                          7) = CAST('decimal' AS text)
                THEN regexp_substr(CAST(ext_columns.external_type AS text),
                                   CAST('[0-9]+' AS text),
                                   regexp_instr(CAST(ext_columns.external_type AS text),
                                                CAST(',' AS text),
                                                7))
              WHEN "left"(CAST(ext_columns.external_type AS text),
                          7) = CAST('numeric' AS text)
                THEN regexp_substr(CAST(ext_columns.external_type AS text),
                                   CAST('[0-9]+' AS text),
                                   regexp_instr(CAST(ext_columns.external_type AS text),
                                                CAST(',' AS text),
                                                7))
              WHEN CAST(ext_columns.external_type AS text) = CAST('int2' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('smallint' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('int4' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('int' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('integer' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('int8' AS text)
                OR CAST(ext_columns.external_type AS text) = CAST('bigint' AS text)
                THEN CAST('0' AS text)
              ELSE CAST(NULL AS text)
            END AS integer) AS numeric_scale,
       CAST(NULL AS unknown) AS remarks
FROM pg_get_all_external_columns() AS ext_columns(databasename varchar,
                                                  schemaname varchar,
                                                  tablename varchar,
                                                  esoid integer,
                                                  columnname varchar,
                                                  external_type varchar,
                                                  columnnum integer,
                                                  part_key integer,
                                                  is_nullable varchar)


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