◆ ユニーク制約を作るとき CREATE TABLE 文の外で CREATE UNIQUE INDEX を使えば WHERE 句を書ける
◆ boolean 型の列で false はいくつあってもいいけど true は 1 つだけみたいな制約を作れる

RDB に保存するデータで過去の状態も残しておきたいことがあります
例えばユーザーのステータスを保存するテーブルがあります

 id | username | status
----+----------+---------
1 | user1 | status1
2 | user2 | status1

テーブルはあまり増やしたくないので タイムスタンプ付きで保存します

 id | username |  status  |         ts          
----+----------+----------+---------------------
1 | user1 | status1 | 2022-07-20 21:10:01
2 | user1 | status2 | 2022-07-22 22:22:10
3 | user2 | status2 | 2022-07-24 12:41:15
4 | user1 | status1 | 2022-07-26 08:08:30

ただこれだと参照するときに対象のユーザーの行の中で ts 列が一番新しいのを探す必要があります
いかにも遅くなりそうですし 各ユーザーの最新情報をまとめて取得というときにはグループごとに最新の行を探す必要があって複雑になります
最新を示す列を追加することにします

 id | username |  status  |         ts          | current
----+----------+----------+---------------------+---------
1 | user1 | status1 | 2022-07-20 21:10:01 | false
2 | user1 | status2 | 2022-07-22 22:22:10 | false
3 | user2 | status2 | 2022-07-24 12:41:15 | true
4 | user1 | status1 | 2022-07-26 08:08:30 | true

テーブルを分けないならこんなものかなと思うものの気になるところもあります
current 列で同じユーザーには true が一つだけって保証がないので登録処理のミスなどで複数が true になったりしそうです
ユニーク制約をつけておこうかなと思います (enum 定義が面倒なので status 列は略)

create table user_status (
id integer primary key generated always as identity,
username text not null,
ts timestamptz not null,
current boolean not null,
unique (username, current)
);

しかしこれだと username と current のペアがユニークなので 同じユーザーの過去のレコードが 2 つ以上保存できません
null はユニーク制約の影響を受けないからと null を許可して true または null のみ使って false は使わないようにしました

create table user_status2 (
id integer primary key generated always as identity,
username text not null,
ts timestamptz not null,
current boolean,
unique (username, current)
);

これでも目的は達成できていたわけですが true または null ってなんか気持ち悪いです
true の場合のみユニークにできないのかなと調べていると create index で作ると where が指定できるみたいです

create table user_status3 (
id integer primary key generated always as identity,
username text not null,
ts timestamptz not null,
current boolean
);

create unique index on user_status3 (username, current) where current = true;

データを入れてみます

postgres=# insert into user_status3 (username, ts, current) values ('user1', now(), false);
INSERT 0 1
postgres=#
postgres=# insert into user_status3 (username, ts, current) values ('user1', now(), false);
INSERT 0 1

false なら同じユーザーに複数行を登録できてます

postgres=# insert into user_status3 (username, ts, current) values ('user1', now(), true);
INSERT 0 1
postgres=# insert into user_status3 (username, ts, current) values ('user1', now(), true);
ERROR: duplicate key value violates unique constraint "user_status3_username_current_idx"
DETAIL: Key (username, current)=(user1, t) already exists.

true だと 2 つめで重複エラーが出てます

ユーザーとの組み合わせなので 別ユーザーなら 1 件目は登録できて 2 件目でエラーです

postgres=# insert into user_status3 (username, ts, current) values ('user2', now(), true);
INSERT 0 1
postgres=# insert into user_status3 (username, ts, current) values ('user2', now(), true);
ERROR: duplicate key value violates unique constraint "user_status3_username_current_idx"
DETAIL: Key (username, current)=(user2, t) already exists.

普段はユニーク制約は create table の中に書いてるので知らなかったですが 別の create index 文を使えば where で行を制限できるのですね

できればユーザーごとに最低 1 件は true が存在するという制約もつけたいですがこれは見た感じなさそうでした