あんちょこ：PostgreSQL

データベース操作

データベースの作成

createdb DBNAME --encoding=UTF8 --lc-collate=ja_JP.UTF-8 --lc-ctype=ja_JP.UTF-8 --template=template0

データベースの複製

既存データベースをテンプレートにして新しいデータベースが作成できます。

createdb -T DB_ORG DB_REP

データベースが稼働中なら、下記のコマンドで複製できます。

pg_dump -Ft DB_ORG | pg_restore -c -d DB_PEP

日付時刻関連

日付のフォーマット

表現	式	結果
桁揃え	to_char(now(),'YYYY-MM-DD HH24:MI:SS')	2019-04-21 09:31:22
前ゼロ抜き	to_char(now(),'YYYY-FMMM-FMDD FMHH24:MI:SS')	2019-4-6 9:32:02
ISO8601	to_char(now(),'YYYY-MM-DD"T"HH24:MI:SS"Z"')	2019-11-14T18:37:28Z

データベースのタイムゾーンを変更する

alter database DBNAME set timezone to 'Asia/Tokyo';
select pg_reload_conf();

日付から年度を取得する

４月開始の年度を取得する場合、３ヶ月引き算して年を取り出すことで得られます。

extract(year from dt + interval '-3 month');

インポート／エクスポート

CSVファイルからインポートする

ファイル名の指定は Windows 環境でもシングルで囲むだけでよい。 ヘッダ行があるデータの場合は with csv header を付け、無いデータだけの場合は with csv にします。 ヘッダ有りの場合でフィールド名を見るかどうかは確認していません。

copy table_name from 'c:\path\to\sample.csv' with csv header;

その他

あるソート順において対象データが何番目にあたるか調べる

例えば、sorted_table で update_time の降順で並び変えたとき、id=24 のデータが何番目に来ているかを調べるには下記のような SQL になります。

select *
from (select row_number() over (order by update_time desc) as rowno,id from source_table) as sorted_table
where id=24

評価が null の場合に別の値を返す（nvl 代替）

coalesce(column,0)

降順の並べ替えで NULL 値を最後にする

order by update_time desc nulls last

論理型をカウントする

sum(colum::integer)