<code>\i &lt;file_path&gt;</code>を実行するだけです。<pre><code class=language-txt>postgres=# \i sqls/create-table.sql</code></pre>少しまとまった SQL を流したい場合は、 <a href="https://nju33.com/note/post?note=postgresql&amp;post=%E4%B8%AD%E3%81%8B%E3%82%89%E3%82%B7%E3%82%A7%E3%83%AB%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89%E3%82%92%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B">中からシェルコマンドを実行する</a>と組み合わせて、<ol><li><code>\! vim sqls/hoge.sql</code>に SQL を書く</li><li><code>\i sqls/hoge.sql</code>で実行する</li></ol>とすると楽かもしれません。

`\i <file_path>`を付けることで`<file_path>`に書かれている SQL を実行することができます。

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

<code>postgres</code>スキーマを使うと以下の形で接続できます。<code>password</code>（未設定時）や<code>dbname</code>は省略できます。<pre><code class=language-sh>psql postgres://&lt;username&gt;:&lt;password&gt;@&lt;host&gt;:&lt;port&gt;(/[dbname])?
</code></pre>例えばこんな感じです。<pre><code class=language-sh>psql postgresql://postgres:@0.0.0.0:54321
# dbname 有り
psql postgresql://postgres:@0.0.0.0:54321/foo
</code></pre>またそれぞれの項目は<code>username</code>なら<code>--username Username</code>のようなオプションとして渡せます。<pre><code class=language-sh>psql \
 --username ... \
 --password ... \
 --host ... \
 --post ... \
 --dbname ...
</code></pre>

接続

データベースの作成には`create database データベース名`を使います。データベースの削除には`drop database データーベース名`を使います。

データーベースの作成と削除

作る場合には<code>create schema</code>を使います。以下は<code>foo</code>名前空間を作る例です。<pre><code class=language-sql>postgres=# create schema foo;
CREATE SCHEMA
</code></pre>作れたかどうかは<code>\dn</code>と叩きます。<pre><code class=language-txt>postgres=# \dn
 List of schemas
 Name | Owner
--------+----------
 foo | postgres
 public | postgres
(2 rows)
</code></pre><code>foo</code>がありました。「もう要らない」「削除したい」場合は<code>drop schema</code>で削除できます。<code>if exists</code>を付けると対象の名前空間がない場合は処理がスキップされエラー（<code>ERROR: schema &quot;foo&quot; does not exist</code>）を回避できます。また、中にテーブルなどが存在する場合最後に<code>cascade</code>と付けることでそれごと削除できます。<pre><code class=language-sql>postgres=# drop schema if exists foo;
DROP SCHEMA
</code></pre>

名前空間を作る・削除する

<div class=n-md_h3-box><a href=#テーブルの作成とテーブルスキーマ確認と削除+テーブルを作成する aria-label=テーブルを作成するのアンカー class=n-md_h3-anchor></a><h3 id=テーブルの作成とテーブルスキーマ確認と削除+テーブルを作成する class=n-md_h3-heading>テーブルを作成する</h3></div><code>create table テーブル名</code>を実行します。<pre><code class=language-txt>create table foo (
 id serial primary key not null
);</code></pre>ちゃんと作成されたか<code>\dt</code>で確認します。<pre><code class=language-txt>postgres=# \dt
 List of relations
 Schema | Name | Type | Owner
--------+-------+-------+----------
 public | foo | table | postgres
(1 row)</code></pre><div class=n-md_h3-box><a href=#テーブルの作成とテーブルスキーマ確認と削除+テーブルスキーマを確認する aria-label=テーブルスキーマを確認するのアンカー class=n-md_h3-anchor></a><h3 id=テーブルの作成とテーブルスキーマ確認と削除+テーブルスキーマを確認する class=n-md_h3-heading>テーブルスキーマを確認する</h3></div><code>\d &lt;table_name&gt;</code>を実行します。例えば<code>\d foo</code>とすると、<pre><code class=language-txt>postgres=# \d foo
 Table "public.foo"
 Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------------------------------
 id | integer | | not null | nextval('foo_id_seq'::regclass)
Indexes:
 "foo_pkey" PRIMARY KEY, btree (id)</code></pre>のように見ることができます。<div class=n-md_h3-box><a href=#テーブルの作成とテーブルスキーマ確認と削除+テーブルを削除する aria-label=テーブルを削除するのアンカー class=n-md_h3-anchor></a><h3 id=テーブルの作成とテーブルスキーマ確認と削除+テーブルを削除する class=n-md_h3-heading>テーブルを削除する</h3></div><code>drop table テーブル名</code>を実行します。上記で<code>foo</code>テーブルを作ったので<code>drop table foo;</code>ですね。<pre><code class=language-txt>postgres=# drop table foo;
