PostgreSQLのSQLと基本操作 | PostgreSQL入門

テーブルを作る

商品一件を一行で表すテーブルを作ります。GENERATED BY DEFAULT AS IDENTITYは、番号を自動生成しつつ必要なら明示値も受け付けます。

商品テーブルSQL

CREATE TABLE products (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    category text,
    price numeric(10, 2) NOT NULL CHECK (price >= 0),
    stock integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (name, category, price, stock) VALUES
    ('PostgreSQL入門書', 'book', 3280.00, 5),
    ('ノート', 'stationery', 300.00, 12),
    ('ボールペン', 'stationery', 150.00, 0),
    ('マグカップ', NULL, 900.00, 3);

timestamptzはタイムゾーンを考慮して時刻を扱う型です。日時は業務要件とタイムゾーンを決めてから選択します。

PostgreSQLの型と制約

integer、numeric、text、date、timestamptzなど、値の意味に合う型を選びます。制約はアプリだけに任せず、DBにもデータルールを持たせる仕組みです。

追加結果を確認するSQL

SELECT id, name, category, price, stock
FROM products
ORDER BY id;

実行結果OUTPUT

id  name              category    price    stock
1   PostgreSQL入門書  book        3280.00  5
2   ノート            stationery  300.00   12
3   ボールペン        stationery  150.00   0
4   マグカップ        NULL        900.00   3

WHEREとORDER BY

価格と在庫で絞るSQL

SELECT name, price, stock
FROM products
WHERE stock > 0 AND price BETWEEN 300 AND 3500
ORDER BY price DESC
LIMIT 2;

実行結果OUTPUT

name              price    stock
PostgreSQL入門書  3280.00  5
マグカップ        900.00   3

条件を満たす行が3件あってもLIMIT 2なら2件です。並べ替えの基準と件数制限は、結果を確認してから組み合わせましょう。

INSERT、UPDATE、DELETE

変更前に同じ条件でSELECTし、対象行を確認します。PostgreSQLのRETURNINGを使うと、追加・更新・削除した行をその場で確認できます。

追加と更新SQL

INSERT INTO products (name, price, stock)
VALUES ('付せん', 250.00, 8)
RETURNING id, name, price, stock;

UPDATE products
SET stock = stock + 2
WHERE id = 2
RETURNING id, name, stock;

DELETE FROM products
WHERE name = '付せん'
RETURNING id, name;

実行結果の例OUTPUT

id  name    price   stock
5   付せん  250.00  8

id  name  stock
2   ノート 14

id  name
5   付せん

次のページの前提を戻すSQL

UPDATE products SET stock = stock - 2 WHERE id = 2;

次のページは初期状態（ノート12個）から説明します。上の戻すSQLを実行してから進みましょう。

NULLを扱う

NULLは0や空文字ではありません。= NULLではなくIS NULLで調べ、表示用の代替値にはCOALESCEを使います。

未分類として表示SQL

SELECT name, COALESCE(category, '未分類') AS category_name
FROM products
WHERE category IS NULL;

実行結果OUTPUT

name        category_name
マグカップ  未分類

練習：在庫金額

在庫がある商品について、price * stockをstock_valueとして表示し、金額の大きい順に並べてください。

解答例と実行結果

解答例SQL

SELECT name, price * stock AS stock_value
FROM products
WHERE stock > 0
ORDER BY stock_value DESC;

実行結果OUTPUT

name              stock_value
PostgreSQL入門書  16400.00
ノート            3600.00
マグカップ        2700.00

PostgreSQL入門の講座一覧

テーブルを作る

PostgreSQLの型と制約

WHEREとORDER BY

INSERT、UPDATE、DELETE

NULLを扱う

練習：在庫金額