あんちょこ: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
ISO8601to_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();
日付から年度を取得する

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

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)