DROP TABLE</code></pre>ちなみにここでもう一度<code>foo</code>テーブルを削除しようとすると、以下のようにエラーになります。<pre><code class=language-txt>postgres=# drop table foo;
ERROR: table "foo" does not exist</code></pre>これは<code>table</code>の後に<code>if exists</code>（存在する時）を付けることで回避できます。<pre><code class=language-txt>postgres=# drop table if exists foo;
NOTICE: table "foo" does not exist, skipping
DROP TABLE</code></pre>同じように<code>create table</code>も既にある場合はエラーになってしまいますが、こちらの場合は<code>if not exists</code>（存在しない時）を付けてあげると回避できます。<pre><code class=language-txt>create table if not exists foo (id text);
NOTICE: relation "foo" already exists, skipping
CREATE TABLE</code></pre>

テーブルの作成には`create table テーブル名`を実行します。テーブルの削除には`drop table テーブル名`を実行します。

テーブルの作成とテーブルスキーマ確認と削除

<code>\conninfo</code>コマンドを使います。以下はその例です。<pre><code class=language-txt>postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "0.0.0.0" at port "4288".</code></pre>上記は、以下の<code>docker</code>で建てた Postgresql 環境です。<pre><code class=language-sh>docker run --name db --rm -p 4288:5432 -e POSTGRES_PASSWORD=postgres postgres:latest </code></pre>

現在の接続状況を調べる

ちょっと準備として、<code>foo</code>と<code>public</code>という２つのスキーマがあり、今は<code>public</code>を選択したいる状態というにします。<pre><code class=language-txt>postgres=# create schema foo;
CREATE SCHEMA

postgres-# \dn
 List of schemas
 Name | Owner
--------+----------
 foo | postgres
 public | postgres
(2 rows)

postgres=# select current_schema;
 current_schema
----------------
 public
(1 row)</code></pre>ここで<code>foo</code>を選択するには<code>set search_path to &lt;スキーマ名&gt;</code>を実行するだけです。（<code>to</code>は<code>=</code>でもいけます）<pre><code class=language-txt>postgres=# set search_path = foo;
SET

postgres=# select current_schema;
 current_schema
----------------
 foo
(1 row)</code></pre>

`set search_path = スキーマ名`を実行します。

参照スキーマを変更する

<div class=n-md_h3-box><a href=#挿入_(insert)_、更新_(update)_、削除_(delete)+挿入_(insert) aria-label="挿入 (insert)のアンカー" class=n-md_h3-anchor></a><h3 id=挿入_(insert)_、更新_(update)_、削除_(delete)+挿入_(insert) class=n-md_h3-heading>挿入 (insert)</h3></div><code>INSERT INTO</code>文を使い以下のような形でデータを挿入できます。括弧の中のカラム名の並びとそのデータとなる値は同じ位置に置く必要があります。<pre><code class=language-sql>INSERT INTO table_name (column1, column2, ...) VALUEAS (value1, value2, ...);</code></pre><code>values</code>以下は<code>,</code>でつなげて書くことにより一度に複数レコードを挿入することができます。<pre><code class=language-sql>INSERT INTO table_name (column1, column2, ...) VALUEAS
 (value1, value2, ...),
 (value1, value2, ...),
 (value1, value2, ...);</code></pre><h4 id="values に渡す値がない場合">values に渡す値がない場合</h4>すべてデフォルト値で入れたい項目しかない場合、<code>VALUES (...)</code>ではなく<code>DEFAULT VALUES</code>が使えます。<pre><code class=language-sql>INSERT INTO table_name DEFAULT VALUES
