【PostgreSQL】SQLでテーブル一覧を取得する

SQLでテーブル一覧を取得する

PostgreSQLでテーブル一覧を取得するにはinformation_schema.tablesテーブルを参照する。

SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = 'public'
ORDER BY table_name;

table_schemaの条件にはスキーマ名を指定する。
スキーマ名で絞らない場合pg_catalogスキーマのシステムテーブルも取得されるため指定しておくとよい。

information_schema.tablesテーブルのカラム

上記SQLではtable_nameのみを取得しているが、information_schema.tablesテーブルには以下のカラムが存在する。

table_catalog: テーブルが存在するデータベース名
table_schema: テーブルが存在するスキーマ名
table_name: テーブル名
table_type: テーブルの種類(通常はBASE TABLE、外部テーブルの場合はFOREIGN、一時テーブルの場合はLOCAL TEMPORARY)
self_referencing_column_name: PostgreSQLでは常にNULL
reference_generation: PostgreSQLでは常にNULL
user_defined_type_catalog: テーブルが型付きテーブルの場合は基になるデータ型を含むデータベースの名前
user_defined_type_schema: テーブルが型付きテーブルの場合は基になるデータ型を含むスキーマの名前
user_defined_type_name: テーブルが型付きテーブルの場合は基になるデータ型の名前
is_insertable_into: テーブルに挿入可能か
is_typed: テーブルが型付けされているか
commit_action: 常にNULL

カラム一覧も取得する

各テーブルのカラム一覧も取得する場合は以下のようにinformation_schema.columnsテーブルをJOINして取得する。
参考: 【PostgreSQL】SQLでカラム情報一覧を取得する

SELECT
  t.table_name,
  t.table_type,
  c.column_name,
  c.is_nullable,
  c.data_type
FROM
  (SELECT * FROM information_schema.tables WHERE table_schema = 'public') t
LEFT JOIN
  (SELECT * FROM information_schema.columns WHERE table_schema = 'public') c
ON
  t.table_name = c.table_name
ORDER BY
  t.table_name, c.ordinal_position;

 table_name | table_type | column_name | is_nullable | data_type
------------+------------+-------------+-------------+-----------
 users      | BASE TABLE | id          | NO          | integer
 users      | BASE TABLE | name        | YES         | text
 users      | BASE TABLE | email       | YES         | text
 users      | BASE TABLE | address     | YES         | text
 users_view | VIEW       | id          | YES         | integer
 users_view | VIEW       | name        | YES         | text
 users_view | VIEW       | email       | YES         | text
 users_view | VIEW       | address     | YES         | text
(8 rows)

上記ではテーブルに加えてビューも取得している。
テーブルだけに絞る場合は以下のようtable_typeをBASE TABLEに絞る。

SELECT
  t.table_name,
  c.column_name,
  c.is_nullable,
  c.data_type
FROM
  (SELECT * FROM information_schema.tables
   WHERE table_schema = 'public' AND table_type = 'BASE TABLE') t
LEFT JOIN
  (SELECT * FROM information_schema.columns WHERE table_schema = 'public') c
ON
  t.table_name = c.table_name
ORDER BY
  t.table_name, c.ordinal_position;

 table_name | column_name | is_nullable | data_type
------------+-------------+-------------+-----------
 users      | id          | NO          | integer
 users      | name        | YES         | text
 users      | email       | YES         | text
 users      | address     | YES         | text
(4 rows)

\dtコマンド

psqlコマンドの場合は\dtコマンドでテーブル一覧を取得できる。

dbname=# \dt
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | u

pg_tablesビュー

pg_tablesビューでもテーブル一覧を取得できる。

select * from pg_tables;
     schemaname     |        tablename        | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------+------------+------------+------------+----------+-------------+-------------
 public             | users                   | sue        |            | t          | f        | f           | f
 pg_catalog         | pg_statistic            | sue        |            | t          | f        | f           | f
・
・
・
 pg_catalog         | pg_largeobject          | sue        |            | t          | f        | f           | f
 information_schema | sql_parts               | sue        |            | f          | f        | f           | f
 information_schema | sql_implementation_info | sue        |            | f          | f        | f           | f
 information_schema | sql_features            | sue        |            | f          | f        | f           | f
 information_schema | sql_sizing              | sue        |            | f          | f        | f           | f

システムテーブルも表示されるので自分で作ったテーブルが属するscheme名だけに絞るとよい。

select * from pg_tables where schemaname = 'public';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | users     | sue        |            | t          | f        | f           | f

select * from pg_tables where schemaname <> 'pg_catalog' and schemaname <> 'information_schema';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | users     | sue        |            | t          | f        | f           | f

カラム名をjoinする場合は以下のとおり。

select
  t.tablename,
  c.column_name,
  c.is_nullable,
  c.data_type
from
  (select * from pg_tables where schemaname = 'public') t
left join
  (select * from information_schema.columns where table_schema = 'public') c
on
  t.tablename = c.table_name
order by
  t.tablename, c.ordinal_position;
 tablename | column_name | is_nullable | data_type
-----------+-------------+-------------+-----------
 users     | id          | NO          | integer
 users     | name        | YES         | text
 users     | email       | YES         | text
 users     | address     | YES         | text