接続

postgresスキーマを使うと以下の形で接続できます。password(未設定時)やdbnameは省略できます。

psql postgres://<username>:<password>@<host>:<port>(/[dbname])?

例えばこんな感じです。

psql postgresql://postgres:@0.0.0.0:54321
# dbname 有り
psql postgresql://postgres:@0.0.0.0:54321/foo

またそれぞれの項目はusernameなら--username Usernameのようなオプションとして渡せます。

psql \
  --username ... \
  --password ... \
  --host ... \
  --post ... \
  --dbname ...

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

データベースの作成

create database データベース名を使います。

postgres=# create database foo;
CREATE DATABASE

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 foo       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

またデータベースを一覧するには\lコマンドを使います。fooデータベースができていることが確認できました。

データベースの削除

drop database データーベース名を使います。

postgres=# drop database foo;
DROP DATABASE

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

消せました。

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

作る場合にはcreate schemaを使います。以下はfoo名前空間を作る例です。

postgres=# create schema foo;
CREATE SCHEMA

作れたかどうかは\dnと叩きます。

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

fooがありました。

「もう要らない」「削除したい」場合はdrop schemaで削除できます。if existsを付けると対象の名前空間がない場合は処理がスキップされエラー(ERROR: schema "foo" does not exist)を回避できます。また、中にテーブルなどが存在する場合最後にcascadeと付けることでそれごと削除できます。

postgres=# drop schema if exists foo;
DROP SCHEMA

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

テーブルを作成する

create table テーブル名を実行します。

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

ちゃんと作成されたか\dtで確認します。

postgres=# \dt
         List of relations
 Schema | Name | Type  |  Owner
--------+-------+-------+----------
 public | foo  | table | postgres
(1 row)

テーブルスキーマを確認する

\d <table_name>を実行します。例えば\d fooとすると、

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)

のように見ることができます。

テーブルを削除する

drop table テーブル名を実行します。上記でfooテーブルを作ったのでdrop table foo;ですね。

postgres=# drop table foo;
DROP TABLE

ちなみにここでもう一度fooテーブルを削除しようとすると、以下のようにエラーになります。

postgres=# drop table foo;
ERROR:  table "foo" does not exist

これはtableの後にif exists(存在する時)を付けることで回避できます。

postgres=# drop table if exists foo;
NOTICE:  table "foo" does not exist, skipping
DROP TABLE

同じようにcreate tableも既にある場合はエラーになってしまいますが、こちらの場合はif not exists(存在しない時)を付けてあげると回避できます。

create table if not exists foo (id text);
NOTICE:  relation "foo" already exists, skipping
CREATE TABLE

現在の接続状況を調べる

\conninfoコマンドを使います。以下はその例です。

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "0.0.0.0" at port "4288".

上記は、以下のdockerで建てた Postgresql 環境です。

docker run --name db --rm -p 4288:5432 -e POSTGRES_PASSWORD=postgres postgres:latest 

参照スキーマを変更する

ちょっと準備として、foopublicという2つのスキーマがあり、今はpublicを選択したいる状態というにします。

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)

ここでfooを選択するにはset search_path to <スキーマ名>を実行するだけです。(to=でもいけます)

postgres=# set search_path = foo;
SET

postgres=# select current_schema;
 current_schema
----------------
 foo
(1 row)

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

挿入 (insert)

INSERT INTO文を使い以下のような形でデータを挿入できます。括弧の中のカラム名の並びとそのデータとなる値は同じ位置に置く必要があります。

INSERT INTO table_name (column1, column2, ...) VALUEAS (value1, value2, ...);

values以下は,でつなげて書くことにより一度に複数レコードを挿入することができます。

INSERT INTO table_name (column1, column2, ...) VALUEAS
  (value1, value2, ...),
  (value1, value2, ...),
  (value1, value2, ...);

values に渡す値がない場合

すべてデフォルト値で入れたい項目しかない場合、VALUES (...)ではなくDEFAULT VALUESが使えます。

INSERT INTO table_name DEFAULT VALUES

更新 (update)

UPDATE文を使い以下のような形でデータを挿入できます。

UPDATE table_name set foo = 'updated foo';

上記ではすべてのレコードのfooカラムの値をupdated fooに更新してしまいます。更新データを絞り込むにはwhereを使うことができます。