</code></pre><div class=n-md_h3-box><a href=#挿入_(insert)_、更新_(update)_、削除_(delete)+更新_(update) aria-label="更新 (update)のアンカー" class=n-md_h3-anchor></a><h3 id=挿入_(insert)_、更新_(update)_、削除_(delete)+更新_(update) class=n-md_h3-heading>更新 (update)</h3></div><code>UPDATE</code>文を使い以下のような形でデータを挿入できます。<pre><code class=language-sql>UPDATE table_name set foo = 'updated foo';</code></pre>上記ではすべてのレコードの<code>foo</code>カラムの値を<code>updated foo</code>に更新してしまいます。更新データを絞り込むには<code>where</code>を使うことができます。<pre><code class=language-sql>UPDATE table_name SET foo = 'updated foo' WHERE id = 1;</code></pre>これで<code>id</code>が<code>1</code>のものだけを更新することができます。また複数カラムを一度に更新したい場合は<code>set</code>以下の値の代入部分を<code>,</code>で繋げることで可能です。<pre><code class=language-sql>UPDATE table_name
 SET foo = 'updated foo', bar = 'updated bar'
 WHERE id = 1;</code></pre><div class=n-md_h3-box><a href=#挿入_(insert)_、更新_(update)_、削除_(delete)+削除_(delete) aria-label="削除 (delete)のアンカー" class=n-md_h3-anchor></a><h3 id=挿入_(insert)_、更新_(update)_、削除_(delete)+削除_(delete) class=n-md_h3-heading>削除 (delete)</h3></div><code>DELETE FROM</code>文を使い以下のような形でデータを削除できます。<pre><code class=language-sql>DELETE FROM table_name;</code></pre>これも<code>UPDATE</code>のようにそのままだと、そのテーブルのレコードすべてが対象になってしまうので<code>WHERE</code>で絞ることができます。<pre><code class=language-sql>DELETE FROM table_name WHERE id = 1;</code></pre>これで<code>id</code>が<code>1</code>のものだけを削除することができます。

挿入 (insert) 、更新 (update) 、削除 (delete)

各カラムには<code>CHECK</code>や<code>UNIQUE</code>、<code>PRIMARY KEY</code>、<code>REFERENCES</code>などの制約を設定できます。<div class=n-md_h3-box><a href=#制約+CHECK aria-label=CHECKのアンカー class=n-md_h3-anchor></a><h3 id=制約+CHECK class=n-md_h3-heading>CHECK</h3></div><code>CHECK (condition)</code>のように書け、<code>condition</code>が<code>true</code>なデータだけ登録するように制限できます。以下は、<code>number</code>へは<code>1</code>以上の値しか入れれないという制限を設定してます。もしその条件を満たさないものを入れようとするとエラーが起こります。<pre><code class=language-sql>CREATE TABLE example_check (
 number INTEGER CHECK ( number &gt; 0 )
);
-- CREATE TABLE

INSERT INTO example_check (number) 
 VALUES ( -1 );
-- ERROR: new row for relation "example_check" violates check constraint "example_check_number_check"
</code></pre>エラー文にある通りこの制約名はデフォルトで<code>&lt;table_name&gt;_&lt;column_name&gt;_check</code>です。<div class=n-md_h3-box><a href=#制約+UNIQUE aria-label=UNIQUEのアンカー class=n-md_h3-anchor></a><h3 id=制約+UNIQUE class=n-md_h3-heading>UNIQUE</h3></div>単に<code>UNIEUQ</code>を置いて設定します。これを設定したカラムの値は全レコード全てで異なる値でなければなれないという制限ができます。以下は、<code>number</code>へ<code>1</code>を2回入れようとしたためエラーが起きています。<pre><code class=language-sql>CREATE TABLE example_unique (
 number INTEGER UNIQUE
);
-- CREATE TABLE

INSERT INTO
 example_unique ( number ) 
VALUES
 ( 1 ), ( 1 );
-- ERROR: duplicate key value violates unique constraint "example_unique_number_key"
</code></pre>エラー文にある通りこの制約名はデフォルトで<code>&lt;table_name&gt;_&lt;column_name&gt;_key</code>です。<div class=n-md_h3-box><a href=#制約+PRIMARY_KEY aria-label="PRIMARY KEYのアンカー" class=n-md_h3-anchor></a><h3 id=制約+PRIMARY_KEY class=n-md_h3-heading>PRIMARY KEY</h3></div>単に<code>UNIEUQ</code>を置いて設定します。そのテーブルでの主キーを設定できます。こちらもユニークと同じようにユニークである必要があります。<pre><code class=language-sql>CREATE TABLE example_primary_key (
 id INTEGER PRIMARY KEY
);
-- CREATE TABLE

INSERT INTO
 example_unique ( number ) 
VALUES
 ( 1 ), ( 1 );
