タグ: postgresql

日付の差分を日数で取得する方法
first_date,last_dateがTimestamp型の場合、差分は1day 10:20:30 みたいに、日数と時間で表示される。

だけど、日数だけで取得したいときは、

SELECT first_date,last_date, cast(last_date as DATE) – cast(first_date as DATE) term FROM [table_name]

と、キャストして実行するそうだ。
勉強になった。

psqlでテーブルの内容をドカーンとファイルに落とす。

psql
DB名 #= COPY [テーブル名] TO [ファイル名] (FORMAT csv);

ファイル名のところは、
E’D¥:¥¥xxx.csv’

というように、エスケープ処理が必要になることを忘れない。

逆に、CSVファイルのデータをテーブルに保存する場合は、

psql
DB名 #= COPY [テーブル名] TO [ファイル名] with csv;

そもそもの発端は、私がPostgreSQLのpostgreユーザのパスワードを忘れてしまったことにある。

忘れてしまったので、コマンドプロンプトを起動させて再設定をしたその後、pgAdminでローカルのDBにアクセスできなくなってしまった。

あぁ～、またパスワード忘れたかなぁ・・・
あぁ～、再インストールかなぁ・・・

などと思ってコマンドプロンプトからpsqlを起動させてみた。
「つながらないよ」「ホントにDBあんの？」みたいな返答が返ってきたので、「あるよ？」と思ったが、「サービス開始されてないんじゃない？」って思って、Windowsの管理画面を見る。

案の定、サービス起動されてませんがな・・・

パスワードを変えちゃったせいで、起動できなくなっていたようで、さっそく新しいパスワードを設定してサービスを開始。
pgAdminで接続したら、ちゃんと見えるようになった。

なんか、自分で原因作って、自分で悩んで、自分で解決って・・・
一人で遊んでしまった。

以下のようなテーブルがあったとして
【t_sample】
id,element,rank,…
1,’A’,1,…
1,’B’,2,…
1,’C’,3,…
2,’D’,1,…

これを
1,’A,B,C’
2,’D’
ってな感じで取得する方法

SELECT id
 , ARRAY_TO_STRING(ARRAY(SELECT element
                         FROM t_sample a
                         WHERE a.id = b.id ORDER BY a.rank),',') elements
 FROM (SELECT id FROM t_sample GROUP BY id ORDER BY id) b

ARRAY_TO_STRING：取得した配列をARRAY_TO_STRINGでカンマ区切りにする。

SQL文が少々複雑で面倒だけど、使い勝手はよさげ。

[編集メモ] 2017/3/29 タイトルと見栄えを修正

OracleでいうところのNVLって、postgreにはないと思ってたけど、あった。

COALESCE関数:coalesce(paraA,paraB)

paraA is NULL なら、paraBに置きかえてくれる。

CREATE TABLE時にシーケンスと紐付けるのを忘れていた。
後から列にシーケンスを紐付けるやり方のメモ。

ALTER TABLE テーブル名
    ALTER COLUMN 列名
    SET DEFAULT nextval(‘シーケンス名’::regclass);

regclassってなんやのん？、と調べたが、
オブジェクト識別子(OID) ～内部システムテーブルに付けられる任意の主キー～の一つ、とのこと。
詳しくは、PostgreSQL 8.0.0 文書「オブジェクト識別子データ型」

ハァ？

ってカンジだが、ここでは指定したシーケンスの型に合わせますよ！って意味と捉えた。

こうして、SQL文実行前

CREATE TABLE テーブル名 (列名 integer NOT NULL,・・・・

が、SQL文実行後には、

CREATE TABLE テーブル名 (列名 serial NOT NULL,・・・・

に変わった。
実行後試しにINSERT文を実行して、シーケンスが有効になっていることが確認できた。