Redshift Observatory

System Table Tracker

System view pg_catalog.svv_all_columns version 1.0.60353 / 2023-11-24

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(btrim(CAST(ext_columns.redshift_database_name 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 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 CAST('character varying[]' AS varchar)
                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('numeric[]' AS varchar)
                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('varchar[]' AS text)
             OR CAST(ext_columns.external_type AS text) = CAST('character varying' 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)
             OR 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)
                  OR 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 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(es_or_edb_oid integer,
                                                redshift_database_name varchar,
                                                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.redshift_database_name 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_external_database_columns() AS ext_columns(es_or_edb_oid integer,
                                                        redshift_database_name varchar,
                                                        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