最近会社でちょくちょく調べることになるので、まとめてみました。

PostgreSQLで"テーブル一覧を知りたい"というようなことがちょくちょくあります。これをネットで検索すると、psqlのメタコマンドを使用するケースが多く見つかります。"psqlで\dtする"といったようなものです。

psqlのメタコマンドといったようなクライアント依存の方法ではなく、"select * from pg_tables"などのようなクライアント非依存の方法を常々知りたいと思っています。都度調べるのは面倒なので、調べたことがあるものをまとめてみました。

PostgresよりもOracleのほうが利用歴が長いです。そのため、"Oracleの機能と同等のことをPostgresでどう表すか"という形で調べたものが多くなっています。

調べる必要が出てきたら、調べた結果を追加していきます。

とくにバージョンが書かれていないものは"Oracle 11g XE (11.2.0.2.0)"と"PostgreSQL 12.2"を用いて確認しています。

なお、公式サイトが提供する各種ドキュメントへのリンクには、別のバージョンのものを使用している場合があります。Oracleは11gのドキュメントを見つけにくくなっていること、Postgresは各バージョン系列の最新ドキュメントしかWeb公開されていないようだということが理由です。

DB管理

バージョン

Oracle : SELECT * FROM V$VERSION
Postgres : select version()

Oracleはバージョンによって出力内容が異なります。11g(XE)では"BANNER"のみ、複数レコードで各コンポーネントのバージョン情報が表示されます。18c(XE)では単一レコード複数カラムが返されます。

データベース名

Oracle : SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL
Postgres : select catalog_name from information_schema.information_schema_catalog_name

"catalog_name"が、"SID/SERVICE_NAME/DB_NAME"などのうち、どれと同等のものと考えるのが正しいか、自信はありません。

カレントユーザー

Oracle : SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM USER_TABLES
Oracle : SELECT * FROM USER_USERS
Postgres : select current_user

設定パラメータ

Oracle : SELECT * FROM V$PARAMETER
Oracle : SELECT * FROM V$PARAMETER2
Oracle : SELECT * FROM V$NLS_PARAMETERS
Postgres : select * from pg_settings

データ型

一部のデータ型のみを抜粋しています。

Oracle	Postgres
NUMBER	numeric
VARCHAR2	character varying

オブジェクト定義

以下、Oralceには"ALL_"と対になる"USER_"があります。

テーブル

Oracle : SELECT * FROM ALL_TABLES
Postgres : select * from pg_tables
Postgres : select * from information_schema.tables

カラム

Oracle : SELECT * FROM ALL_TAB_COLUMNS
Postgres : select * from pg_attribute
Postgres : select * from information_schema.columns

テーブルコメント

Oracle

SELECT * FROM ALL_TAB_COMMENTS

Postgres

select	psut.relname
	,	pd.description
from	pg_stat_user_tables psut
left outer join pg_description pd
	on	pd.objoid = psut.relid
	and	pd.objsubid = 0

シーケンス

Oracle : SELECT * FROM ALL_SEQUENCES
Postgres : select * from シーケンス名
Postgres : select * from information_schema.sequences (v8.2以降)

DDL

ユーザー作成

Oracle : CREATE USER TEST IDENTIFIED BY TEST
Postgres : create user TEST with password 'TEST'

接続許可

Oracle : GRANT CONNECT TO TEST
Postgres : alter role TEST LOGIN

但し、Postgresの"create user"には"login"ロールが既に含まれています。"create role TEST"で作成された"TEST"に対して"alter role TEST LOGIN"するというのなら意味があります。

カラムの追加

Oracle : ALTER TABLE TBL_TEST ADD ( COL001 NUMBER )
Postgres : alter table tbl_test add col001 numeric

Oracleは、Postgres同様の書式"()なし"でもOKです。Oracleは"()"を指定して複数のカラムをカンマ区切りで複数追加することができます。MODIFYも同様です。

注：2022/04/18 Postgres用SQLからmodifyを除去しました。

カラムの変更

Oracle : ALTER TABLE TBL_TEST MODIFY ( COL001 NUMBER )
Postgres : alter table tbl_test alter column col001 type numeric

その他

日時計算

6時間前

Oracle : SELECT SYSDATE - ( 6 / 24 ) FROM DUAL
Postgres : select current_timestamp - interval '6 hours'

今日の00:00

Oracle : SELECT TRUNC ( SYSDATE ) FROM DUAL
Oracle : SELECT TRUNC ( SYSDATE, 'DAY' ) FROM DUAL
Postgres : select date_trunc ( 'day', current_timestamp )

Oracleは、切捨て単位を省略することで、"DAY"指定相当の切捨てを行います。

型変換

Oracle : SELECT TO_NUMBER ( '123' ) + 1 FROM DUAL
Oracle : SELECT CAST ( '123' AS NUMBER ) + 1 FROM DUAL
Postgres : select cast ( '123' as numeric ) + 1
Postgres : select '123'::numeric + 1
Postgres : select ( numeric '123' ) + 1

Oralceの"TO_CHAR"には書式化の機能もあります。JavaでString.formatを型変換と呼んでよいか、という感覚に近いと思います。

Postgresの"::"は、"123::character varying"のように型名の途中に空白が混ざっているものも評価できるようです。

コメント

テーブルコメントの節を追加して

テーブルコメントの節を追加したときに感じたことです。

Oracleのデータディクショナリ・ビューは、オブジェクト名、テーブル名、カラム名などがキーとなっています。そのため、今回のようにテーブルコメントを取得する際、テーブルコメントを含んでいるビューをSELECTすれば事足ります。また、複数のビューにまたがった情報を取得する場合でも、テーブル名やカラム名などを用いて結合すればよいです。そのため、分かりやすいです。

他方、Postgresのシステムカタログは、内部IDがキーとなっているものが多くあります。例えば、テーブルコメントを含むビュー"pg_description"には、テーブル名が含まれていません。内部IDを用いてテーブル名を含むビューと結合する必要があります。しかも、それぞれの内部IDの名称がビューによって異なるものもあります。上の"テーブルコメント"に書いた例だと、"pg_stat_user_tables.relid"と"pg_description.objoid"という異なるカラム名のIDを結合しています。そのため、覚えづらいです。

この記事は対訳表なので、これ以上は掘り下げません。しかし、それぞれのデータベースの設計思想と合わせて、ナチュラルキー、サロゲートキー、複合主キーのようなところを調べてみると、面白いかもしれません。