DISTINCT句によって検索結果がユニークになることを利用し、結果を別テーブルにコピーして戻す。
※制約(プライマリキー制約、 非NULL制約など)やデフォルト値などの設定は、自動的にコピーされないので適宜再設定する。
1 2 3 4 5 6 7 8 | --重複レコード除去（一時テーブル） CREATE TABLE tmp_table AS SELECT DISTINCT * FROM target_table; --元テーブルバックアップ CREATE TABLE bk_table AS SELECT * FROM target_table; --元テーブル削除 DROP TABLE target_table; --一時テーブルを元テーブルの名称にリネーム ALTER TABLE tmp_table RENAME TO target_table; |
改行（LF）したい箇所にchr(10)を挿入する。
1 | INSERT INTO target_table (target_col) VALUES ('foo' || chr(10) || 'bar'); |
ASCIIコード参考。
| ASCIIコード | 内容 |
|---|---|
| chr(9) | タブ |
| chr(13) | CR |
| chr(10) | LF |
| chr(32) | スペース |
| chr(39) | 引用符(') |
また、文字列の前にE（小文字でもOK）を入力することでも改行コードを挿入できる。
1 | INSERT INTO target_table (target_col) VALUES (E'foo\nbar'); |
単純なCOPYコマンド
1 | COPY tablename TO '/tmp/output.csv' DELIMITER ',' |
列指定のCOPYコマンド
1 | COPY tablename(col1, col2, col3) TO '/tmp/output.csv' DELIMITER ',' |
SELECT結果のCOPYコマンド
1 | COPY (SELECT * FROM tablename) TO '/tmp/output.csv' DELIMITER ',' |
※オプション
FORMAT：読み取りまたは書き込みに使用するデータ書式を選択（text、csv（カンマ区切り値）、またはbinary、デフォルトはtext）
DELIMITER：フィールド区切り文字の指定（書式でのデフォルトはタブ文字、CSV書式ではカンマ）
出力結果を復元するにはCOPY…FROM
1 | COPY tablename FROM '/tmp/output.csv' |
SQLを記述してエクスポート
1 | psql -U {user} {dbname} -c 'SELECT * FROM tablename' -A -F, -t > output.csv |
※オプション
-A：位置揃えなしの出力モードに切り替える（デフォルトの出力モードは位置揃えあり）
-F：位置揃えを行わない出力におけるフィールド区切り文字の指定（後続の文字を区切り文字とする、デフォルトは ASCII の縦棒”|”）
-t：列名と結果の行数フッタなどの表示を無効にする
SQLファイルを指定してエクスポート
1 | psql -U {user} {dbname} -f export_csv.sql -A -F, > output.csv |
1 2 3 4 5 | SELECT '"' || coalesce(col1, '') || '"' , '"' || coalesce(cast(col2 as text), '') || '"' FROM tablename; |
メタコマンドを利用してエクスポート
1 | psql -U {user} {dbname} -f export_csv_meta.sql |
1 2 3 4 5 6 7 8 9 10 11 12 | \o output.csv --出力先を output.csv へ切り替え \pset format unaligned --現在有効なフィールド区切り文字で区切って出力 \pset fieldsep ',' --位置揃えなしの出力書式で使用されるフィールド区切り文字を指定 \pset tuples_only --出力列名ヘッダと行数フッタの表示を切り替え SELECT '"' || coalesce(col1, '') || '"' , '"' || coalesce(cast(col2 as text), '') || '"' FROM tablename; \o --出力先を元に戻す |
Postgresqlでは、OracleやInformix等でNULL置換を行うNVL関数が存在しない。
代わりにCOALESCE（コアレス）関数が用意されている。
使い方というよりは綴りを忘れてしまうのでメモ。
1 | SELECT COALESCE(target_col, " ") FROM target_table; |
なお、正確にはNVL関数は「該当する値がNULLの場合の代替値を返す」仕様に対して、CALESCEは「NULLでない自身の最初の引数を返す」仕様であり引数を列挙することができる。
1 | SELECT COALESCE(target_col1, target_col2, target_col3, " ") FROM target_table; |
上記の場合は、target_col1がNULLならtarget_col2、それもNULLならtarget_col3。それでもNULLなら” “、といった「IF-ELSE構文」のような挙動をする。
DBLINKの基本的な構文例。
1 2 3 4 5 6 | SELECT * FROM DBLINK('host=XXX port=5432 dbname=XXX user=XXX password=XXX' ,'SELECT col1, col2 FROM target') AS T1 (col1 text, col2 text) ORDER BY T1.col2 ; |
接続先を動的に変更する為、接続先情報をテーブルで管理する。
1 2 3 4 5 6 7 | CREATE TABLE linktest ( key character varying(1) NOT NULL, value character varying(200) NOT NULL, CONSTRAINT linktest_pk PRIMARY KEY (key) ) ; |
接続先情報を取得する為のPL/pgSQLを定義。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- Function: get_dblinkstr(character varying) -- DROP FUNCTION get_dblinkstr(character varying); CREATE OR REPLACE FUNCTION get_dblinkstr(argkey character varying) RETURNS character varying AS $BODY$ DECLARE -- <TITLE>DBLINK接続文字列取得</TITLE> -- <REMARKS> -- keyに一致するDBLINK接続文字列を取得する -- </REMARKS> fldvalue linktest.value%TYPE; BEGIN BEGIN SELECT value INTO fldvalue FROM linktest WHERE key = argkey; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; RETURN fldvalue; END; $BODY$ LANGUAGE plpgsql VOLATILE ; |
接続先情報取得関数の利用方法。
1 | SELECT * FROM get_dblinkstr('1'); |
接続先情報取得関数を利用したDBLINKを行うVIEWの定義。
1 2 3 4 5 6 | CREATE VIEW view_test AS SELECT * FROM DBLINK(get_dblinkstr('1') ,'SELECT col1, col2 FROM target') AS T1 (col1 text, col2 text) ; |
接続先情報取得関数を利用したDBLINKを行うVIEWの参照。
1 | SELECT * FROM view_test ORDER BY col1; |