-- ERROR: duplicate key value violates unique constraint "example_primary_key_pkey"
</code></pre>エラー文にある通りこの制約名はデフォルトで<code>&lt;table_name&gt;_pkey</code>です。<div class=n-md_h3-box><a href=#制約+REFERENCES aria-label=REFERENCESのアンカー class=n-md_h3-anchor></a><h3 id=制約+REFERENCES class=n-md_h3-heading>REFERENCES</h3></div><code>REFERENCES table(column, ...)</code>のように書き、外部テーブルとの関連した値を安全に記録する為に使います。制約名はデフォルトで<code>&lt;table_name&gt;_&lt;column_name&gt;_fkey</code>です。例を見る前に以下で、外部テーブルとして使うテーブルを作っておきます。<pre><code class=language-sql>CREATE TABLE example_references_parent (
 id SERIAL PRIMARY KEY
);
</code></pre>それを使うテーブルを以下のように作ります。<pre><code class=language-sql>CREATE TABLE example_references1 (
 parent_id INTEGER REFERENCES example_references_parent(id)
);
</code></pre>まだ<code>example_references_parent</code>にはレコードは1つもありません。その状態で<code>example_references1</code>へ適当な<code>parent_id</code>指定で入れようとしても、<pre><code class=language-sql>INSERT INTO
 example_references1 (parent_id) 
VALUES
 ( 1 );
-- ERROR: insert or update on table "example_references1" violates foreign key constraint "example_references1_parent_id_fkey"
</code></pre>エラーになります。これは<code>parent_id</code>へ入れられる値は、<code>example_references_parent</code>に実在するある<code>id</code>値である必要がある為です。このエラーは先にそのような値を入れておくことで回避できます。<pre><code class=language-sql>INSERT INTO example_references_parent DEFAULT VALUES;
-- INSERT 0 1
</code></pre>参照テーブルに<code>id</code>が<code>1</code>のレコードを入れたので、先程エラーになったものをもう1度試してみます。<pre><code class=language-sql>INSERT INTO
 example_references1 (parent_id) 
VALUES
 ( 1 );
-- INSERT 0 1
</code></pre>今度は大丈夫でした！そしてこの状態で参照側のレコードを消してみます。<pre><code class=language-sql>DELETE FROM example_references_parent;
-- ERROR: update or delete on table "example_references_parent" violates foreign key constraint "example_references1_parent_id_fkey" on table "example_references1"
</code></pre>が、この制約がちゃんと付いてると参照されているレコードは上記のようなエラーがでて消すことができなくなります。これを回避するには先に参照しているレコードを消す必要があります。<pre><code class=language-sql>DELETE FROM example_references1;
-- DELETE 1

DELETE FROM example_references_parent;
-- DELETE 1
</code></pre>もし、参照してる値がとても多い場合これでは不便かもしれません。上記の削除の動作を1度に行える方法があります。それは制約を設定する時に<code>ON UPDATE CASCADE ON DELETE CASCADE</code>を付けます。<pre><code class=language-sql>CREATE TABLE example_references2 (
 parent_id INTEGER REFERENCES example_references_parent(id) 
 ON UPDATE CASCADE 
 ON DELETE CASCADE
);
-- CREATE TABLE
</code></pre>先程消してしまった参照テーブルと今作ったテーブルに値を入れておきます。<pre><code class=language-sql>INSERT INTO example_references_parent DEFAULT VALUES;
-- `id: 2`のレコードが入る

INSERT INTO
 example_references2 (parent_id) 
VALUES
 ( 2 );
-- INSERT 0 1
</code></pre>この状態だと参照元データを削除できます。<pre><code class=language-sql>DELETE FROM example_references_parent;
-- DELETE 1
</code></pre>削除してみます。<pre><code class=language-sql>SELECT * FROM example_references2 \gx
-- (0 rows)

</code></pre>参照してたレコードも削除されました！これは<code>ON DELTE CASCADE</code>によって、参照元が削除された時にそれを参照しているレコードも削除するように設定した為です。 そういえば、<code>ON UPDATE CASCADE</code>も一緒に設定してました。これは参照元の値が更新された時に、それを参照しているレコードの値も更新されます。（例えば親が<code>id:1</code>で子が<code>parent_id:1</code>の時、<code>id:2</code>へ更新すると連藤して<code>parent_id:2</code>に更新される）

制約

