はじめに
SQLを触る機会が業務的に多いのですが、いざ書く際に構文がわからなくなることが多く毎回調べるのが地味に工数かかるなと思う日々。
忘れがちな構文やハマりどころを、自分用のメモも兼ねてまとめておこうと思います。
第一弾としてテーブルの更新周りとSELECT文周りから。
他の方の参考になれば嬉しいです。
テーブル編集に使うSQLまとめ
テーブル作成時の存在確認
postgres=# CREATE TABLE IF NOT EXISTS table1 (
id SERIAL,
value VARCHAR(64)
);
CREATE TABLE
テーブル削除時の存在確認
postgres=# DROP TABLE IF EXISTS table1;
DROP TABLE
列の追加(NOTNULL制約あり)
postgres=# ALTER TABLE table1 ADD COLUMN hoge VARCHAR(32) NOT NULL DEFAULT '';
ALTER TABLE
列にNOT NULL制約を付与
既にレコードがある場合にデフォルトをつけないとエラーになるので注意
postgres=# ALTER TABLE table1 ADD COLUMN hoge VARCHAR(32) NOT NULL DEFAULT '';
ALTER TABLE
列にNOT NULL制約を削除
既にレコードがある場合にデフォルトをつけないとエラーになるので注意
postgres=# ALTER TABLE table1 ALTER COLUMN hoge DROP NOT NULL;
ALTER TABLE
列の削除
postgres=# ALTER TABLE table1 DROP COLUMN hoge;
ALTER TABLE
ENUM型の作成
ENUM型はNULL許容
postgres=#CREATE TYPE type1 AS ENUM ('0', '1');
CREATE TYPE
ENUM型に値追加
AFTER/BEFOREは任意
postgres=# ALTER TYPE type1 ADD VALUE '2' AFTER '1';
ALTER TYPE
postgres=# ALTER TYPE type1 ADD VALUE '3' AFTER '2';
ALTER TYPE
ENUM型に値削除(再作成)
postgresqlはENUMの削除ができないため、再度ENUMを作成し、レコードを移し替える必要がある
postgres=# ALTER TYPE type1 RENAME TO type1_old;
ALTER TYPE
postgres=# CREATE TYPE type1 AS ENUM ('0', '1');
CREATE TYPE
postgres=# ALTER TABLE table1 ALTER COLUMN enum_type TYPE type1 USING enum_type::text::type1;
ALTER TABLE
postgres=#
postgres=# DROP TYPE type1_old;
DROP TYPE
制約追加
postgres=# ALTER TABLE table1 ADD CONSTRAINT check1 CHECK (id IS NOT NULL);
ALTER TABLE
制約削除
postgres=# ALTER TABLE table1 DROP CONSTRAINT check1;
ALTER TABLE
関数およびトリガー作成
postgres=# CREATE OR REPLACE FUNCTION table_ref() RETURNS TRIGGER AS $$
postgres$# BEGIN
postgres$# IF EXISTS (
postgres$# SELECT
postgres$# 1
postgres$# FROM
postgres$# table1
postgres$# WHERE
postgres$# id = NEW.id
postgres$# ) THEN
postgres$# RAISE EXCEPTION 'ERROR';
postgres$# END IF;
postgres$# RETURN NEW;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# CREATE TRIGGER table_trigger
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW EXECUTE PROCEDURE table_ref();
CREATE TRIGGER
トリガー削除
postgres=# DROP TRIGGER table_trigger ON table1;
DROP TRIGGER
postgres=#
ユニークインデックスの作成(条件付き)
postgres=# CREATE UNIQUE INDEX ix_table1 ON table1 USING btree (id) WHERE (enum_type IS NULL);
CREATE INDEX
インデックスの削除
postgres=# DROP INDEX ix_table1;
DROP INDEX
SELECTする際に使うSQLまとめ
WITH句
postgres=# WITH table1_data AS (
postgres(# SELECT * FROM table1
postgres(# ),
postgres-# table2_date AS (
postgres(# SELECT * FROM table2
postgres(# )
postgres-# SELECT *
postgres-# FROM table1_data
postgres-# LEFT OUTER JOIN table2_date ON table1_data.id = table2_date.table1_id
postgres-#
postgres-# ;
id | value | hoge | enum_type | table1_id | value
----+-------+------+-----------+-----------+-------
1 | val1 | | 1 | |
(1 row)
文字列の連結
postgres=# SELECT 'a' || id || 'b' FROM table1;
?column?
----------
a1b
(1 row)
主キー毎のレコード数
postgres=# SELECT id, COUNT(*) AS count
postgres-# FROM table1
postgres-# GROUP BY id
postgres-# HAVING COUNT(*) >= 1;
id | count
----+-------
1 | 1
(1 row)
日付を出力
postgres=# SELECT DATE(current_timestamp);
date
------------
2025-08-21
(1 row)
時間部分を消す
postgres=# SELECT DATE_TRUNC('day', current_timestamp);
date_trunc
------------------------
2025-08-21 00:00:00+00
(1 row)
記載順序
SELECT句 > FROM句 > WHERE句 > GROUP BY句 > HAVING句 > ORDER BY句
注意
- SELECT COUNT(*)は行数を正確に、COUNT(column)はNULLを除外した行数を返す
-
TO_CHAR(created_at, 'YYYY-MM-DD')などでGROUP BYするとインデックスが無効になる - BETWEENは両端を含む時間指定
終わりに
一旦今回はここまでとします。
他SLECT系で使用するもので思い出したものがあれば別記事にてまとめます。(ウィンドウ関数は詳しく1記事で扱う予定)
Discussion