UPDATE table_name SET foo = 'updated foo' WHERE id = 1;

これでid1のものだけを更新することができます。

また複数カラムを一度に更新したい場合はset以下の値の代入部分を,で繋げることで可能です。

UPDATE table_name
  SET foo = 'updated foo', bar = 'updated bar'
  WHERE id = 1;

削除 (delete)

DELETE FROM文を使い以下のような形でデータを削除できます。

DELETE FROM table_name;

これもUPDATEのようにそのままだと、そのテーブルのレコードすべてが対象になってしまうのでWHEREで絞ることができます。

DELETE FROM table_name WHERE id = 1;

これでid1のものだけを削除することができます。

制約

各カラムにはCHECKUNIQUEPRIMARY KEYREFERENCESなどの制約を設定できます。

CHECK

CHECK (condition)のように書け、conditiontrueなデータだけ登録するように制限できます。

以下は、numberへは1以上の値しか入れれないという制限を設定してます。もしその条件を満たさないものを入れようとするとエラーが起こります。

CREATE TABLE example_check (
 number INTEGER CHECK ( number > 0 )
);
-- CREATE TABLE

INSERT INTO example_check (number) 
  VALUES ( -1 );
-- ERROR:  new row for relation "example_check" violates check constraint "example_check_number_check"

エラー文にある通りこの制約名はデフォルトで<table_name>_<column_name>_checkです。

UNIQUE

単にUNIEUQを置いて設定します。これを設定したカラムの値は全レコード全てで異なる値でなければなれないという制限ができます。

以下は、number1を2回入れようとしたためエラーが起きています。

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"

エラー文にある通りこの制約名はデフォルトで<table_name>_<column_name>_keyです。

PRIMARY KEY

単にUNIEUQを置いて設定します。そのテーブルでの主キーを設定できます。こちらもユニークと同じようにユニークである必要があります。

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"

エラー文にある通りこの制約名はデフォルトで<table_name>_pkeyです。

REFERENCES

REFERENCES table(column, ...)のように書き、外部テーブルとの関連した値を安全に記録する為に使います。制約名はデフォルトで<table_name>_<column_name>_fkeyです。

例を見る前に以下で、外部テーブルとして使うテーブルを作っておきます。

CREATE TABLE example_references_parent (
  id SERIAL PRIMARY KEY
);

それを使うテーブルを以下のように作ります。

CREATE TABLE example_references1 (
  parent_id INTEGER REFERENCES example_references_parent(id)
);

まだexample_references_parentにはレコードは1つもありません。その状態でexample_references1へ適当なparent_id指定で入れようとしても、

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"

エラーになります。これはparent_idへ入れられる値は、example_references_parentに実在するあるid値である必要がある為です。

このエラーは先にそのような値を入れておくことで回避できます。

INSERT INTO example_references_parent DEFAULT VALUES;
-- INSERT 0 1

参照テーブルにid1のレコードを入れたので、先程エラーになったものをもう1度試してみます。

INSERT INTO
  example_references1 (parent_id) 
VALUES
  ( 1 );
-- INSERT 0 1

今度は大丈夫でした!

そしてこの状態で参照側のレコードを消してみます。

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"

が、この制約がちゃんと付いてると参照されているレコードは上記のようなエラーがでて消すことができなくなります。これを回避するには先に参照しているレコードを消す必要があります。

DELETE FROM example_references1;
-- DELETE 1

DELETE FROM example_references_parent;
-- DELETE 1

もし、参照してる値がとても多い場合これでは不便かもしれません。上記の削除の動作を1度に行える方法があります。それは制約を設定する時にON UPDATE CASCADE ON DELETE CASCADEを付けます。

CREATE TABLE example_references2 (
  parent_id INTEGER REFERENCES example_references_parent(id) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE
);
-- CREATE TABLE

先程消してしまった参照テーブルと今作ったテーブルに値を入れておきます。

INSERT INTO example_references_parent DEFAULT VALUES;
-- `id: 2`のレコードが入る

INSERT INTO
  example_references2 (parent_id) 
VALUES
  ( 2 );
-- INSERT 0 1

この状態だと参照元データを削除できます。

DELETE FROM example_references_parent;
-- DELETE 1

削除してみます。

SELECT * FROM example_references2 \gx
-- (0 rows)

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

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