これは<code>insert</code>でレコードを追加した後に<code>select ... limit 1</code>をせずに一度に終わらせるものです。<div class=n-md_h3-box><a href=#insert_時に作ったレコードを返す+確認用の準備 aria-label=確認用の準備のアンカー class=n-md_h3-anchor></a><h3 id=insert_時に作ったレコードを返す+確認用の準備 class=n-md_h3-heading>確認用の準備</h3></div>以下のように<code>foo</code>テーブルを作っておきます。<pre><code class=language-txt>postgres=# create table foo (id serial primary key not null, text text not null);
CREATE TABLE</code></pre><div class=n-md_h3-box><a href=#insert_時に作ったレコードを返す+実際に確認 aria-label=実際に確認のアンカー class=n-md_h3-anchor></a><h3 id=insert_時に作ったレコードを返す+実際に確認 class=n-md_h3-heading>実際に確認</h3></div>まず、普通に<code>insert</code>した時はこうなります。「1件追加しました」という情報だけ返っています。<pre><code class=language-txt>postgres=# insert into foo (text) values ('hoge');
INSERT 0 1</code></pre><h4 id="returning を使う">returning を使う</h4>ですが、後ろに<code>returning *</code>を追加すると今入れたレコードが返るようになります。<pre><code class=language-txt>postgres=# insert into foo (text) values ('fuga') returning *;
 id | text
----+------
 2 | fuga
(1 row)

INSERT 0 1</code></pre><code>*</code>は<code>select ...</code>のカラム指定と同じように指定できます。したがって、カラム名を指定すればそれだけを返すようにできます。<pre><code class=language-txt>postgres=# insert into foo (text) values ('piyo') returning text;
 text
------
 piyo
(1 row)

INSERT 0 1</code></pre>

`insert`時の最後に`returning`を付けることで、`insert`からの`select ... limit 1`を一度に実行することができます。

insert 時に作ったレコードを返す

<code>\!</code>の後ろにシェルコマンドを書いて実行します。こんな感じです。<pre><code class=language-txt>\! cat foo.txt</code></pre><code>vim</code>などもちゃんとエディターが開いてくれます。ただ<code>.sql</code>ファイルの実行は<a href="https://nju33.com/note/post?note=postgresql&amp;post=psql%20%E3%81%AE%E4%B8%AD%E3%81%8B%E3%82%89%20sql%20%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B"><code>\i</code></a>という便利なコマンドがあるのでそちらを使ったほうがいいと思います。

中からシェルコマンドを実行する

<code>-v, --set, --variable</code>オプションを使います。その後<code>key=value</code>の形で設定していきます。 設定した値は<code>.sql</code>ファイルの中で<code>:key</code>と<code>:</code>を頭に付けて参照できます。<div class=n-md_h3-box><a href=#コマンドラインから変数を使う_SQL_を流す+foo_というスキーマに_hoge_テーブルを作る aria-label="foo というスキーマに hoge テーブルを作るのアンカー" class=n-md_h3-anchor></a><h3 id=コマンドラインから変数を使う_SQL_を流す+foo_というスキーマに_hoge_テーブルを作る class=n-md_h3-heading>foo というスキーマに hoge テーブルを作る</h3></div><code>foo</code>は変数で渡す形で書くとこのような SQL になります。<pre><code class=language-sql>create table :schema.hoge ();
-- CREATE TABLE</code></pre>この内容が書かれている<code>.sql</code>ファイルは、<code>schema</code>という変数を定義した上で実行されることを期待しています。そのように変数を実行し、かつ SQL を流すコマンドは以下です。<pre><code class=language-sh>psql -v schema=foo &lt; create-hoge.sql
# CREATE TABLE</code></pre>ちゃんとできていればいい感じです。<pre><code class=language-sh>psql -c '\dt foo.*'
# List of relations
# Schema | Name | Type | Owner
# --------+------+-------+----------
# foo | hoge | table | postgres
# (1 row)</code></pre>

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

<code>-c, --command</code>オプションを使います。あとに SQL や<code>psql</code>内部で使えるコマンドを書きます。<pre><code class=language-sh>psql -c 'select 1 as one'
# one
# -----
# 1
# (1 row)</code></pre><div class=n-md_h3-box><a href=#コマンドラインで_SQL_を書いて直接実行する+カラムを縦並びで表示 aria-label=カラムを縦並びで表示のアンカー class=n-md_h3-anchor></a><h3 id=コマンドラインで_SQL_を書いて直接実行する+カラムを縦並びで表示 class=n-md_h3-heading>カラムを縦並びで表示</h3></div><code>psql</code>内部では<code>\x</code>コマンドでカラムの縦・横並びを切り替えできました。これはコマンドラインでは<code>-x, --expanded</code>オプションで切り替えられます。<pre><code class=language-sh>psql -x -c 'select 1 as one'
# -[ RECORD 1 ]
# one | 1</code></pre>

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

