Redshift Observatory

System Table Tracker

System view pg_catalog.svv_columns version 1.0.67699 / 2024-05-26

schema name column data type
pg_catalog svv_columns character_maximum_length int4
pg_catalog svv_columns character_set_catalog varchar(128)
pg_catalog svv_columns character_set_name varchar(128)
pg_catalog svv_columns character_set_schema varchar(128)
pg_catalog svv_columns collation_catalog varchar(128)
pg_catalog svv_columns collation_name varchar(128)
pg_catalog svv_columns collation_schema varchar(128)
pg_catalog svv_columns column_default varchar(4000)
pg_catalog svv_columns column_name varchar(128)
pg_catalog svv_columns data_type varchar(128)
pg_catalog svv_columns datetime_precision int4
pg_catalog svv_columns domain_name varchar(128)
pg_catalog svv_columns interval_precision varchar(256)
pg_catalog svv_columns interval_type varchar(256)
pg_catalog svv_columns is_nullable varchar(128)
pg_catalog svv_columns numeric_precision int4
pg_catalog svv_columns numeric_precision_radix int4
pg_catalog svv_columns numeric_scale int4
pg_catalog svv_columns ordinal_position int4
pg_catalog svv_columns remarks varchar(256)
pg_catalog svv_columns table_catalog varchar(128)
pg_catalog svv_columns table_name varchar(128)
pg_catalog svv_columns table_schema varchar(128)

View Text

