PostgreSQLチート・SQL でデータベーススキーマを確認したい

• あるWebサービスの開発メモ・目次ページに戻る
• あるWebサービスの開発メモ・PostgreSQL チートに戻る

やりたいこと

PostgreSQL から SQL を使ってテーブル一覧、各テーブルのカラム一覧とプライマリキー情報を取得したいと思います

PostgreSQL でのデータベース操作は psql という PostgreSQL が提供してくれるターミナルからコマンドを投入して行います

Supabase の GUI からは SQL Editor による SQL の投入しかできないため、何とか SQL で完結させようとしています

テーブルの一覧を取得する

Supabase の SQL Editor から以下を投入すると

select schemaname, relname from pg_stat_user_tables order by schemaname, relname;

この SQL は、とくに変更すべき部分はなくて、PostgreSQL であればそのまま投げられます

以下が返却されます

schemaname	relname
auth	audit_log_entries
auth	identities
auth	instances
auth	refresh_tokens
auth	schema_migrations
auth	users
graphql	_field
graphql	_type
graphql	entity
graphql	entity_column
graphql	entity_unique_columns
graphql	relationship
graphql	schema_version
public	cats
public	comments
public	posts
public	profiles
public	replies
public	votes
realtime	schema_migrations
realtime	subscription
storage	buckets
storage	migrations
storage	objects

public スキーマに、開発時に定義したテーブルが登録されていました

public スキーマ だけ確認する場合は where schemaname = 'public' を追加して投入します

select schemaname, relname
  from pg_stat_user_tables
 where schemaname = 'public'
 order by schemaname, relname ;

schemaname	relname
public	cats
public	comments
public	posts
public	profiles
public	replies
public	votes

カラムの一覧を取得する

cats テーブルのカラム一覧を取得してみます

select column_name, ordinal_position, data_type
  from information_schema.columns
 where table_schema = 'public' and table_name = 'cats'
 order by ordinal_position;

3 行目で table_schema と table_name を指定します

以下が返却されました

column_name	ordinal_position	data_type
id	1	uuid
created_at	2	timestamp with time zone
updated_at	3	timestamp with time zone
user_id	4	uuid
name	5	text
post_cnt	6	numeric
com_cnt	7	numeric
reply_cnt	8	numeric
yoyaku	9	boolean
del	10	boolean
sname	11	text
disporder	12	numeric

プライマリキーの定義

select
	ccu.column_name as COLUMN_NAME
from
	 information_schema.table_constraints tc
	,information_schema.constraint_column_usage ccu
where
  tc.constraint_schema='public'
  and
	tc.table_name='cats'
  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;

where 句の 1つ目で schema、2つ目で table_name を指定しています

以下が返却されました

column_name
id
disporder