SQL関数
シーケンスの現在値を取得
「現在値の取得」ならばcurrval関数が思いつきますが、currval関数はnextval関数を実行したセッションからしか実行できません。
現在のセッションにおいて、そのシーケンスから nextval によって取得された直近の値を返します。 (セッション内で、シーケンスに対し nextval が呼ばれていない場合には、エラーが報告されます。) これはローカルのセッション値を返すことから、その時点で別のセッションが nextval を実行していたとしても、予想に違わない回答をもたらすことに注意してください。
主キーとしての値ではなく、単に「現在のシーケンスの値はいくつかな？」と確認したいときは、以下のselect文を実行してください。
SELECT last_value FROM sample_sequence
最大値や増分値など、その他のシーケンス情報は、以下のSQLで取得できます。
SELECT last_value FROM sample_sequence
なお、「nextval関数でinsertして、そのIDを知りたい」ときは、必ずcurrval関数を使ってください。
便利だと感じた関数
- regexp_split_to_table : POSIX正規表現を区切り文字に使ってstringを分割します。
PSQLコマンド関係
PostgresSQL用の環境変数
接続用の情報を環境変数に設定すれば、psqlコマンドのオプションが簡潔になります。
SET PGHOST=127.0.0.1
SET PGPORT=5432
SET PGDATABASE=sample_dbname
SET PGUSER=postgres
SET PGPASSWORD=password
psql --command "select * from sample_table;"
よく使うオプション
- -c / --command : SQLコマンド実行
- -e / --echo-queries : 実行したSQLコマンドも出力する
- -f / -- file : ファイルに書かれたSQLコマンドを実行
- -v / --variable : 変数の設定
- -W / --password : パスワードの強制入力を促す
- -1 / --single-transaction : 一つのトランザクションで実行（複数の-cオプション, -fオプションがある場合）
SQL文で変数を使用
SQL文で変数が使えると便利です。
たとえば、「指定した日に更新されたデータ数を、テーブルごとに調べたい」場合、以下のようになります。
count.sql
-- 各テーブルにtimestamp型の列"t"が存在する
select `sample_A`, count(*) from sample_A where t = :update_date ;
select `sample_B`, count(*) from sample_B where t = :update_date ;
count.bat
psql --variable update_date='2017-01-01' --file count.sql
複数のSQLファイルを実行
- 標準入力で、全ファイルを渡す方法
type sample1.sql sample2.sql | psql
- メタコマンド
\iを使う方法
all.sql
\i sample1.sql
\i sample2.sql
type all.sql | psql
pg_dump
formatの値は、以下の4種類から選択できます。
- plain
- custom
- directory
- tar
通常のバックアップの場合customを選択するとよいでしょう。
customが最も圧縮されます。
pg_restoreへの入力に適したカスタム形式アーカイブを出力します。 ディレクトリ出力形式と一緒に使用する場合、リストア時に手作業で保管された項目の選択、再順序付けできますので、これはもっとも柔軟な出力形式です。 また、この形式はデフォルトで圧縮されます。