普段のOracle使いが、PostgreSQLを触って便利と感じた事一覧です。それぞれ具体例については追々書いていきます。
正規表現が充実
Oracleでは肯定先読み、肯定戻り読みがサポートされていないらしく「囲まれた中身だけの抽出」というのが（一気には）できませんが、PostgreSQLではきちんとサポートされています。また、正規表現にマッチしたテキストを配列で返してくれるregexp_match()はとても強力です。ストアドプログラムの行数を一気にに減らしてくれます。
また、以下は「都道府県始まりの住所」を抽出する条件を正規表現で書いていますが、Oracleに比べPostgreSQLの方が見た目として分かりやすいですよね。（PostgreSQLでは『~』が正規表現評価演算子となります。
正規表現による条件抽出方法の比較
-- 以下のようなデータがある時「都道府県」から始まる住所のみを
-- 抽出したい場合を例題とします
myposdb=# select * from addrestest order by 1;
dtno | address
------+--------------------------------------
1 | 北海道上川郡清水町本通八丁目７
2 | 上川郡清水町本通八丁目７
3 | 出身は北海道上川郡清水町本通八丁目７
(3 rows)
-- PostgreSQL版
select dtno,address from addrestest
where address ~ '^...??[都道府県]'
order by dtno;
dtno | address
------+--------------------------------
1 | 北海道上川郡清水町本通八丁目７
(1 row)
-- Oracle版
select dtno,address from addresstest
where REGEXP_LIKE(address, '^...??[都道府県]', 'im')
order by dtno;正規表現サンプル
concat()関数で一度に複数の文字列連結ができる
Oracleでの文字列連結関数としてconcat()があります。一度に２つの文字列しか連結できませんが、PostgreSQL版concat()は一度に複数文字列の連結ができて便利です。また連結をする際区切り文字の指定ができるconcat_ws()関数も別途用意されていてこちらもかなり重宝します。
テーブルやインデックス作成時に IF NOT EXISTSが使える
これは以前からOracleにも実装してほしい機能だと思っていました。
-- 新規に作成
myposdb=# create table if not exists ex400 (employee_number int, name varchar(40));
CREATE TABLE
-- 既存の場合でもエラーにならない
myposdb=# create table if not exists ex400 (employee_number int, name varchar(40));
NOTICE: relation "ex400" already exists, skipping
CREATE TABLE
-- 当然 if not existsを付けないとエラーになる
myposdb=# create table ex400 (employee_number int, name varchar(40));
ERROR: relation "ex400" already exists
-- create indexでも同様
myposdb=# create index if not exists idx01_ex400 on ex400(employee_number);
CREATE INDEX
myposdb=# create index if not exists idx01_ex400 on ex400(employee_number);
NOTICE: relation "idx01_ex400" already exists, skipping
CREATE INDEX
myposdb=#
-- カラム追加時（alter table add column)にも使える！
myposdb=# alter table ex400 add column if not exists job_title varchar(30);
ALTER TABLE
myposdb=# alter table ex400 add column if not exists job_title varchar(30);
NOTICE: column "job_title" of relation "ex400" already exists, skipping
ALTER TABLE
myposdb=#テーブルファンクションが簡単に実現できる
Oracleのテーブルファンクションと比較して随分手数少なく作成できるのでお手軽です。
VALUEリストがありがたい
簡単に配列項目が作成できかつ簡単に読み書きできます
psqlにてselect表示結果を縦にする \x
sqlplusと比べpsql自体圧が倒的に使いやすい（sqlplusが使い辛いのだと思いますがｗ）とは感じていましたが、このメタコマンド『 \x』は便利です。
-- 見ての通り横長になるものを表示すると非常に見辛いです
myposdb=# select * from pg_roles where rolname in ('sooni','udonman') order by oid;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+------------------------------------------+-------
sooni | t | t | f | f | t | f | -1 | ******** | | f | {"search_path=sooni, public, prjschema"} | 16384
udonman | f | t | f | f | t | f | -1 | ******** | | f | | 16397
(2 rows)
-- メタコマンド\xを入れる
myposdb=# \x
Expanded display is on.
myposdb=# select * from pg_roles where rolname in ('sooni','udonman') order by oid;
-[ RECORD 1 ]--+-----------------------------------------
rolname | sooni
rolsuper | t
rolinherit | t
rolcreaterole | f
rolcreatedb | f
rolcanlogin | t
rolreplication | f
rolconnlimit | -1
rolpassword | ********
rolvaliduntil |
rolbypassrls | f
rolconfig | {"search_path=sooni, public, prjschema"}
oid | 16384
-[ RECORD 2 ]--+-----------------------------------------
rolname | udonman
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | f
rolcanlogin | t
rolreplication | f
rolconnlimit | -1
rolpassword | ********
rolvaliduntil |
rolbypassrls | f
rolconfig |
oid | 16397
-- 再度 \x を打ち込むと元に戻ります
myposdb=# \x
Expanded display is off.
