Redshiftでテーブルのカラム番号・名前・型・桁数を抽出する
2023.02.21
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部のueharaです。
今回はAmazon Redshiftでテーブルのカラム番号・名前・型・桁数を抽出するという処理をしてみたいと思います。
方法1
SVV_COLUMNSを利用して、以下のように書くことができます。
SELECT
table_schema AS schema_name,
table_name,
ordinal_position AS column_number,
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale
FROM SVV_COLUMNS
-- 確認不要なスキーマをフィルタ
WHERE table_schema NOT IN ('admin', 'information_schema', 'pg_catalog')
ORDER BY schema_name, table_name, column_number
結果は、例えば次のようになります。
| schema_name | table_name | column_number | column_name | data_type | character_maximum_length | numeric_precision | numeric_scale |
|---|---|---|---|---|---|---|---|
| schema_a | table_a | 1 | column_1 | character varying | 16 | [NULL] | [NULL] |
| schema_a | table_a | 2 | column_2 | date | [NULL] | [NULL] | [NULL] |
| schema_a | table_a | 3 | column_3 | numeric | [NULL] | 32 | 2 |
| schema_a | table_b | 1 | column_1 | integer | [NULL] | 32 | 0 |
NUMERIC(precision, scale)といった、precisionとscaleの桁数をそれぞれカラムを分けて表示したい場合は、こちらを使うと良いと思います。
該当しない項目（例：VARCHARにおけるnumeric_precision等）にはNULLが入ります。
方法2
pg_attribute, pg_class, pg_namespaceを利用して、以下のように書くことができます。
SELECT
ns.nspname AS schema_name,
cls.relname AS table_name,
atr.attnum AS column_number,
atr.attname AS column_name,
pg_catalog.format_type(atr.atttypid, atr.atttypmod) AS data_type
FROM pg_attribute atr
JOIN pg_class cls ON atr.attrelid = cls.oid
JOIN pg_namespace ns ON cls.relnamespace = ns.oid
WHERE
column_number > 0 AND
NOT atr.attisdropped AND
-- 確認不要なスキーマをフィルタ
schema_name NOT LIKE 'pg_%' AND
schema_name NOT IN ('admin', 'information_schema')
ORDER BY schema_name, table_name, column_number
結果は、例えば次のようになります。
| schema_name | table_name | column_number | column_name | data_type |
|---|---|---|---|---|
| schema_a | table_a | 1 | column_1 | character varying(16) |
| schema_a | table_a | 2 | column_2 | date |
| schema_a | table_a | 3 | column_3 | numeric(32,2) |
| schema_a | table_b | 1 | column_1 | integer |
データ型と桁数を同じカラム内に表現したい場合は、こちらを使うと良いと思います。
その他の方法
カラムの型・桁を抽出したいテーブルが属するスキーマがsearch_pathに設定されている場合、PG_TABLE_DEFを利用して次のように書くこともできます。
set_pathについては公式ドキュメントをご確認下さい。
WITH tmp AS (
SELECT
table_schema AS schema_name,
table_name,
ordinal_position AS column_number,
column_name
FROM SVV_COLUMNS
)
SELECT
schemaname AS schema_name,
tablename AS table_name,
column_number,
"column" AS column_name,
type AS data_type
FROM PG_TABLE_DEF ptd
JOIN tmp ON
ptd.schemaname = tmp.schema_name AND
ptd.tablename = tmp.table_name AND
ptd."column" = tmp.column_name
-- 確認不要なスキーマをフィルタ
WHERE schema_name NOT IN ('pg_catalog')
ORDER BY schema_name, table_name, column_number
結果は「方法2」の表示結果と同じになります。
| schema_name | table_name | column_number | column_name | data_type |
|---|---|---|---|---|
| schema_a | table_a | 1 | column_1 | character varying(16) |
| schema_a | table_a | 2 | column_2 | date |
| schema_a | table_a | 3 | column_3 | numeric(32,2) |
| schema_a | table_b | 1 | column_1 | integer |
最後に
今回はAmazon Redshiftのテーブルのカラム番号・名前・型・桁数を抽出するを抽出するという処理をしてみました。
参考になりましたら幸いです。
