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