最近会社でちょくちょく調べることになるので、まとめてみました。
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を結合しています。そのため、覚えづらいです。
この記事は対訳表なので、これ以上は掘り下げません。しかし、それぞれのデータベースの設計思想と合わせて、ナチュラルキー、サロゲートキー、複合主キーのようなところを調べてみると、面白いかもしれません。