<div class=n-md_h3-box><a href=#ダンプとリストア+ダンプ aria-label=ダンプのアンカー class=n-md_h3-anchor></a><h3 id=ダンプとリストア+ダンプ class=n-md_h3-heading>ダンプ</h3></div>ダンプにはそれ用のコマンド<code>pg_dump</code>がありこれでダンプファイルの作成ができます。これの接続時のオプションなどは<code>psql</code>と同じです。以下で<code>output.sql</code>にダンプできます。<pre><code class=language-sh>PGPASSWORD=password pg_dump \
 -h host \
 -p port \
 -U username \
 foo_database &gt; output.sql
</code></pre>接続情報には<code>postgres://</code>スキーマを使うこともできます。<pre><code class=language-sh>pg_dump postgres://.../foo_database &gt; output.sql
</code></pre><div class=n-md_h3-box><a href=#ダンプとリストア+リストア aria-label=リストアのアンカー class=n-md_h3-anchor></a><h3 id=ダンプとリストア+リストア class=n-md_h3-heading>リストア</h3></div>ダンプされたファイルはそのまま<code>psql</code>で扱える形なので、シェルのリダイレクションで取り込むことができます。<code>database</code>がない場合は先に作っておきます。<pre><code class=language-sh>psql postgres://... -c 'create database foo_database'
psql postgres://.../foo_database &lt; output.sql
</code></pre>

ダンプとリストア

<code>\watch</code>コマンドを使います。<code>SQL文; \watch 2</code>のように実行すると SQL 文を２秒毎に実行してくれます。<div class=n-md_h3-box><a href=#クエリを一定時間毎に実行+watchコマンドを確認する aria-label=watchコマンドを確認するのアンカー class=n-md_h3-anchor></a><h3 id=クエリを一定時間毎に実行+watchコマンドを確認する class=n-md_h3-heading>watchコマンドを確認する</h3></div>ターミナルを２つ開いて以下のようにして見てみます。テーブルは<code>foo</code>という名前のものを使います。<ol><li>テーブルに３秒毎に<code>insert</code>する</li><li>テーブルの最新１件を２秒毎に表示する</li></ol><h4 id=準備>準備</h4>とりあえず以下で<code>foo</code>を作ります。<pre><code class=language-sql>create table foo (
 id serial primary key not null
);</code></pre>1番目の方では以下で<code>insert</code>を走らせます。<code>\watch 3</code>で３秒毎走らせるということになります。 ちなみに<code>default values</code>はすべてのカラムでデフォルト値を使うということになるので<code>values</code>を省略できます、<pre><code class=language-sql>insert into foo default values; \watch 3</code></pre>2番目の方では以下で<code>select</code>を走らせます。<pre><code class=language-sql>select * from foo order by id desc limit 1; \watch 2</code></pre>これで3秒毎に新しい<code>id</code>のデータが増えていくので、その度<code>select</code>で取得するデータも変わるはずです。<h4 id=実行結果>実行結果</h4>右が1です。左側には1,2回毎に新しいレコードが表示されています。（<code>\x</code>で縦表示にしています）<a href=https://gyazo.com/93abeb90b6bc4c8e0abb09c08906cc6c><div data-tag=img data-src=https://i.gyazo.com/93abeb90b6bc4c8e0abb09c08906cc6c.png alt=null style="min-height: 30em"></div></a>色々と結果を見ながらいじりたい時に便利だと思います。

`\watch`コマンドを使うと左側に書いた SQL を指定秒毎に走らせることができます。例えば最新のレコードを２秒毎に表示させたいなら`select * from table order by id desc limit 1; \watch 2`とします。

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

<code>\set name value</code>を使います。例えば以下で<code>tablename</code>に<code>posts</code>という値を設定しています。<pre><code class=language-txt>postgres=# \set tablename posts</code></pre>設定できたかどうかは<code>\set</code>と実行します。恐らく最後に出てくるはずです。<pre><code class=language-txt>postgres=# \set
...
tablename = 'posts'</code></pre>何か<code>-</code>を挟んだもの（<code>table-name</code>みたいな）変数名はうまく SQL の中で使えなかったので、避けたほうがいいかもしれないです。では、使ってみます。<pre><code class=language-txt>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)</code></pre>「もう必要ないな」となったら<code>\unset name</code>で削除できます。<pre><code class=language-txt>postgres-# \unset tablename

postgres=# \set
...</code></pre>

`\set name value`で変数を登録でき、`:name`の形で SQL の中で使うことができます。必要なくなった時は`\unset name`で削除することができます。

変数を使う

<code>DO $$ ... $$</code>で SQL の塊を作ることができます。 <code>RAISE</code>はトランザクション時に予期せぬ事が起こったときに使うものみたいですが、これを使ってログみたいな情報を流せるみたいですね。<pre><code class=language-sql>DO $$
BEGIN
 RAISE NOTICE 'aaa % %', 'b', 'c';
