PostgreSQLメモ

rhaco2をPostgreSQL8.3で使えるように色々調べていくうちに学んだメモ。

likeでindexを使う

text_pattern_opsのインデックスを付ける（前方一致）

後方一致させる場合はreverseを使うとできる

insert時に「デフォルト値を入れる」だけの処理をしたい場合

insert into テーブル名 default value;

でOK

integerのカラムとかにlikeを適用したい場合

select * from テーブル名 where id::text like &#039;%abc%&#039;;

のようにカラムをテキストにキャストする。MySQLのvarcharをselectする時はデフォルトignore case。ついでにtrimも勝手にかける？らしい

show column

Mysqlで使えるshow columnは無いが、information_schemaの情報をselectすることで同じようなことが出来る。ただし、create table時にserialで指定してもinformation_schema.columnsから取得できるカラム情報はinteger(int4)のみ。なのでserialと判定するにはカラムがintegerであり、かつデフォルト値としてnextvalが指定されていることなど、複合的にチェックする必要がある。

cronでsqlを実行

毎日0時0分に実行する場合

0 0 * * * /path/to/psql -U username -c "sql" database > /dev/null

select結果でinsert, update

insertの場合はinsert文の後でselect文を書く。以下は一例

insert into table select a,b from table2 where a like 'hoge%';

別テーブルへの一部データのバックアップ代わりにも使える

insert into backup_table (select * from table where id=1);

updateは次のように行う

update table set a = t.a from (select * from table2) t where table.b = t.b;

どの範囲のデータが更新されるかに注意してwhere句を使う。上の場合、tの中でwhere句を使う場合とで異なる結果になる場合がある。

インデックス一覧を表示

インデックス一覧を表示。インデックスを2重に登録しようとするとエラーが出るので、チェックに使う

SELECT tablename, indexname FROM pg_indexes;

ビューの一覧を表示

SELECT viewname FROM pg_views ORDER BY viewname;

ビューの定義を調べる

select definition from pg_views WHERE viewname = 'ビュー名';

psqlで実行結果を見やすくする

psql => \x
psql => select * from table;

カラムの数が多いと表示が崩れやすいので、これで見やすくなる

カラム追加時にNOT NULLを指定する手順

ALTER TABLE table_name ADD COLUMN column_name text;
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT '';
UPDATE table_name SET column_name = '';
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

こんな感じで複数業使ってNOT NULL制約を入れる

テーブル（ビュー）のスキーマをファイルに書き込む

$ pg_dump -s -t table_name db_name > file_name

これだけ。反映したい時は

$ cat file_name | psql db_name

でおk

csvの取り込み

1,"hoge"

みたいなデータを/path/to/csvに保存して

copy tablename from '/path/to/csv' with csv;

とpsql で実行すると取り込みできる。

copy tablename (column1,column2) from '/path/to/csv' with csv;

とやれば、カラム名と順序の指定が可能

SQL結果をcsvに出力

$ psql db_name -c "SQL" -A -F, -t > filename

psql内では（タブ区切り出力とする）

$ psql
\o filename
\a
\f '\t'
SQL
\o
\q

IN句に複数条件を指定

select * from hoge where (a,b) in (select a,b from fuga where hage>1);
select * from hoge where (a,b) in (('fuga','hage'),('abc','def'));

みたいに指定すればおk

ユーザー定義関数の定義内容を表示

select procsrc from pg_proc where proname='関数名';

結構こんな感じでシステム用テーブルとかカラムとか色々あるので大変。

実行中のsql確認

select * from pg_stat_activity;

実行中sqlをストップ

select pg_cancel_backend(プロセスID);

データベースやテーブルのサイズ取得

データベース

select pg_database_size('dbname');

テーブル

select pg_relation_size('tablename');

select結果を配列にして、比較する

テーブル(event)に、日付(date)とイベントコード(code)カラムがあって、あるイベントコードのイベントと、全く同じ日程のイベントがあるかどうか調べる必要があったんだけど、配列使ったら上手く出来たのでメモ

select * from (select array_agg(date_trunc('day',date)) as d, code from (select * from event where order by date) b group by code) a where code != '...' and a.d = ARRAY(select date_trunc('day',date) from event where code ='...' order by date);

こんな感じで出来た。ポイントはgroup_byとarray_aggを使うとgroup化した時にそれぞれの行にあった数字をまとめて配列化できることと、配列は順序を同じにしておかないと比較出来ないので、予めorderをかけておくこと。select結果をそのまま配列にするにはARRAYでいけること。もうちょっとsqlをスッキリ出来そうだけど、とりあえずこんな感じ。

正規表現関連

置換

select regex_replace(columnname,'pattern','replace') from tablename;

検索

select * from tablename where columnname ~ 'pattern';

psqlでパスワードを入力しない方法

~HOME/.pgpassを作成する(Linuxの場合)

hostname:port:database:username:password

参考：パスワード入力なしでpsql » サイキョウライン

全データベースの物理ファイルサイズ

[Postgres]全データベースの物理ファイルサイズを取得 | Read it later.をそのまま使えば良い

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;

psqlでsqlにパラメータを渡す

vオプションを用いる

PostgreSQL: How to pass parameters from command line? - Stack Overflow

# a.sql
select :vint, :vstr;

$ psql -v vint=10 -v vstr="'hoge'" -f a.sql dbname
10  hoge

-cオプションで実行するsqlを指定する場合は無理っぽい

サブクエリで2列以上を返す

調べるのに結構時間かかった。配列を使うと上手く出来る

select a.id, sub_array[1] as hoge, sub_array[2] as fuga from (select a.id, (select array[avg(hoge),stddev(hoge)] from table1 where ...) as sub_array from table1) a where ...;

こんな感じで一旦複数列の値を配列に格納して、1列扱いとして、その配列を展開することでいける。ちなみに配列の添字は0スタートではなく、1スタートみたい。ある期間での集計値を各行に追加したいとかそんな場合にとても使える。

参考：PostgreSQL/SQL/サブクエリで複数カラムを返す - yanor.net/wiki

クエリを止める

select pg_stat_activity;

でpidを確認して

select pg_cancel_backend(pid);

でおk

集計に対するnullの扱い

sum, min, maxでは特に気にする必要は無いがcount, avg, stddevなどの集計の場合、nullは無視される。これを応用して、色んな条件での平均を計算したりできる。例えば毎日のデータに1週間分、1ヶ月分の集計を追加するとか出来る。

参考：floatingdays: SQLの集計関数で NULLを含む列を集計する場合の注意点

マテリアライズド・ビューの定義を表示

マテリアライズド・ビューの情報は通常のビューと違い、pg_matviewsに入っている。定義を確認する場合は

select definition from pg_matviews where matviewname='ビュー名';

でおk

コマンドラインでpsqlした結果をファイルに保存

カラム名などの情報無しでpsqlした結果を保存する

$ psql dbname -A -t -c "select ..." > result.txt

オプションで

-F ,

とか加えるとカンマ区切りになる

配列フィールドがhogeを含む条件をsqlで

select * from hoge where arr && 'fuga';

でarrという配列フィールドにfugaを含むものを取り出せる。良く使いそうなのでメモ

参考：配列関数と演算子