| schema | name | column | data type |
|---|---|---|---|
| information_schema | columns | character_maximum_length | cardinal_number |
| information_schema | columns | character_octet_length | cardinal_number |
| information_schema | columns | character_set_catalog | sql_identifier |
| information_schema | columns | character_set_name | sql_identifier |
| information_schema | columns | character_set_schema | sql_identifier |
| information_schema | columns | collation_catalog | sql_identifier |
| information_schema | columns | collation_name | sql_identifier |
| information_schema | columns | collation_schema | sql_identifier |
| information_schema | columns | column_default | character_data |
| information_schema | columns | column_name | sql_identifier |
| information_schema | columns | data_type | character_data |
| information_schema | columns | datetime_precision | cardinal_number |
| information_schema | columns | domain_catalog | sql_identifier |
| information_schema | columns | domain_name | sql_identifier |
| information_schema | columns | domain_schema | sql_identifier |
| information_schema | columns | dtd_identifier | sql_identifier |
| information_schema | columns | interval_precision | cardinal_number |
| information_schema | columns | interval_type | character_data |
| information_schema | columns | is_nullable | character_data |
| information_schema | columns | is_self_referencing | character_data |
| information_schema | columns | maximum_cardinality | cardinal_number |
| information_schema | columns | numeric_precision | cardinal_number |
| information_schema | columns | numeric_precision_radix | cardinal_number |
| information_schema | columns | numeric_scale | cardinal_number |
| information_schema | columns | ordinal_position | cardinal_number |
| information_schema | columns | scope_catalog | sql_identifier |
| information_schema | columns | scope_name | sql_identifier |
| information_schema | columns | scope_schema | sql_identifier |
| information_schema | columns | table_catalog | sql_identifier |
| information_schema | columns | table_name | sql_identifier |
| information_schema | columns | table_schema | sql_identifier |
| information_schema | columns | udt_catalog | sql_identifier |
| information_schema | columns | udt_name | sql_identifier |
| information_schema | columns | udt_schema | sql_identifier |
SELECT CAST(current_database() AS information_schema.sql_identifier) AS table_catalog,
CAST(nc.nspname AS information_schema.sql_identifier) AS table_schema,
CAST(c.relname AS information_schema.sql_identifier) AS table_name,
CAST(a.attname AS information_schema.sql_identifier) AS column_name,
CAST(a.attnum AS information_schema.cardinal_number) AS ordinal_position,
CAST(ad.adsrc AS information_schema.character_data) AS column_default,
CAST(CASE
WHEN a.attnotnull
OR (t.typtype = CAST('d' AS "char")
AND t.typnotnull)
THEN CAST('NO' AS text)
ELSE CAST('YES' AS text)
END AS information_schema.character_data) 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 text)
WHEN nbt.nspname = CAST('pg_catalog' AS name)
THEN format_type(t.typbasetype,
CAST(NULL AS integer))
ELSE CAST('USER-DEFINED' AS text)
END
ELSE CASE
WHEN t.typelem <> CAST(0 AS oid)
AND t.typlen = -1
THEN CAST('ARRAY' AS text)
WHEN nt.nspname = CAST('pg_catalog' AS name)
THEN format_type(a.atttypid,
CAST(NULL AS integer))
ELSE CAST('USER-DEFINED' AS text)
END
END AS information_schema.character_data) AS data_type,
CAST(information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.cardinal_number) AS character_maximum_length,
CAST(information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.cardinal_number) AS character_octet_length,
CAST(information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.cardinal_number) AS numeric_precision,
CAST(information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.cardinal_number) AS numeric_precision_radix,
CAST(information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.cardinal_number) AS numeric_scale,
CAST(information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.cardinal_number) AS datetime_precision,
CAST(information_schema._pg_interval_type(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.character_data) AS interval_type,
CAST(information_schema._pg_interval_precision(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*, t.*)) AS information_schema.cardinal_number) AS interval_precision,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_catalog,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_schema,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_name,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_catalog,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_schema,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_name,
CAST(CASE
WHEN t.typtype = CAST('d' AS "char")
THEN current_database()
ELSE CAST(NULL AS name)
END AS information_schema.sql_identifier) AS domain_catalog,
CAST(CASE
WHEN t.typtype = CAST('d' AS "char")
THEN nt.nspname
ELSE CAST(NULL AS name)
END AS information_schema.sql_identifier) AS domain_schema,
CAST(CASE
WHEN t.typtype = CAST('d' AS "char")
THEN t.typname
ELSE CAST(NULL AS name)
END AS information_schema.sql_identifier) AS domain_name,
CAST(current_database() AS information_schema.sql_identifier) AS udt_catalog,
CAST(COALESCE(nbt.nspname, nt.nspname) AS information_schema.sql_identifier) AS udt_schema,
CAST(COALESCE(bt.typname, t.typname) AS information_schema.sql_identifier) AS udt_name,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_catalog,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_schema,
CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_name,
CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS maximum_cardinality,
CAST(a.attnum AS information_schema.sql_identifier) AS dtd_identifier,
CAST(CAST('NO' AS information_schema.character_data) AS information_schema.character_data) AS is_self_referencing
FROM pg_attribute AS a
LEFT JOIN pg_attrdef AS ad ON a.attrelid = ad.adrelid
AND a.attnum = ad.adnum,
pg_class AS c,
pg_namespace AS nc,
pg_user AS u,
pg_type AS t
INNER JOIN pg_namespace AS nt ON t.typnamespace = nt.oid
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.attrelid = c.oid
AND a.atttypid = t.oid
AND u.usesysid = c.relowner
AND nc.oid = c.relnamespace
AND a.attnum > 0
AND NOT a.attisdropped
AND (c.relkind = CAST('r' AS "char")
OR c.relkind = CAST('v' AS "char"))
AND (u.usename = CAST("current_user"() AS name)
OR has_table_privilege(c.oid,
CAST('SELECT' AS text))
OR has_table_privilege(c.oid,
CAST('INSERT' AS text))
OR has_table_privilege(c.oid,
CAST('UPDATE' AS text))
OR has_table_privilege(c.oid,
CAST('REFERENCES' AS text))
OR has_column_privilege(c.oid,
a.attnum,
CAST('SELECT' AS text))
OR has_column_privilege(c.oid,
a.attnum,
CAST('UPDATE' AS text))) 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