Postgresql 2 / 3

psql の中から sql ファイルを実行する

関連

\i <file_path>を実行するだけです。

postgres=# \i sqls/create-table.sql

少しまとまった SQL を流したい場合は、中からシェルコマンドを実行すると組み合わせて、

\! vim sqls/hoge.sqlに SQL を書く
\i sqls/hoge.sqlで実行する

とすると楽かもしれません。

コマンドラインから変数を使う SQL を流す

関連

-v, --set, --variableオプションを使います。その後key=valueの形で設定していきます。
設定した値は.sqlファイルの中で:keyと:を頭に付けて参照できます。

foo というスキーマに hoge テーブルを作る

fooは変数で渡す形で書くとこのような SQL になります。

create table :schema.hoge ();
-- CREATE TABLE

この内容が書かれている.sqlファイルは、schemaという変数を定義した上で実行されることを期待しています。そのように変数を実行し、かつ SQL を流すコマンドは以下です。

psql -v schema=foo < create-hoge.sql
# CREATE TABLE

ちゃんとできていればいい感じです。

psql -c '\dt foo.*'
#         List of relations
#  Schema | Name | Type  |  Owner
# --------+------+-------+----------
#  foo    | hoge | table | postgres
# (1 row)

コマンドラインで SQL を書いて直接実行する

関連

-c, --commandオプションを使います。あとに SQL やpsql内部で使えるコマンドを書きます。

psql -c 'select 1 as one'
#  one
# -----
#    1
# (1 row)

カラムを縦並びで表示

psql内部では\xコマンドでカラムの縦・横並びを切り替えできました。これはコマンドラインでは-x, --expandedオプションで切り替えられます。

psql -x -c 'select 1 as one'
# -[ RECORD 1 ]
# one | 1

ダンプとリストア

関連

ダンプ

ダンプにはそれ用のコマンドpg_dumpがありこれでダンプファイルの作成ができます。これの接続時のオプションなどはpsqlと同じです。

以下でoutput.sqlにダンプできます。

PGPASSWORD=password pg_dump \
  -h host \
  -p port \
  -U username \
  foo_database > output.sql

接続情報にはpostgres://スキーマを使うこともできます。

pg_dump postgres://.../foo_database > output.sql

リストア

ダンプされたファイルはそのままpsqlで扱える形なので、シェルのリダイレクションで取り込むことができます。databaseがない場合は先に作っておきます。

psql postgres://... -c 'create database foo_database'
psql postgres://.../foo_database < output.sql

クエリを一定時間毎に実行

関連

\watchコマンドを使います。SQL文; \watch 2のように実行すると SQL 文を２秒毎に実行してくれます。

watchコマンドを確認する

ターミナルを２つ開いて以下のようにして見てみます。テーブルはfooという名前のものを使います。

テーブルに３秒毎にinsertする
テーブルの最新１件を２秒毎に表示する

準備

とりあえず以下でfooを作ります。

create table foo (
  id serial primary key not null
);

1番目の方では以下でinsertを走らせます。\watch 3で３秒毎走らせるということになります。
ちなみにdefault valuesはすべてのカラムでデフォルト値を使うということになるのでvaluesを省略できます、

insert into foo default values; \watch 3

2番目の方では以下でselectを走らせます。

select * from foo order by id desc limit 1; \watch 2

これで3秒毎に新しいidのデータが増えていくので、その度selectで取得するデータも変わるはずです。

実行結果

右が1です。左側には1,2回毎に新しいレコードが表示されています。（\xで縦表示にしています）

色々と結果を見ながらいじりたい時に便利だと思います。

変数を使う

関連

\set name valueを使います。例えば以下でtablenameにpostsという値を設定しています。

postgres=# \set tablename posts

設定できたかどうかは\setと実行します。恐らく最後に出てくるはずです。

postgres=# \set
...
tablename = 'posts'

何か-を挟んだもの（table-nameみたいな）変数名はうまく SQL の中で使えなかったので、避けたほうがいいかもしれないです。

では、使ってみます。

postgres=# create table :tablename (content text);
CREATE TABLE

postgres=# insert into :tablename values ('aaa');
INSERT 0 1

postgres=# select * from :tablename;
 content
---------
 aaa
(1 row)

「もう必要ないな」となったら\unset nameで削除できます。

postgres-# \unset tablename

postgres=# \set
...

好きな値を表示

関連

DO $$ ... $$で SQL の塊を作ることができます。
RAISEはトランザクション時に予期せぬ事が起こったときに使うものみたいですが、これを使ってログみたいな情報を流せるみたいですね。

DO $$
BEGIN
  RAISE NOTICE 'aaa % %', 'b', 'c';
END
$$;

NOTICE:  aaa b c
DO

環境変数

関連

PGHOST

-hオプションと同様に接続先のホストを指定できます。

psql -h ...
PGHOST=... psql

PGPORT

-pオプションと同様に接続先のホストを指定できます。

psql -p ...
PGPORT=... psql

PGUSER

-Uオプションと同様に接続先のホストを指定できます。

psql -U ...
PGUSER=... psql

PGPASSWORD

psqlコマンドを実行した時にパスワードを聞かれずにログインすることができます。

PGPASSWORD=... psql

fish shell で環境変数を設定

以下のような Docker で Postgresql を起動するとして、

docker run --rm -p 54321:5432 postgres

.config/fish/config.fishに以下のように書くとpsqlだけで接続できるようになります。

set -gx PGHOST 0.0.0.0
set -gx PGUSER postgres
set -gx PGPORT 54321
set -gx PGPASSWORD ''

クエリの実行にどれだけかかっているか調べる

関連

\timingコマンドを先に実行します。

\timing
-- Timing is on.

あとは好きなクエリを実行するとTime: 1 msのようにかかった時間も表示しれくれるようになります。

alter table hoge add id serial;
-- ALTER TABLE
-- Time: 9.109 ms

サブクエリ

関連

（親）SQL の中に( )で囲んで子 SQL 文を書くと、実行時にその部分の結果に置き換わった状態で親 SQL が実行されます。

例えば以下のサブクエリは、barテーブルからvalueがnju33なレコードfoo_idを返します。

select * from foo
  where foo_id = (
    select foo_id from bar where value = 'nju33'
  );

例えばそれが33だった場合、以下のように展開されます。

select * from foo where foo_id = 33;

またwith構文を使う方法もあります。

環境。

create schema tmp;

set search_path to tmp;

create table foo (
  id serial,
  foo varchar(255)
);

create table bar (
  id serial,
  bar varchar(255)
);

insert into foo (foo) values ('a'), ('b'), ('c');
insert into bar (bar) values ('1'), ('2'), ('3');

以下はselect * from bar where id > 1の結果テーブルに対してinner joinするという意味になる。

with as_bar as (select * from bar where id > 1)
select foo.id, foo.foo, as_bar.bar
  from foo
  inner join as_bar
  on foo.id = as_bar.id;

この結果は以下のようになります。

 id | foo | bar
----+-----+-----
  2 | b   | 2
  3 | c   | 3
(2 rows