END
$$;

NOTICE: aaa b c
DO</code></pre>

好きな値を表示

<div class=n-md_h3-box><a href=#環境変数+PGHOST aria-label=PGHOSTのアンカー class=n-md_h3-anchor></a><h3 id=環境変数+PGHOST class=n-md_h3-heading>PGHOST</h3></div><code>-h</code>オプションと同様に接続先のホストを指定できます。<pre><code class=language-sh>psql -h ...
PGHOST=... psql</code></pre><div class=n-md_h3-box><a href=#環境変数+PGPORT aria-label=PGPORTのアンカー class=n-md_h3-anchor></a><h3 id=環境変数+PGPORT class=n-md_h3-heading>PGPORT</h3></div><code>-p</code>オプションと同様に接続先のホストを指定できます。<pre><code class=language-sh>psql -p ...
PGPORT=... psql</code></pre><div class=n-md_h3-box><a href=#環境変数+PGUSER aria-label=PGUSERのアンカー class=n-md_h3-anchor></a><h3 id=環境変数+PGUSER class=n-md_h3-heading>PGUSER</h3></div><code>-U</code>オプションと同様に接続先のホストを指定できます。<pre><code class=language-sh>psql -U ...
PGUSER=... psql</code></pre><div class=n-md_h3-box><a href=#環境変数+PGPASSWORD aria-label=PGPASSWORDのアンカー class=n-md_h3-anchor></a><h3 id=環境変数+PGPASSWORD class=n-md_h3-heading>PGPASSWORD</h3></div><code>psql</code>コマンドを実行した時にパスワードを聞かれずにログインすることができます。<pre><code class=language-sh>PGPASSWORD=... psql</code></pre><div class=n-md_h3-box><a href=#環境変数+fish_shell_で環境変数を設定 aria-label="fish shell で環境変数を設定のアンカー" class=n-md_h3-anchor></a><h3 id=環境変数+fish_shell_で環境変数を設定 class=n-md_h3-heading>fish shell で環境変数を設定</h3></div>以下のような Docker で Postgresql を起動するとして、<pre><code class=language-sh>docker run --rm -p 54321:5432 postgres</code></pre><code>.config/fish/config.fish</code>に以下のように書くと<code>psql</code>だけで接続できるようになります。<pre><code class=language-txt>set -gx PGHOST 0.0.0.0
set -gx PGUSER postgres
set -gx PGPORT 54321
set -gx PGPASSWORD ''</code></pre>

環境変数

<code>\timing</code>コマンドを先に実行します。<pre><code class=language-sql>\timing
-- Timing is on.</code></pre>あとは好きなクエリを実行すると<code>Time: 1 ms</code>のようにかかった時間も表示しれくれるようになります。<pre><code class=language-sh>alter table hoge add id serial;
-- ALTER TABLE
-- Time: 9.109 ms</code></pre>

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

（親）SQL の中に<code>( )</code>で囲んで子 SQL 文を書くと、実行時にその部分の結果に置き換わった状態で親 SQL が実行されます。例えば以下のサブクエリは、<code>bar</code>テーブルから<code>value</code>が<code>nju33</code>なレコード<code>foo_id</code>を返します。<pre><code class=language-sql>select * from foo
 where foo_id = (
 select foo_id from bar where value = 'nju33'
 );
</code></pre>例えばそれが<code>33</code>だった場合、以下のように展開されます。<pre><code class=language-sql>select * from foo where foo_id = 33;</code></pre>また<code>with</code>構文を使う方法もあります。環境。<pre><code class=language-sql>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');
</code></pre>以下は<code>select * from bar where id &gt; 1</code>の結果テーブルに対して<code>inner join</code>するという意味になる。<pre><code class=language-sql>with as_bar as (select * from bar where id &gt; 1)
select foo.id, foo.foo, as_bar.bar
 from foo
 inner join as_bar
 on foo.id = as_bar.id;
</code></pre>この結果は以下のようになります。<pre><code class=language-txt> id | foo | bar
----+-----+-----
 2 | b | 2
 3 | c | 3
