Postgresql 1 / 3

接続

関連

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

psql postgres://:@:(/[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 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

参照スキーマを変更する

関連

ちょっと準備として、fooとpublicという２つのスキーマがあり、今は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;

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

また複数カラムを一度に更新したい場合は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;

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

制約

関連

各カラムにはCHECKやUNIQUE、PRIMARY KEY、REFERENCESなどの制約を設定できます。

CHECK

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

以下は、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を置いて設定します。これを設定したカラムの値は全レコード全てで異なる値でなければなれないという制限ができます。

以下は、numberへ1を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

参照テーブルにidが1のレコードを入れたので、先程エラーになったものをもう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という便利なコマンドがあるのでそちらを使ったほうがいいと思います。

接続

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

データベースの作成

データベースの削除