postgreSQL

Postgresで別なDBにデータをコピーする方法

psql from_db -c "COPY (SELECT * FROM from_tbl WHERE ...) TO STDOUT" | psql to_db -c "COPY to_tbl FROM STDIN"テーブル丸ごととかなら pg_dump の出力を psql に渡せばよいのだが、テーブルの一部とか入れたいときはこうすればよさそう。カラムの値にタ…

postgres での空文字とNULL

postgres は空文字とNULLを区別する。 検証用テーブル test=# \d test Table "public.test" Column | Type | Modifiers --------+-----------------------+----------- text1 | character varying(20) | text2 | character varying(20) | test=# 空文字を投…

postgreSQL で不要領域の比率を出すクエリ

参考 PostgreSQL の VACUUM をなんとなくでするのはやめよう但しここに書いているクエリだと n_live_tup が0件の時にエラーになるので代替案。 SELECT relname, n_live_tup, n_dead_tup, CASE n_dead_tup WHEN 0 THEN 0 ELSE round(n_dead_tup*100/(n_live_t…

postgresの全文検索インデックス

全文検索用に GiST, GIN の2種類のインデックスが用意されている。https://www.postgresql.jp/document/9.3/html/textsearch-indexes.htmlGiST は tsvector またはtsquery 型のカラムに対して GIN は tsvector型のカラムに対して インデックスを付与できる。…

戻り値の型がrecordの場合の取り扱い方法

Postgresの関数の中で、recordを返すものがある。 つまり、テーブルをSelectした時みたいに複数のカラムを持つ複数行のレコードを返す関数だ。例えば、textsearch_ja の ja_analyze(text)関数。 こいつは、ある文字列を受け取って、それをmecabで形態素解析…

-f オプションで"COPY xxx FROM STDIN"が効かない。

echo "1 hoge" | psql test -c "COPY test FROM STDIN"とかってするとちゃんとデータが入るんだが、 [hoge@test]cat copy.sql COPY test FROM STDIN [hoge@test]echo "2 fuga" | psql test -f copy.sqlってやっても、正常終了するがデータは入っていない謎仕…

DISTINCT ON

http://www.odin.hyork.net/write/write0228.html知らんかった・・・標準SQLではないのだが、Postgre固有の方言でこんなことができるらしい。「○○毎に、XXでソートして最初の行を取得する」 「○○毎にXXが最大の行を取得する」 といったことがクエリ一本でで…

postgre のデータサイズ確認系SQL

DBサイズ一覧 select datname, pg_size_pretty(pg_database_size(datname)) from pg_database ORDER BY datname; テーブル、インデックスサイズ一覧 (postgre 9.1.1で確認。古いバージョン(8.3.1)だとエラー) SELECT relname, reltuples, pg_size_pretty(p…