[PosgreSQL]テーブルやカラムの一覧をSQLで取得する
| 実行例で使用しているデータベースのサンプルは、 PostgreSQL のサンプルデータ を利用している。 |
1. テーブル一覧
SELECT
schemaname,
relname AS table_name
FROM
pg_stat_user_tables
ORDER BY
table_name;テーブル一覧の例
dvdrental=# SELECT
dvdrental-# schemaname,
dvdrental-# relname AS table_name
dvdrental-# FROM
dvdrental-# pg_stat_user_tables
dvdrental-# ORDER BY
dvdrental-# table_name;
schemaname | table_name
------------+---------------
public | actor
public | address
public | category
public | city
public | country
public | customer
public | film
public | film_actor
public | film_category
public | inventory
public | language
public | payment
public | rental
public | staff
public | store
(15 行)2. カラム一覧
テーブルのカラムを取得する
SELECT
table_catalog AS databasename,
table_schema AS schemaname,
table_name,
column_name,
ordinal_position,
column_default,
is_nullable,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale
FROM
information_schema.columns
WHERE
table_catalog = '<database name>'
AND table_name = '<table name>'
ORDER BY
ordinal_position;dvdrental.filmテーブルのカラム例
dvdrental=# SELECT
dvdrental-# table_catalog AS databasename,
dvdrental-# table_schema AS schemaname,
dvdrental-# table_name,
dvdrental-# column_name,
dvdrental-# ordinal_position,
dvdrental-# column_default,
dvdrental-# is_nullable,
dvdrental-# data_type,
dvdrental-# character_maximum_length,
dvdrental-# numeric_precision,
dvdrental-# numeric_scale
dvdrental-# FROM
dvdrental-# information_schema.columns
dvdrental-# WHERE
dvdrental-# table_catalog = 'dvdrental'
dvdrental-# AND table_name = 'film'
dvdrental-# ORDER BY
dvdrental-# ordinal_position;
databasename | schemaname | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | numeric_precision | numeric_scale
--------------+------------+------------+------------------+------------------+---------------------------------------+-------------+-----------------------------+--------------------------+-------------------+---------------
dvdrental | public | film | film_id | 1 | nextval('film_film_id_seq'::regclass) | NO | integer | | 32 | 0
dvdrental | public | film | title | 2 | | NO | character varying | 255 | |
dvdrental | public | film | description | 3 | | YES | text | | |
dvdrental | public | film | release_year | 4 | | YES | integer | | 32 | 0
dvdrental | public | film | language_id | 5 | | NO | smallint | | 16 | 0
dvdrental | public | film | rental_duration | 6 | 3 | NO | smallint | | 16 | 0
dvdrental | public | film | rental_rate | 7 | 4.99 | NO | numeric | | 4 | 2
dvdrental | public | film | length | 8 | | YES | smallint | | 16 | 0
dvdrental | public | film | replacement_cost | 9 | 19.99 | NO | numeric | | 5 | 2
dvdrental | public | film | rating | 10 | 'G'::mpaa_rating | YES | USER-DEFINED | | |
dvdrental | public | film | last_update | 11 | now() | NO | timestamp without time zone | | |
dvdrental | public | film | special_features | 12 | | YES | ARRAY | | |
dvdrental | public | film | fulltext | 13 | | NO | tsvector | | |
(13 行)3. プライマリキー
テーブルのプライマリキーを取得する
SELECT
ccu.constraint_name AS constraint_name,
ccu.column_name AS column_name
FROM
information_schema.table_constraints tc,
information_schema.constraint_column_usage ccu
WHERE
tc.table_catalog = '<database name>'
AND tc.table_name = '<table name>'
AND tc.constraint_type = 'PRIMARY KEY'
AND tc.table_catalog = ccu.table_catalog
AND tc.table_schema = ccu.table_schema
AND tc.table_name = ccu.table_name
AND tc.constraint_name = ccu.constraint_name;dvdrental.filmテーブルのプライマリキーの例
dvdrental=# SELECT
dvdrental-# ccu.constraint_name AS constraint_name,
dvdrental-# ccu.column_name AS column_name
dvdrental-# FROM
dvdrental-# information_schema.table_constraints tc,
dvdrental-# information_schema.constraint_column_usage ccu
dvdrental-# WHERE
dvdrental-# tc.table_catalog = 'dvdrental'
dvdrental-# AND tc.table_name = 'film'
dvdrental-# AND tc.constraint_type = 'PRIMARY KEY'
dvdrental-# AND tc.table_catalog = ccu.table_catalog
dvdrental-# AND tc.table_schema = ccu.table_schema
dvdrental-# AND tc.table_name = ccu.table_name
dvdrental-# AND tc.constraint_name = ccu.constraint_name;
constraint_name | column_name
-----------------+-------------
film_pkey | film_id
(1 行)4. ユニークキー
SELECT
tc.table_name,
ccu.constraint_name AS constraint_name,
ccu.column_name AS column_name
FROM
information_schema.table_constraints tc,
information_schema.constraint_column_usage ccu
WHERE
tc.table_catalog = '<database name>'
AND tc.table_name = '<table name>'
AND tc.constraint_type = 'UNIQUE'
AND tc.table_catalog = ccu.table_catalog
AND tc.table_schema = ccu.table_schema
AND tc.table_name = ccu.table_name
AND tc.constraint_name = ccu.constraint_name
ORDER BY
constraint_name;ユニークキーの例
保留5. テーブルコメント
SELECT
psut.relname AS table_name,
pd.description AS table_comment
FROM
pg_stat_user_tables psut,
pg_description pd
WHERE
psut.relname = '<table name>'
AND psut.relid = pd.objoid
AND pd.objsubid = 0;cityテーブルのコメントの例
dvdrental=# SELECT
dvdrental-# psut.relname AS table_name,
dvdrental-# pd.description AS table_comment
dvdrental-# FROM
dvdrental-# pg_stat_user_tables psut,
dvdrental-# pg_description pd
dvdrental-# WHERE
dvdrental-# psut.relname = 'city'
dvdrental-# AND psut.relid = pd.objoid
dvdrental-# AND pd.objsubid = 0;
table_name | table_comment
------------+---------------
city | 都市テーブル
(1 行)| オリジナルのサンプルデータにはコメントのあるテーブルがないので、自前でコメントを定義しています。 |
6. カラムのコメント
テーブルのカラムのコメントを取得する。
SELECT
psat.relname AS table_name,
pa.attname AS column_name,
pd.description AS column_comment
FROM
pg_stat_all_tables psat,
pg_description pd,
pg_attribute pa
WHERE
psat.schemaname = (
SELECT
schemaname
FROM
pg_stat_user_tables
WHERE
relname = '<table name>'
)
AND psat.relname = '<table name>'
AND psat.relid = pd.objoid
AND pd.objsubid <> 0
AND pd.objoid = pa.attrelid
AND pd.objsubid = pa.attnum
ORDER BY
pd.objsubid;cityテーブルの各カラムのコメントの例
dvdrental=# SELECT
dvdrental-# psat.relname AS table_name,
dvdrental-# pa.attname AS column_name,
dvdrental-# pd.description AS column_comment
dvdrental-# FROM
dvdrental-# pg_stat_all_tables psat,
dvdrental-# pg_description pd,
dvdrental-# pg_attribute pa
dvdrental-# WHERE
dvdrental-# psat.schemaname = (
dvdrental(# SELECT
dvdrental(# schemaname
dvdrental(# FROM
dvdrental(# pg_stat_user_tables
dvdrental(# WHERE
dvdrental(# relname = 'city'
dvdrental(# )
dvdrental-# AND psat.relname = 'city'
dvdrental-# AND psat.relid = pd.objoid
dvdrental-# AND pd.objsubid <> 0
dvdrental-# AND pd.objoid = pa.attrelid
dvdrental-# AND pd.objsubid = pa.attnum
dvdrental-# ORDER BY
dvdrental-# pd.objsubid;
table_name | column_name | column_comment
------------+-------------+----------------
city | city | 都市名
city | country_id | 国id
city | last_update | 更新日時
(3 行)| オリジナルのサンプルデータにはコメントのあるカラムがないので、自前でコメントを定義しています。 |
7. シーケンス
シーケンス一覧を取得します。
SELECT
*
FROM
information_schema.sequences sq
WHERE
sq.sequence_catalog = '<database name>'
ORDER BY
sq.sequence_name;dvdrentalのシーケンス一覧の例
dvdrental=# SELECT
dvdrental-# *
dvdrental-# FROM
dvdrental-# information_schema.sequences sq
dvdrental-# WHERE
dvdrental-# sq.sequence_catalog = 'dvdrental'
dvdrental-# ORDER BY
dvdrental-# sq.sequence_name;
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cyc
le_option
------------------+-----------------+----------------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+----
----------
dvdrental | public | actor_actor_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | address_address_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | category_category_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | city_city_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | country_country_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | customer_customer_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | film_film_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | inventory_inventory_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | language_language_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | payment_payment_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | rental_rental_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | staff_staff_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
dvdrental | public | store_store_id_seq | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO
(13 行)8. トリガー一覧
SELECT
t.trigger_name,
t.event_manipulation,
t.event_object_table,
t.action_statement,
t.action_order,
t.action_condition,
t.action_orientation,
t.action_timing
FROM
information_schema.triggers t
WHERE
t.trigger_catalog = '<database name>'
ORDER BY
t.event_object_table,
t.action_order;dvdrentalのトリガー一覧の例
dvdrental=# SELECT
dvdrental-# t.trigger_name,
dvdrental-# t.event_manipulation,
dvdrental-# t.event_object_table,
dvdrental-# t.action_statement,
dvdrental-# t.action_order,
dvdrental-# t.action_condition,
dvdrental-# t.action_orientation,
dvdrental-# t.action_timing
dvdrental-# FROM
dvdrental-# information_schema.triggers t
dvdrental-# WHERE
dvdrental-# t.trigger_catalog = 'dvdrental'
dvdrental-# ORDER BY
dvdrental-# t.event_object_table,
dvdrental-# t.action_order;
trigger_name | event_manipulation | event_object_table | action_statement | action_order | action_condition | action_orientation | action_timing
-----------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------+--------------+------------------+--------------------+---------------
last_updated | UPDATE | actor | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | address | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | category | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | city | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | country | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | customer | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
film_fulltext_trigger | INSERT | film | EXECUTE PROCEDURE tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description') | | | ROW | BEFORE
film_fulltext_trigger | UPDATE | film | EXECUTE PROCEDURE tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description') | | | ROW | BEFORE
last_updated | UPDATE | film | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | film_actor | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | film_category | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | inventory | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | language | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | rental | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | staff | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
last_updated | UPDATE | store | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
(16 行)9. テーブルのトリガー一覧
SELECT
t.trigger_name,
t.event_manipulation,
t.event_object_table,
t.action_statement,
t.action_order,
t.action_condition,
t.action_orientation,
t.action_timing
FROM
information_schema.triggers t
WHERE
t.trigger_catalog = '<database name>'
AND t.event_object_table = '<table name>'
ORDER BY
t.action_order;dvdrental.filmテーブルのトリガーの例
dvdrental=# SELECT
dvdrental-# t.trigger_name,
dvdrental-# t.event_manipulation,
dvdrental-# t.event_object_table,
dvdrental-# t.action_statement,
dvdrental-# t.action_order,
dvdrental-# t.action_condition,
dvdrental-# t.action_orientation,
dvdrental-# t.action_timing
dvdrental-# FROM
dvdrental-# information_schema.triggers t
dvdrental-# WHERE
dvdrental-# t.trigger_catalog = 'dvdrental'
dvdrental-# AND t.event_object_table = 'film'
dvdrental-# ORDER BY
dvdrental-# t.action_order;
trigger_name | event_manipulation | event_object_table | action_statement | action_order | action_condition | action_orientation | action_timing
-----------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------+--------------+------------------+--------------------+---------------
film_fulltext_trigger | INSERT | film | EXECUTE PROCEDURE tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description') | | | ROW | BEFORE
film_fulltext_trigger | UPDATE | film | EXECUTE PROCEDURE tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description') | | | ROW | BEFORE
last_updated | UPDATE | film | EXECUTE PROCEDURE last_updated() | | | ROW | BEFORE
(3 行)10. ビュ一覧
SELECT
v.*
FROM
information_schema.views v,
information_schema.view_table_usage AS vt
WHERE
v.table_catalog = '<database name>'
AND v.table_catalog = vt.view_catalog
AND v.table_schema = vt.view_schema
AND v.table_name = vt.view_name
AND v.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY
v.table_name;dvdrentalのビュー一覧の例
dvdrental=# SELECT
dvdrental-# v.*
dvdrental-# FROM
dvdrental-# information_schema.views v,
dvdrental-# information_schema.view_table_usage AS vt
dvdrental-# WHERE
dvdrental-# v.table_catalog = 'dvdrental'
dvdrental-# AND v.table_catalog = vt.view_catalog
dvdrental-# AND v.table_schema = vt.view_schema
dvdrental-# AND v.table_name = vt.view_name
dvdrental-# AND v.table_schema NOT IN ('pg_catalog', 'information_schema')
dvdrental-# ORDER BY
dvdrental-# v.table_name
dvdrental-# LIMIT
dvdrental-# 1;
table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
---------------+--------------+------------+---------------------------------------------------------------------------------------------------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
dvdrental | public | actor_info | SELECT a.actor_id, +| NONE | NO | NO | NO | NO | NO
| | | a.first_name, +| | | | | |
| | | a.last_name, +| | | | | |
| | | group_concat(DISTINCT (((c.name)::text || ': '::text) || ( SELECT group_concat((f.title)::text) AS group_concat+| | | | | |
| | | FROM ((film f +| | | | | |
| | | JOIN film_category fc_1 ON ((f.film_id = fc_1.film_id))) +| | | | | |
| | | JOIN film_actor fa_1 ON ((f.film_id = fa_1.film_id))) +| | | | | |
| | | WHERE ((fc_1.category_id = c.category_id) AND (fa_1.actor_id = a.actor_id)) +| | | | | |
| | | GROUP BY fa_1.actor_id))) AS film_info +| | | | | |
| | | FROM (((actor a +| | | | | |
| | | LEFT JOIN film_actor fa ON ((a.actor_id = fa.actor_id))) +| | | | | |
| | | LEFT JOIN film_category fc ON ((fa.film_id = fc.film_id))) +| | | | | |
| | | LEFT JOIN category c ON ((fc.category_id = c.category_id))) +| | | | | |
| | | GROUP BY a.actor_id, a.first_name, a.last_name; | | | | | |
(1 行)11. index
SELECT
i.indexrelid,
i.schemaname,
i.tablename,
i.indexname,
CASE i.indoption [i.attnum - 1]
WHEN 0 THEN ARRAY ['ASC', 'NULLS LAST']
WHEN 1 THEN ARRAY ['DESC', 'NULLS FIRST']
WHEN 2 THEN ARRAY ['ASC', 'NULLS FIRST']
WHEN 3 THEN ARRAY ['DESC', 'NULLS ']
ELSE ARRAY ['UNKNOWN OPTION' || i.indoption[i.attnum - 1],
'' ]
END ::text [] AS options,
i.attnum,
pg_get_indexdef(i.indexrelid, i.attnum, TRUE) AS attdef,
i.indisclustered,
i.indisvalid,
i.indisunique
FROM
(
SELECT
idx.indexrelid,
ui.schemaname AS schemaname,
ui.relname AS tablename,
ui.indexrelname AS indexname,
idx.indkey,
idx.indoption,
idx.indclass,
idx.indisclustered,
idx.indisvalid,
idx.indisunique,
UNNEST (
ARRAY (SELECT generate_series(1, idx.indnatts) AS n)
) AS attnum
FROM
pg_index idx JOIN pg_stat_user_indexes ui
ON ui.indexrelid = idx.indexrelid
WHERE
indisprimary <> TRUE
) i
ORDER BY
i.schemaname,
i.tablename,
i.indexrelid,
i.attnum;indexrelid が同じ値のレコードが複数ある場合は複合インデックスを構成するカラムとなり、 attnum がインデックスを構成するカラムの順番となる。
例
dvdrental=# SELECT
dvdrental-# i.indexrelid,
dvdrental-# i.schemaname,
dvdrental-# i.tablename,
dvdrental-# i.indexname,
dvdrental-# CASE i.indoption [i.attnum - 1]
dvdrental-# WHEN 0 THEN ARRAY ['ASC', 'NULLS LAST']
dvdrental-# WHEN 1 THEN ARRAY ['DESC', 'NULLS FIRST']
dvdrental-# WHEN 2 THEN ARRAY ['ASC', 'NULLS FIRST']
dvdrental-# WHEN 3 THEN ARRAY ['DESC', 'NULLS ']
dvdrental-# ELSE ARRAY ['UNKNOWN OPTION' || i.indoption[i.attnum - 1],
dvdrental-# '' ]
dvdrental-# END ::text [] AS options,
dvdrental-# i.attnum,
dvdrental-# pg_get_indexdef(i.indexrelid, i.attnum, TRUE) AS attdef,
dvdrental-# i.indisclustered,
dvdrental-# i.indisvalid,
dvdrental-# i.indisunique
dvdrental-# FROM
dvdrental-# (
dvdrental(# SELECT
dvdrental(# idx.indexrelid,
dvdrental(# ui.schemaname AS schemaname,
dvdrental(# ui.relname AS tablename,
dvdrental(# ui.indexrelname AS indexname,
dvdrental(# idx.indkey,
dvdrental(# idx.indoption,
dvdrental(# idx.indclass,
dvdrental(# idx.indisclustered,
dvdrental(# idx.indisvalid,
dvdrental(# idx.indisunique,
dvdrental(# UNNEST (
dvdrental(# ARRAY (SELECT generate_series(1, idx.indnatts) AS n)
dvdrental(# ) AS attnum
dvdrental(# FROM
dvdrental(# pg_index idx JOIN pg_stat_user_indexes ui
dvdrental(# ON ui.indexrelid = idx.indexrelid
dvdrental(# WHERE
dvdrental(# indisprimary <> TRUE
dvdrental(# ) i
dvdrental-# ORDER BY
dvdrental-# i.schemaname,
dvdrental-# i.tablename,
dvdrental-# i.indexrelid,
dvdrental-# i.attnum;
indexrelid | schemaname | tablename | indexname | options | attnum | attdef | indisclustered | indisvalid | indisunique
------------+------------+------------+-----------------------------------------------------+--------------------+--------+------------------+----------------+------------+-------------
7638986 | public | actor | idx_actor_last_name | {ASC,"NULLS LAST"} | 1 | last_name | f | t | f
7638988 | public | address | idx_fk_city_id | {ASC,"NULLS LAST"} | 1 | city_id | f | t | f
7638989 | public | city | idx_fk_country_id | {ASC,"NULLS LAST"} | 1 | country_id | f | t | f
7638987 | public | customer | idx_fk_address_id | {ASC,"NULLS LAST"} | 1 | address_id | f | t | f
7638996 | public | customer | idx_fk_store_id | {ASC,"NULLS LAST"} | 1 | store_id | f | t | f
7638997 | public | customer | idx_last_name | {ASC,"NULLS LAST"} | 1 | last_name | f | t | f
7638985 | public | film | film_fulltext_idx | {ASC,"NULLS LAST"} | 1 | fulltext | f | t | f
7638993 | public | film | idx_fk_language_id | {ASC,"NULLS LAST"} | 1 | language_id | f | t | f
7638999 | public | film | idx_title | {ASC,"NULLS LAST"} | 1 | title | f | t | f
7638991 | public | film_actor | idx_fk_film_id | {ASC,"NULLS LAST"} | 1 | film_id | f | t | f
7638998 | public | inventory | idx_store_id_film_id | {ASC,"NULLS LAST"} | 1 | store_id | f | t | f
7638998 | public | inventory | idx_store_id_film_id | {ASC,"NULLS LAST"} | 2 | film_id | f | t | f
7638990 | public | payment | idx_fk_customer_id | {ASC,"NULLS LAST"} | 1 | customer_id | f | t | f
7638994 | public | payment | idx_fk_rental_id | {ASC,"NULLS LAST"} | 1 | rental_id | f | t | f
7638995 | public | payment | idx_fk_staff_id | {ASC,"NULLS LAST"} | 1 | staff_id | f | t | f
7638992 | public | rental | idx_fk_inventory_id | {ASC,"NULLS LAST"} | 1 | inventory_id | f | t | f
7639001 | public | rental | idx_unq_rental_rental_date_inventory_id_customer_id | {ASC,"NULLS LAST"} | 1 | rental_date | f | t | t
7639001 | public | rental | idx_unq_rental_rental_date_inventory_id_customer_id | {ASC,"NULLS LAST"} | 2 | inventory_id | f | t | t
7639001 | public | rental | idx_unq_rental_rental_date_inventory_id_customer_id | {ASC,"NULLS LAST"} | 3 | customer_id | f | t | t
7639000 | public | store | idx_unq_manager_staff_id | {ASC,"NULLS LAST"} | 1 | manager_staff_id | f | t | t
(20 行)index の情報と index に設定されているカラムを別々に取得する。
SELECT
ui.schemaname AS schemaname,
ui.relname AS tablename,
ui.indexrelname AS indexname,
idx.indexrelid, (1)
idx.indkey,
idx.indoption,
idx.indclass,
idx.indnatts,
idx.indisclustered,
idx.indisvalid,
idx.indisunique,
idx.indisprimary
FROM
pg_index idx JOIN pg_stat_user_indexes ui
ON ui.indexrelid = idx.indexrelid
WHERE
indisprimary <> TRUE
ORDER BY
schemaname,
tablename,
indexname;
SELECT
i.indexrelid,
CASE i.indoption [i.attnum - 1]
WHEN 0 THEN ARRAY ['ASC', 'NULLS LAST']
WHEN 1 THEN ARRAY ['DESC', 'NULLS FIRST']
WHEN 2 THEN ARRAY ['ASC', 'NULLS FIRST']
WHEN 3 THEN ARRAY ['DESC', 'NULLS ']
ELSE ARRAY ['UNKNOWN OPTION' || i.indoption[i.attnum - 1],
'' ]
END ::text [] AS options,
i.attnum,
pg_get_indexdef(i.indexrelid, i.attnum, TRUE) AS attdef
FROM
(
SELECT
idx.indexrelid,
idx.indoption,
idx.indclass,
UNNEST (
ARRAY (SELECT generate_series(1, idx.indnatts) AS n)
) AS attnum
FROM
pg_index idx
WHERE
idx.indexrelid = <❶の値>::OID (2)
) i
ORDER BY
i.attnum;| 1 | : ❷に使用する |
| 2 | : ❶の値を使用する |
インデックスidx_unq_rental_rental_date_inventory_id_customer_idに設定されているカラムの例
dvdrental=# SELECT
dvdrental-# ui.schemaname AS schemaname,
dvdrental-# ui.relname AS tablename,
dvdrental-# ui.indexrelname AS indexname,
dvdrental-# idx.indexrelid,
dvdrental-# idx.indkey,
dvdrental-# idx.indoption,
dvdrental-# idx.indclass,
dvdrental-# idx.indnatts,
dvdrental-# idx.indisclustered,
dvdrental-# idx.indisvalid,
dvdrental-# idx.indisunique,
dvdrental-# idx.indisprimary
dvdrental-# FROM
dvdrental-# pg_index idx JOIN pg_stat_user_indexes ui
dvdrental-# ON ui.indexrelid = idx.indexrelid
dvdrental-# WHERE
dvdrental-# indisprimary <> TRUE
dvdrental-# ORDER BY
dvdrental-# schemaname,
dvdrental-# tablename,
dvdrental-# indexname;
schemaname | tablename | indexname | indexrelid | indkey | indoption | indclass | indnatts | indisclustered | indisvalid | indisunique | indisprimary
------------+------------+-----------------------------------------------------+------------+--------+-----------+----------------+----------+----------------+------------+-------------+--------------
public | actor | idx_actor_last_name | 7638986 | 3 | 0 | 3126 | 1 | f | t | f | f
public | address | idx_fk_city_id | 7638988 | 5 | 0 | 1979 | 1 | f | t | f | f
public | city | idx_fk_country_id | 7638989 | 3 | 0 | 1979 | 1 | f | t | f | f
public | customer | idx_fk_address_id | 7638987 | 6 | 0 | 1979 | 1 | f | t | f | f
public | customer | idx_fk_store_id | 7638996 | 2 | 0 | 1979 | 1 | f | t | f | f
public | customer | idx_last_name | 7638997 | 4 | 0 | 3126 | 1 | f | t | f | f
public | film | film_fulltext_idx | 7638985 | 13 | 0 | 10110 | 1 | f | t | f | f
public | film | idx_fk_language_id | 7638993 | 5 | 0 | 1979 | 1 | f | t | f | f
public | film | idx_title | 7638999 | 2 | 0 | 3126 | 1 | f | t | f | f
public | film_actor | idx_fk_film_id | 7638991 | 2 | 0 | 1979 | 1 | f | t | f | f
public | inventory | idx_store_id_film_id | 7638998 | 3 2 | 0 0 | 1979 1979 | 2 | f | t | f | f
public | payment | idx_fk_customer_id | 7638990 | 2 | 0 | 1979 | 1 | f | t | f | f
public | payment | idx_fk_rental_id | 7638994 | 4 | 0 | 1978 | 1 | f | t | f | f
public | payment | idx_fk_staff_id | 7638995 | 3 | 0 | 1979 | 1 | f | t | f | f
public | rental | idx_fk_inventory_id | 7638992 | 3 | 0 | 1978 | 1 | f | t | f | f
public | rental | idx_unq_rental_rental_date_inventory_id_customer_id | 7639001 | 2 3 4 | 0 0 0 | 3128 1978 1979 | 3 | f | t | t | f (1)
public | store | idx_unq_manager_staff_id | 7639000 | 2 | 0 | 1979 | 1 | f | t | t | f
(17 行)
dvdrental=# SELECT
dvdrental-# i.indexrelid,
dvdrental-# CASE i.indoption [i.attnum - 1]
dvdrental-# WHEN 0 THEN ARRAY ['ASC', 'NULLS LAST']
dvdrental-# WHEN 1 THEN ARRAY ['DESC', 'NULLS FIRST']
dvdrental-# WHEN 2 THEN ARRAY ['ASC', 'NULLS FIRST']
dvdrental-# WHEN 3 THEN ARRAY ['DESC', 'NULLS ']
dvdrental-# ELSE ARRAY ['UNKNOWN OPTION' || i.indoption[i.attnum - 1],
dvdrental-# '' ]
dvdrental-# END ::text [] AS options,
dvdrental-# i.attnum,
dvdrental-# pg_get_indexdef(i.indexrelid, i.attnum, TRUE) AS attdef
dvdrental-# FROM
dvdrental-# (
dvdrental(# SELECT
dvdrental(# idx.indexrelid,
dvdrental(# idx.indoption,
dvdrental(# idx.indclass,
dvdrental(# UNNEST (
dvdrental(# ARRAY (SELECT generate_series(1, idx.indnatts) AS n)
dvdrental(# ) AS attnum
dvdrental(# FROM
dvdrental(# pg_index idx
dvdrental(# WHERE
dvdrental(# idx.indexrelid = 7639001::OID (2)
dvdrental(# ) i
dvdrental-# ORDER BY
dvdrental-# i.attnum;
indexrelid | options | attnum | attdef
------------+--------------------+--------+--------------
7639001 | {ASC,"NULLS LAST"} | 1 | rental_date
7639001 | {ASC,"NULLS LAST"} | 2 | inventory_id
7639001 | {ASC,"NULLS LAST"} | 3 | customer_id
(3 行)| 1 | : indexrelid の値を❷に使用する |
| 2 | : ❶のレコードの indexrelid 値を使用する |
12. 外部参照
テーブルの外部参照を取得する。
SELECT
tc.constraint_name AS constraint_name,
tc.table_name,
kcu.column_name AS column_name,
ccu.table_name AS ref_table_name,
ccu.column_name AS ref_column_name,
rc.match_option,
rc.update_rule,
rc.delete_rule
FROM
information_schema.table_constraints tc JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name JOIN information_schema.referential_constraints rc
ON tc.constraint_name = rc.constraint_name
WHERE
tc.constraint_type = 'FOREIGN KEY'
AND tc.table_catalog = '<database name>'
AND tc.table_name = '<table name>'
ORDER BY
constraint_name;dvdrental.rentalテーブルの外部参照の例
dvdrental=# SELECT
dvdrental-# tc.constraint_name AS constraint_name,
dvdrental-# tc.table_name,
dvdrental-# kcu.column_name AS column_name,
dvdrental-# ccu.table_name AS ref_table_name,
dvdrental-# ccu.column_name AS ref_column_name,
dvdrental-# rc.match_option,
dvdrental-# rc.update_rule,
dvdrental-# rc.delete_rule
dvdrental-# FROM
dvdrental-# information_schema.table_constraints tc JOIN information_schema.constraint_column_usage ccu
dvdrental-# ON tc.constraint_name = ccu.constraint_name JOIN information_schema.key_column_usage kcu
dvdrental-# ON tc.constraint_name = kcu.constraint_name JOIN information_schema.referential_constraints rc
dvdrental-# ON tc.constraint_name = rc.constraint_name
dvdrental-# WHERE
dvdrental-# tc.constraint_type = 'FOREIGN KEY'
dvdrental-# AND tc.table_catalog = 'dvdrental'
dvdrental-# AND tc.table_name = 'rental'
dvdrental-# ORDER BY
dvdrental-# constraint_name;
constraint_name | table_name | column_name | ref_table_name | ref_column_name | match_option | update_rule | delete_rule
--------------------------+------------+--------------+----------------+-----------------+--------------+-------------+-------------
rental_customer_id_fkey | rental | customer_id | customer | customer_id | NONE | CASCADE | RESTRICT
rental_inventory_id_fkey | rental | inventory_id | inventory | inventory_id | NONE | CASCADE | RESTRICT
rental_staff_id_key | rental | staff_id | staff | staff_id | NONE | NO ACTION | NO ACTION
(3 行)13. ファンクション
SELECT
pr.proname AS name,
pg_get_function_result(pr.oid) AS prorettypename,
typ.typname,
lang.lanname,
pg_get_functiondef(pr.oid) AS func_def,
COALESCE(
pg_catalog.pg_get_function_identity_arguments(pr.oid),
''
) AS func_with_identity_arguments,
nsp.nspname AS namespace
FROM
pg_proc pr JOIN pg_type typ
ON typ.oid = prorettype JOIN pg_language lang
ON lang.oid = prolang JOIN pg_namespace nsp
ON nsp.oid = pr.pronamespace
WHERE
proisagg = FALSE
AND typ.typname NOT IN ('trigger', 'event_trigger')
AND lanname NOT IN ('internal')
AND nsp.nspname NOT IN ('information_schema', 'pg_catalog')
ORDER BY
proname;ファンクションの例
dvdrental=# SELECT
dvdrental-# pr.proname AS name,
dvdrental-# pg_get_function_result(pr.oid) AS prorettypename,
dvdrental-# typ.typname,
dvdrental-# lang.lanname,
dvdrental-# pg_get_functiondef(pr.oid) AS func_def,
dvdrental-# COALESCE(
dvdrental(# pg_catalog.pg_get_function_identity_arguments(pr.oid),
dvdrental(# ''
dvdrental(# ) AS func_with_identity_arguments,
dvdrental-# nsp.nspname AS namespace
dvdrental-# FROM
dvdrental-# pg_proc pr JOIN pg_type typ
dvdrental-# ON typ.oid = prorettype JOIN pg_language lang
dvdrental-# ON lang.oid = prolang JOIN pg_namespace nsp
dvdrental-# ON nsp.oid = pr.pronamespace
dvdrental-# WHERE
dvdrental-# proisagg = FALSE
dvdrental-# AND typ.typname NOT IN ('trigger', 'event_trigger')
dvdrental-# AND lanname NOT IN ('internal')
dvdrental-# AND nsp.nspname NOT IN ('information_schema', 'pg_catalog')
dvdrental-# ORDER BY
dvdrental-# proname
dvdrental-# LIMIT
dvdrental-# 2;
name | prorettypename | typname | lanname | func_def | func_with_identity_arguments | namespace
---------------+----------------+---------+---------+------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+-----------
_group_concat | text | text | sql | CREATE OR REPLACE FUNCTION public._group_concat(text, text) +| text, text | public
| | | | RETURNS text +| |
| | | | LANGUAGE sql +| |
| | | | IMMUTABLE +| |
| | | | AS $function$ +| |
| | | | SELECT CASE +| |
| | | | WHEN $2 IS NULL THEN $1 +| |
| | | | WHEN $1 IS NULL THEN $2 +| |
| | | | ELSE $1 || ', ' || $2 +| |
| | | | END +| |
| | | | $function$ +| |
| | | | | |
film_in_stock | SETOF integer | int4 | sql | CREATE OR REPLACE FUNCTION public.film_in_stock(p_film_id integer, p_store_id integer, OUT p_film_count integer)+| p_film_id integer, p_store_id integer, OUT p_film_count integer | public
| | | | RETURNS SETOF integer +| |
| | | | LANGUAGE sql +| |
| | | | AS $function$ +| |
| | | | SELECT inventory_id +| |
| | | | FROM inventory +| |
| | | | WHERE film_id = $1 +| |
| | | | AND store_id = $2 +| |
| | | | AND inventory_in_stock(inventory_id); +| |
| | | | $function$ +| |
| | | | | |
(2 行)14. トリガーファンクション
SELECT
pr.proname AS name,
pg_get_function_result(pr.oid) AS prorettypename,
typ.typname,
lang.lanname,
pg_get_functiondef(pr.oid) AS func_def,
COALESCE(
pg_catalog.pg_get_function_identity_arguments(pr.oid),
''
) AS func_with_identity_arguments,
nsp.nspname AS namespace
FROM
pg_proc pr JOIN pg_type typ
ON typ.oid = prorettype JOIN pg_language lang
ON lang.oid = prolang JOIN pg_namespace nsp
ON nsp.oid = pr.pronamespace
WHERE
proisagg = FALSE
AND typ.typname IN ('trigger', 'event_trigger')
AND lanname NOT IN ('internal')
AND nsp.nspname NOT IN ('information_schema', 'pg_catalog')
ORDER BY
proname;トリガーファンクションの例
dvdrental=# SELECT
dvdrental-# pr.proname AS name,
dvdrental-# pg_get_function_result(pr.oid) AS prorettypename,
dvdrental-# typ.typname,
dvdrental-# lang.lanname,
dvdrental-# pg_get_functiondef(pr.oid) AS func_def,
dvdrental-# COALESCE(
dvdrental(# pg_catalog.pg_get_function_identity_arguments(pr.oid),
dvdrental(# ''
dvdrental(# ) AS func_with_identity_arguments,
dvdrental-# nsp.nspname AS namespace
dvdrental-# FROM
dvdrental-# pg_proc pr JOIN pg_type typ
dvdrental-# ON typ.oid = prorettype JOIN pg_language lang
dvdrental-# ON lang.oid = prolang JOIN pg_namespace nsp
dvdrental-# ON nsp.oid = pr.pronamespace
dvdrental-# WHERE
dvdrental-# proisagg = FALSE
dvdrental-# AND typ.typname IN ('trigger', 'event_trigger')
dvdrental-# AND lanname NOT IN ('internal')
dvdrental-# AND nsp.nspname NOT IN ('information_schema', 'pg_catalog')
dvdrental-# ORDER BY
dvdrental-# proname;
name | prorettypename | typname | lanname | func_def | func_with_identity_arguments | namespace
--------------+----------------+---------+---------+--------------------------------------------------+------------------------------+-----------
last_updated | trigger | trigger | plpgsql | CREATE OR REPLACE FUNCTION public.last_updated()+| | public
| | | | RETURNS trigger +| |
| | | | LANGUAGE plpgsql +| |
| | | | AS $function$ +| |
| | | | BEGIN +| |
| | | | NEW.last_update = CURRENT_TIMESTAMP; +| |
| | | | RETURN NEW; +| |
| | | | END $function$ +| |
| | | | | |
(1 行)15. 列挙型
SELECT
t.oid, (1)
t.typlen,
t.typdelim,
nsp.nspname
FROM
pg_type t JOIN pg_namespace nsp
ON nsp.oid = t.typnamespace
WHERE
nsp.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND t.typtype IN ('e')
SELECT
enumlabel
FROM
pg_enum
WHERE
enumtypid = <❶の値>::oid (2)
ORDER BY
enumsortorder| 1 | : ❷に使用する値 |
| 2 | : ❶の値 |
列挙型の例
dvdrental=# SELECT
dvdrental-# t.oid, (1)
dvdrental-# t.typlen,
dvdrental-# t.typdelim,
dvdrental-# nsp.nspname
dvdrental-# FROM
dvdrental-# pg_type t JOIN pg_namespace nsp
dvdrental-# ON nsp.oid = t.typnamespace
dvdrental-# WHERE
dvdrental-# nsp.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
dvdrental-# AND t.typtype IN ('e');
oid | typlen | typdelim | nspname
---------+--------+----------+---------
7638786 | 4 | , | public
(1 行)
dvdrental=# SELECT
dvdrental-# enumlabel
dvdrental-# FROM
dvdrental-# pg_enum
dvdrental-# WHERE
dvdrental-# enumtypid = 7638786::oid (2)
dvdrental-# ORDER BY
dvdrental-# enumsortorder;
enumlabel
-----------
G
PG
PG-13
R
NC-17
(5 行)| 1 | : ❷に使用する値 |
| 2 | : ❶の値 |