| 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) |
((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