これはinsertでレコードを追加した後にselect ... limit 1をせずに一度に終わらせるものです。

確認用の準備

以下のようにfooテーブルを作っておきます。

postgres=# create table foo (id serial primary key not null, text text not null);
CREATE TABLE

実際に確認

まず、普通にinsertした時はこうなります。「1件追加しました」という情報だけ返っています。

postgres=# insert into foo (text) values ('hoge');
INSERT 0 1

returning を使う

ですが、後ろにreturning *を追加すると今入れたレコードが返るようになります。

postgres=# insert into foo (text) values ('fuga') returning *;
 id | text
----+------
  2 | fuga
(1 row)

INSERT 0 1

*select ...のカラム指定と同じように指定できます。したがって、カラム名を指定すればそれだけを返すようにできます。

postgres=# insert into foo (text) values ('piyo') returning text;
 text
------
 piyo
(1 row)

INSERT 0 1

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

\!の後ろにシェルコマンドを書いて実行します。こんな感じです。

\! cat foo.txt

vimなどもちゃんとエディターが開いてくれます。

ただ.sqlファイルの実行は\iという便利なコマンドがあるのでそちらを使ったほうがいいと思います。

コマンドラインから変数を使う 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 文を2秒毎に実行してくれます。

watchコマンドを確認する

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

  1. テーブルに3秒毎にinsertする
  2. テーブルの最新1件を2秒毎に表示する

準備

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

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

1番目の方では以下でinsertを走らせます。\watch 3で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を使います。例えば以下でtablenamepostsという値を設定しています。

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テーブルからvaluenju33なレコード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

集約関数

集約関数を使うと、結果のある列のデータから演算を行い単一の結果を受け取ることができます。集約関数には、

  1. AVG
  2. COUNT
  3. MAX
  4. MIN
  5. SUM

などあります。

以下のようなデータが入ってるsandboxデータベースで動作を見ていきます。

 id | number
----+--------
  1 |    123
  2 |    343
  3 |    534
  4 |    513
  5 |    643
  6 |    141
  7 |    643
  8 |    981

AVG

AVGは列データの平均値を求めます。

SELECT AVG(number) FROM sandbox \gx
-- avg | 490.1250000000000000

COUNT

COUNTは列データの個数を求めます。

SELECT count(*) FROM sandbox \gx
SELECT count(number) FROM sandbox \gx
-- count | 8

MAX, MIN

MAXは列データの中での最大値、MINは最小値を探します。

SELECT MAX(number) FROM sandbox \gx
-- max | 981

SELECT MIN(number) FROM sandbox \gx
-- min | 123

SUM

SUMは列データすべての合計値を求めます。

SELECT SUM(number) FROM sandbox \gx
-- sum | 3921

ユーザーを作成・削除

作成するにはCREATE USERを使います。

CREATE USER nju33;

パスワードも設定したい場合WITH PASSWORD ...と続けます。

CREATE USER nju33 WITH PASSWORD '...';

ユーザー一覧はpg_userというテーブルに入ってます。作られたか確認してみます。

SELECT count(*) FROM pg_user WHERE usename = 'nju33' \gx
-- count | 1

カウントが1なので作られたことが分かりました。

ユーザーを削除するにはDROP USERを使います。上記のユーザーを削除するには以下の通り。

DROP USER nju33;

SELECT count(*) FROM pg_user WHERE usename = 'nju33' \gx
-- count | 0

インストール

Mac

Homebrew を使って以下で入ります。

brew update
brew install postgres

バージョンを確認

バージョンが表示されれば大丈夫です。

psql --version
# psql (PostgreSQL) 11.3

バージョンがでない場合

サービスが起動しているか確認します。brew services listpostgresql stopedになっていると起動してあげる必要があります。

# 正常
brew services list
# postgresql started . .

起動するにはbrew services startコマンドを使います。

brew services start postgresql

Ubuntu

環境。

cat /etc/lsb-release
# DISTRIB_ID=Ubuntu
# DISTRIB_RELEASE=18.04
# DISTRIB_CODENAME=bionic
# DISTRIB_DESCRIPTION="Ubuntu 18.04.3 LTS"

aptを使ってインストールします。

sudo apt update
sudo apt install postgresql postgresql-contrib --yes