(SELECT CAST(current_database() AS varchar(128)) AS table_catalog,
        CAST(nc.nspname AS varchar(128)) AS table_schema,
        CAST(c.relname AS varchar(128)) AS table_name,
        CAST(a.attname AS varchar(128)) AS column_name,
        CAST(a.attnum AS integer) AS ordinal_position,
        CAST(ad.adsrc AS varchar(4000)) AS column_default,
        CASE
          WHEN a.attnotnull
            OR (t.typtype = CAST('d' AS "char")
            AND t.typnotnull)
            THEN CAST('NO' AS varchar)
          ELSE CAST('YES' AS varchar)
        END AS is_nullable,
        CAST(CASE
               WHEN t.typtype = CAST('d' AS "char")
                 THEN CASE
                        WHEN bt.typelem <> CAST(0 AS oid)
                         AND bt.typlen = -1
                          THEN CAST('ARRAY' AS varchar)
                        WHEN nbt.nspname = CAST('pg_catalog' AS name)
                          THEN CASE
                                 WHEN t.typbasetype = CAST(16 AS oid)
                                   THEN CAST('boolean' AS varchar)
                                 WHEN t.typbasetype = CAST(18 AS oid)
                                   THEN CAST('"char"' AS varchar)
                                 WHEN t.typbasetype = CAST(21 AS oid)
                                   THEN CAST('smallint' AS varchar)
                                 WHEN t.typbasetype = CAST(23 AS oid)
                                   THEN CAST('integer' AS varchar)
                                 WHEN t.typbasetype = CAST(20 AS oid)
                                   THEN CAST('bigint' AS varchar)
                                 WHEN t.typbasetype = CAST(700 AS oid)
                                   THEN CAST('real' AS varchar)
                                 WHEN t.typbasetype = CAST(701 AS oid)
                                   THEN CAST('double precision' AS varchar)
                                 WHEN t.typbasetype = CAST(1042 AS oid)
                                   THEN CAST('character' AS varchar)
                                 WHEN t.typbasetype = CAST(1043 AS oid)
                                   THEN CAST('character varying' AS varchar)
                                 WHEN t.typbasetype = CAST(1082 AS oid)
                                   THEN CAST('date' AS varchar)
                                 WHEN t.typbasetype = CAST(1083 AS oid)
                                   THEN CAST('time without time zone' AS varchar)
                                 WHEN t.typbasetype = CAST(1114 AS oid)
                                   THEN CAST('timestamp without time zone' AS varchar)
                                 WHEN t.typbasetype = CAST(1184 AS oid)
                                   THEN CAST('timestamp with time zone' AS varchar)
                                 WHEN t.typbasetype = CAST(1700 AS oid)
                                   THEN CAST('numeric' AS varchar)
                                 WHEN t.typbasetype = CAST(6551 AS oid)
                                   THEN CAST('binary varying' AS varchar)
                                 ELSE CAST(t.typname AS varchar(128))
                               END
                        ELSE CAST('USER-DEFINED' AS varchar)
                      END
               ELSE CASE
                      WHEN t.typelem <> CAST(0 AS oid)
                       AND t.typlen = -1
                        THEN CAST('ARRAY' AS varchar)
                      WHEN nt.nspname = CAST('pg_catalog' AS name)
                        THEN CASE
                               WHEN a.atttypid = CAST(16 AS oid)
                                 THEN CAST('boolean' AS varchar)
                               WHEN a.atttypid = CAST(18 AS oid)
                                 THEN CAST('"char"' AS varchar)
                               WHEN a.atttypid = CAST(21 AS oid)
                                 THEN CAST('smallint' AS varchar)
                               WHEN a.atttypid = CAST(23 AS oid)
                                 THEN CAST('integer' AS varchar)
                               WHEN a.atttypid = CAST(20 AS oid)
                                 THEN CAST('bigint' AS varchar)
                               WHEN a.atttypid = CAST(700 AS oid)
                                 THEN CAST('real' AS varchar)
                               WHEN a.atttypid = CAST(701 AS oid)
                                 THEN CAST('double precision' AS varchar)
                               WHEN a.atttypid = CAST(1042 AS oid)
                                 THEN CAST('character' AS varchar)
                               WHEN a.atttypid = CAST(1043 AS oid)
                                 THEN CAST('character varying' AS varchar)
                               WHEN a.atttypid = CAST(1082 AS oid)
                                 THEN CAST('date' AS varchar)
                               WHEN a.atttypid = CAST(1083 AS oid)
                                 THEN CAST('time without time zone' AS varchar)
                               WHEN a.atttypid = CAST(1114 AS oid)
                                 THEN CAST('timestamp without time zone' AS varchar)
                               WHEN a.atttypid = CAST(1184 AS oid)
                                 THEN CAST('timestamp with time zone' AS varchar)
                               WHEN a.atttypid = CAST(1700 AS oid)
                                 THEN CAST('numeric' AS varchar)
                               WHEN a.atttypid = CAST(6551 AS oid)
                                 THEN CAST('binary varying' AS varchar)
                               ELSE CAST(t.typname AS varchar(128))
                             END
                      ELSE CAST('USER-DEFINED' AS varchar)
                    END
             END AS varchar(128)) AS data_type,
        CASE
          WHEN a.atttypmod = -1
            THEN CAST(NULL AS integer)
          WHEN a.atttypid = CAST(1042 AS oid)
            OR a.atttypid = CAST(1043 AS oid)
            OR a.atttypid = CAST(6551 AS oid)
            THEN a.atttypmod - 4
          WHEN a.atttypid = CAST(1560 AS oid)
            OR a.atttypid = CAST(1562 AS oid)
            THEN a.atttypmod
          ELSE CAST(NULL AS integer)
        END AS character_maximum_length,
        CASE
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(21 AS oid)
            THEN 16
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(23 AS oid)
            THEN 32
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(20 AS oid)
            THEN 64
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(1700 AS oid)
            THEN CASE
                   WHEN CASE
                          WHEN t.typtype = CAST('d' AS "char")
                            THEN t.typtypmod
                          ELSE a.atttypmod
                        END = -1
                     THEN CAST(NULL AS integer)
                   ELSE ((CASE
                            WHEN t.typtype = CAST('d' AS "char")
                              THEN t.typtypmod
                            ELSE a.atttypmod
                          END - 4) >> 16) & 65535
                 END
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(700 AS oid)
            THEN 24
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(701 AS oid)
            THEN 53
          ELSE CAST(NULL AS integer)
        END AS numeric_precision,
        CASE
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(21 AS oid)
            OR CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(23 AS oid)
            OR CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(20 AS oid)
            OR CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(700 AS oid)
            OR CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(701 AS oid)
            THEN 2
          WHEN CASE
                 WHEN t.typtype = CAST('d' AS "char")
                   THEN t.typbasetype
                 ELSE a.atttypid
               END = CAST(1700 AS oid)
            THEN 10
          ELSE CAST(NULL AS integer)
        END AS numeric_precision_radix,
        CASE
          WHEN a.atttypid = CAST(21 AS oid)
            OR a.atttypid = CAST(23 AS oid)
            OR a.atttypid = CAST(20 AS oid)
            THEN 0
          WHEN a.atttypid = CAST(1700 AS oid)
            THEN CASE
                   WHEN a.atttypmod = -1
                     THEN CAST(NULL AS integer)
                   ELSE (a.atttypmod - 4) & 65535
                 END
          ELSE CAST(NULL AS integer)
        END AS numeric_scale,
        CASE
          WHEN a.atttypmod = -1
            THEN CAST(NULL AS integer)
          WHEN a.atttypid = CAST(1083 AS oid)
            OR a.atttypid = CAST(1114 AS oid)
            OR a.atttypid = CAST(1184 AS oid)
            OR a.atttypid = CAST(1266 AS oid)
            THEN a.atttypmod
          WHEN a.atttypid = CAST(1186 AS oid)
            THEN a.atttypmod & 65535
          ELSE CAST(NULL AS integer)
        END AS datetime_precision,
        CAST(NULL AS varchar) AS interval_type,
        CAST(NULL AS varchar) AS interval_precision,
        CAST(CAST(NULL AS varchar) AS varchar(128)) AS character_set_catalog,
        CAST(CAST(NULL AS varchar) AS varchar(128)) AS character_set_schema,
        CAST(CAST(NULL AS varchar) AS varchar(128)) AS character_set_name,
        CAST(CAST(NULL AS varchar) AS varchar(128)) AS collation_catalog,
        CAST(CAST(NULL AS varchar) AS varchar(128)) AS collation_schema,
        CAST(CASE
               WHEN a.atttypid = CAST(1042 AS oid)
                 OR a.atttypid = CAST(1043 AS oid)
                 OR a.atttypid = CAST(19 AS oid)
                 OR a.atttypid = CAST(25 AS oid)
                 THEN CASE
                        WHEN a.attencrypttype = 1
                          OR a.attrelid < CAST(100000 AS oid)
                          THEN CAST('case_sensitive' AS text)
                        WHEN a.attencrypttype = 2
                          THEN CAST('case_insensitive' AS text)
                        WHEN a.attencrypttype = 0
                          THEN db_collation()
                        ELSE CAST(NULL AS text)
                      END
               ELSE CAST(NULL AS text)
             END AS varchar(128)) AS collation_name,
        CAST(CASE
               WHEN t.typtype = CAST('d' AS "char")
                 THEN t.typname
               ELSE CAST(NULL AS name)
             END AS varchar(128)) AS domain_name,
        CAST(d.description AS varchar) AS remarks
 FROM pg_attribute AS a
      INNER JOIN pg_class AS c ON a.attrelid = c.oid
      INNER JOIN pg_namespace AS nc ON nc.oid = c.relnamespace
      INNER JOIN pg_user AS u ON u.usesysid = c.relowner
      INNER JOIN pg_type AS t ON a.atttypid = t.oid
      INNER JOIN pg_namespace AS nt ON t.typnamespace = nt.oid
      LEFT JOIN pg_attrdef AS ad ON a.attrelid = ad.adrelid
                                AND a.attnum = ad.adnum
      LEFT JOIN pg_description AS d ON c.oid = d.objoid
                                   AND a.attnum = d.objsubid
      LEFT JOIN (pg_type AS bt
                 INNER JOIN pg_namespace AS nbt ON bt.typnamespace = nbt.oid)
         ON t.typtype = CAST('d' AS "char")
        AND t.typbasetype = bt.oid
 WHERE a.attnum > 0
   AND NOT a.attisdropped
   AND (c.relkind = CAST('r' AS "char")
     OR c.relkind = CAST('v' AS "char"))

 UNION ALL

 SELECT CAST(current_database() AS varchar(128)) AS table_catalog,
        svv_external_columns.schemaname AS table_schema,
        svv_external_columns.tablename AS table_name,
        svv_external_columns.columnname AS column_name,
        svv_external_columns.columnnum AS ordinal_position,
        CAST(NULL AS unknown) AS column_default,
        svv_external_columns.is_nullable,
        CASE
          WHEN CAST(svv_external_columns.external_type AS text) ~~* CAST('%[]%' AS text)
            THEN svv_external_columns.external_type
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('varchar' AS text)
            OR "left"(CAST(svv_external_columns.external_type AS text),
                      17) = CAST('character varying' AS text)
            THEN CAST('character varying' AS varchar)
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('varbyte' AS text)
            OR "left"(CAST(svv_external_columns.external_type AS text),
                      14) = CAST('binary varying' AS text)
            THEN CAST('binary varying' AS varchar)
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      4) = CAST('char' AS text)
            THEN CAST('character' AS varchar)
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('decimal' AS text)
            THEN CAST('numeric' AS varchar)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('float' AS text)
            THEN CAST('real' AS varchar)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('double' AS text)
            THEN CAST('double precision' AS varchar)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('int' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('int4' AS text)
            THEN CAST('integer' AS varchar)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('int2' AS text)
            THEN CAST('smallint' AS varchar)
          ELSE svv_external_columns.external_type
        END AS data_type,
        CASE
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('varchar' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('character varying' AS text)
            OR CAST(svv_external_columns.external_type AS text) ~~* CAST('%[]%' AS text)
            THEN -1
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('varbyte' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('binary varying' AS text)
            THEN -1
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('varbyte' AS text)
            OR "left"(CAST(svv_external_columns.external_type AS text),
                      14) = CAST('binary varying' AS text)
            THEN CAST(regexp_substr(CAST(svv_external_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('varchar' AS text)
            OR "left"(CAST(svv_external_columns.external_type AS text),
                      17) = CAST('character varying' AS text)
            THEN CAST(regexp_substr(CAST(svv_external_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('char' AS text)
            THEN -1
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      4) = CAST('char' AS text)
            THEN CAST(regexp_substr(CAST(svv_external_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    4) AS integer)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('string' AS text)
            THEN 16383
          ELSE CAST(NULL AS integer)
        END AS character_maximum_length,
        CASE
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('decimal' AS text)
            THEN CAST(NULL AS integer)
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('decimal' AS text)
            THEN CAST(regexp_substr(CAST(svv_external_columns.external_type AS text),
                                    CAST('[0-9]+' AS text),
                                    7) AS integer)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('int2' AS text)
            THEN 16
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('int' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('int4' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('integer' AS text)
            THEN 32
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('bigint' AS text)
            THEN 64
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('float' AS text)
            THEN 24
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('double' AS text)
            THEN 53
          ELSE CAST(NULL AS integer)
        END AS numeric_precision,
        CASE
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('smallint' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('integer' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('bigint' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('float' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('double' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('int' AS text)
            THEN 2
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('decimal' AS text)
            THEN 10
          ELSE CAST(NULL AS integer)
        END AS numeric_precision_radix,
        CASE
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('decimal' AS text)
            THEN CAST(NULL AS integer)
          WHEN "left"(CAST(svv_external_columns.external_type AS text),
                      7) = CAST('decimal' AS text)
            THEN CAST(CASE
                        WHEN regexp_instr(CAST(svv_external_columns.external_type AS text),
                                          CAST(',' AS text),
                                          7) = 0
                          THEN CAST('0' AS text)
                        ELSE regexp_substr(CAST(svv_external_columns.external_type AS text),
                                           CAST('[0-9]+' AS text),
                                           regexp_instr(CAST(svv_external_columns.external_type AS text),
                                                        CAST(',' AS text),
                                                        7))
                      END AS integer)
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('smallint' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('integer' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('bigint' AS text)
            OR CAST(svv_external_columns.external_type AS text) = CAST('int' AS text)
            THEN 0
          ELSE CAST(NULL AS integer)
        END AS numeric_scale,
        CASE
          WHEN CAST(svv_external_columns.external_type AS text) = CAST('timestamp' AS text)
            THEN 6
          ELSE CAST(NULL AS integer)
        END AS datetime_precision,
        CAST(NULL AS unknown) AS interval_type,
        CAST(NULL AS unknown) AS interval_precision,
        CAST(NULL AS unknown) AS character_set_catalog,
        CAST(NULL AS unknown) AS character_set_schema,
        CAST(NULL AS unknown) AS character_set_name,
        CAST(NULL AS unknown) AS collation_catalog,
        CAST(NULL AS unknown) AS collation_schema,
        CAST(CASE
               WHEN "left"(CAST(svv_external_columns.external_type AS text),
                           7) = CAST('varchar' AS text)
                 OR "left"(CAST(svv_external_columns.external_type AS text),
                           17) = CAST('character varying' AS text)
                 OR "left"(CAST(svv_external_columns.external_type AS text),
                           4) = CAST('char' AS text)
                 THEN db_collation()
               ELSE CAST(NULL AS text)
             END AS varchar(128)) AS collation_name,
        CAST(NULL AS unknown) AS domain_name,
        CAST(NULL AS unknown) AS remarks
 FROM svv_external_columns)

UNION ALL

SELECT CAST(current_database() AS varchar(128)) AS table_catalog,
       CAST(lbv_columns.schemaname AS varchar(128)) AS table_schema,
       CAST(lbv_columns.tablename AS varchar(128)) AS table_name,
       CAST(lbv_columns.columnname AS varchar(128)) AS column_name,
       lbv_columns.columnnum AS ordinal_position,
       CAST(NULL AS unknown) AS column_default,
       CAST(NULL AS unknown) AS is_nullable,
       CAST(CASE
              WHEN "left"(CAST(lbv_columns.columntype AS text),
                          18) = CAST('character varying(' AS text)
                THEN CAST('character varying' AS varchar)
              WHEN "left"(CAST(lbv_columns.columntype AS text),
                          10) = CAST('character(' AS text)
                THEN CAST('character' AS varchar)
              WHEN "left"(CAST(lbv_columns.columntype AS text),
                          8) = CAST('numeric(' AS text)
                THEN CAST('numeric' AS varchar)
              WHEN "left"(CAST(lbv_columns.columntype AS text),
                          15) = CAST('binary varying(' AS text)
                THEN CAST('binary varying' AS varchar)
              ELSE lbv_columns.columntype
            END AS varchar(128)) AS data_type,
       CASE
         WHEN "left"(CAST(lbv_columns.columntype AS text),
                     18) = CAST('character varying(' AS text)
           THEN CAST(regexp_substr(CAST(lbv_columns.columntype AS text),
                                   CAST('[0-9]+' AS text),
                                   19) AS integer)
         WHEN "left"(CAST(lbv_columns.columntype AS text),
                     10) = CAST('character(' AS text)
           THEN CAST(regexp_substr(CAST(lbv_columns.columntype AS text),
                                   CAST('[0-9]+' AS text),
                                   11) AS integer)
         WHEN "left"(CAST(lbv_columns.columntype AS text),
                     15) = CAST('binary varying(' AS text)
           THEN CAST(regexp_substr(CAST(lbv_columns.columntype AS text),
                                   CAST('[0-9]+' AS text),
                                   16) AS integer)
         ELSE CAST(NULL AS integer)
       END AS character_maximum_length,
       CASE
         WHEN CAST(lbv_columns.columntype AS text) = CAST('numeric' AS text)
           THEN CAST(NULL AS integer)
         WHEN "left"(CAST(lbv_columns.columntype AS text),
                     7) = CAST('numeric' AS text)
           THEN CAST(regexp_substr(CAST(lbv_columns.columntype AS text),
                                   CAST('[0-9]+' AS text),
                                   7) AS integer)
         WHEN CAST(lbv_columns.columntype AS text) = CAST('smallint' AS text)
           THEN 16
         WHEN CAST(lbv_columns.columntype AS text) = CAST('integer' AS text)
           THEN 32
         WHEN CAST(lbv_columns.columntype AS text) = CAST('bigint' AS text)
           THEN 64
         WHEN CAST(lbv_columns.columntype AS text) = CAST('double precision' AS text)
           THEN 53
         WHEN CAST(lbv_columns.columntype AS text) = CAST('real' AS text)
           THEN 24
         ELSE CAST(NULL AS integer)
       END AS numeric_precision,
       CASE
         WHEN CAST(lbv_columns.columntype AS text) = CAST('smallint' AS text)
           OR CAST(lbv_columns.columntype AS text) = CAST('integer' AS text)
           OR CAST(lbv_columns.columntype AS text) = CAST('bigint' AS text)
           OR CAST(lbv_columns.columntype AS text) = CAST('double precision' AS text)
           OR CAST(lbv_columns.columntype AS text) = CAST('real' AS text)
           THEN 2
         WHEN "left"(CAST(lbv_columns.columntype AS text),
                     7) = CAST('numeric' AS text)
           THEN 10
         ELSE CAST(NULL AS integer)
       END AS numeric_precision_radix,
       CASE
         WHEN CAST(lbv_columns.columntype AS text) = CAST('numeric' AS text)
           THEN CAST(NULL AS integer)
         WHEN "left"(CAST(lbv_columns.columntype AS text),
                     7) = CAST('numeric' AS text)
           THEN CAST(CASE
                       WHEN regexp_instr(CAST(lbv_columns.columntype AS text),
                                         CAST(',' AS text),
                                         7) = 0
                         THEN CAST('0' AS text)
                       ELSE regexp_substr(CAST(lbv_columns.columntype AS text),
                                          CAST('[0-9]+' AS text),
                                          regexp_instr(CAST(lbv_columns.columntype AS text),
                                                       CAST(',' AS text),
                                                       7))
                     END AS integer)
         WHEN CAST(lbv_columns.columntype AS text) = CAST('smallint' AS text)
           OR CAST(lbv_columns.columntype AS text) = CAST('integer' AS text)
           OR CAST(lbv_columns.columntype AS text) = CAST('bigint' AS text)
           THEN 0
         ELSE CAST(NULL AS integer)
       END AS numeric_scale,
       CASE
         WHEN CAST(lbv_columns.columntype AS text) = CAST('timestamp with time zone' AS text)
           OR CAST(lbv_columns.columntype AS text) = CAST('timestamp without time zone' AS text)
           THEN 6
         ELSE CAST(NULL AS integer)
       END AS datetime_precision,
       CAST(NULL AS unknown) AS interval_type,
       CAST(NULL AS unknown) AS interval_precision,
       CAST(NULL AS unknown) AS character_set_catalog,
       CAST(NULL AS unknown) AS character_set_schema,
       CAST(NULL AS unknown) AS character_set_name,
       CAST(NULL AS unknown) AS collation_catalog,
       CAST(NULL AS unknown) AS collation_schema,
       CAST(CASE
              WHEN lbv_columns.columncollation = 1
                THEN CAST('case_sensitive' AS text)
              WHEN lbv_columns.columncollation = 2
                THEN CAST('case_insensitive' AS text)
              WHEN lbv_columns.columncollation = 0
                THEN CASE
                       WHEN "left"(CAST(lbv_columns.columntype AS text),
                                   18) = CAST('character varying(' AS text)
                         OR "left"(CAST(lbv_columns.columntype AS text),
                                   10) = CAST('character(' AS text)
                         THEN db_collation()
                       ELSE CAST(NULL AS text)
                     END
              ELSE CAST(NULL AS text)
            END AS varchar(128)) AS collation_name,
       CAST(NULL AS unknown) AS domain_name,
       CAST(NULL AS unknown) AS remarks
FROM (SELECT lbv_cols.schemaname,
             lbv_cols.tablename,
             lbv_cols.columnname,
             lbv_cols.columntype,
             lbv_cols.columnnum,
             lbv_cols.columncollation
      FROM pg_get_lbvcols_collation() AS lbv_cols(schemaname name,
                                                  tablename name,
                                                  columnname name,
                                                  columntype varchar,
                                                  columnnum integer,
                                                  columncollation integer)) AS lbv_columns


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