(2 rows
</code></pre>

サブクエリ

集約関数を使うと、結果のある列のデータから演算を行い単一の結果を受け取ることができます。集約関数には、<ol><li><code>AVG</code></li><li><code>COUNT</code></li><li><code>MAX</code></li><li><code>MIN</code></li><li><code>SUM</code></li></ol>などあります。以下のようなデータが入ってる<code>sandbox</code>データベースで動作を見ていきます。<pre><code class=language-txt> id | number
----+--------
 1 | 123
 2 | 343
 3 | 534
 4 | 513
 5 | 643
 6 | 141
 7 | 643
 8 | 981
</code></pre><div class=n-md_h3-box><a href=#集約関数+AVG aria-label=AVGのアンカー class=n-md_h3-anchor></a><h3 id=集約関数+AVG class=n-md_h3-heading>AVG</h3></div><code>AVG</code>は列データの平均値を求めます。<pre><code class=language-sql>SELECT AVG(number) FROM sandbox \gx
-- avg | 490.1250000000000000
</code></pre><div class=n-md_h3-box><a href=#集約関数+COUNT aria-label=COUNTのアンカー class=n-md_h3-anchor></a><h3 id=集約関数+COUNT class=n-md_h3-heading>COUNT</h3></div><code>COUNT</code>は列データの個数を求めます。<pre><code class=language-sql>SELECT count(*) FROM sandbox \gx
SELECT count(number) FROM sandbox \gx
-- count | 8
</code></pre><div class=n-md_h3-box><a href=#集約関数+MAX,_MIN aria-label="MAX, MINのアンカー" class=n-md_h3-anchor></a><h3 id=集約関数+MAX,_MIN class=n-md_h3-heading>MAX, MIN</h3></div><code>MAX</code>は列データの中での最大値、<code>MIN</code>は最小値を探します。<pre><code class=language-sql>SELECT MAX(number) FROM sandbox \gx
-- max | 981

SELECT MIN(number) FROM sandbox \gx
-- min | 123
</code></pre><div class=n-md_h3-box><a href=#集約関数+SUM aria-label=SUMのアンカー class=n-md_h3-anchor></a><h3 id=集約関数+SUM class=n-md_h3-heading>SUM</h3></div><code>SUM</code>は列データすべての合計値を求めます。<pre><code class=language-sql>SELECT SUM(number) FROM sandbox \gx
-- sum | 3921
</code></pre>

集約関数

作成するには<code>CREATE USER</code>を使います。<pre><code class=language-sql>CREATE USER nju33;
</code></pre>パスワードも設定したい場合<code>WITH PASSWORD ...</code>と続けます。<pre><code class=language-sql>CREATE USER nju33 WITH PASSWORD '...';
</code></pre>ユーザー一覧は<code>pg_user</code>というテーブルに入ってます。作られたか確認してみます。<pre><code class=language-sql>SELECT count(*) FROM pg_user WHERE usename = 'nju33' \gx
-- count | 1
</code></pre>カウントが1なので作られたことが分かりました。ユーザーを削除するには<code>DROP USER</code>を使います。上記のユーザーを削除するには以下の通り。<pre><code class=language-sql>DROP USER nju33;

SELECT count(*) FROM pg_user WHERE usename = 'nju33' \gx
-- count | 0
</code></pre>

ユーザーを作成・削除

<div class=n-md_h3-box><a href=#インストール+Mac aria-label=Macのアンカー class=n-md_h3-anchor></a><h3 id=インストール+Mac class=n-md_h3-heading>Mac</h3></div>Homebrew を使って以下で入ります。<pre><code class=language-sh>brew update
brew install postgres</code></pre><h4 id=バージョンを確認>バージョンを確認</h4>バージョンが表示されれば大丈夫です。<pre><code class=language-sh>psql --version
# psql (PostgreSQL) 11.3</code></pre><h4 id=バージョンがでない場合>バージョンがでない場合</h4>サービスが起動しているか確認します。<code>brew services list</code>で<code>postgresql stoped</code>になっていると起動してあげる必要があります。<pre><code class=language-sh># 正常
brew services list
# postgresql started . .</code></pre>起動するには<code>brew services start</code>コマンドを使います。<pre><code class=language-sh>brew services start postgresql</code></pre><div class=n-md_h3-box><a href=#インストール+Ubuntu aria-label=Ubuntuのアンカー class=n-md_h3-anchor></a><h3 id=インストール+Ubuntu class=n-md_h3-heading>Ubuntu</h3></div>環境。<pre><code class=language-sh>cat /etc/lsb-release
# DISTRIB_ID=Ubuntu
# DISTRIB_RELEASE=18.04
# DISTRIB_CODENAME=bionic
# DISTRIB_DESCRIPTION="Ubuntu 18.04.3 LTS"
</code></pre><code>apt</code>を使ってインストールします。<pre><code class=language-sh>sudo apt update
sudo apt install postgresql postgresql-contrib --yes
</code></pre>