Unnamed

constraints=# create table sessions (
  loginName varchar(255) not null references users (loginname),
  started_at timestamp not null,
  ended_at timestamp,
  check (ended_at is null or ended_at >= started_at),
  sessionId bigint primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sessions_pkey" for table "sessions"
CREATE TABLE

constraints=# create unique index one_active_session on sessions (loginname) where ended_at is null;
CREATE INDEX

constraints=# insert into sessions (loginname, started_at, sessionid) values ('bob', now(), 1);
INSERT 0 1

constraints=# select * from sessions;
 loginname |         started_at         | ended_at | sessionid 
-----------+----------------------------+----------+-----------
 bob       | 2007-07-30 20:54:53.084682 |          |         1
(1 row)

constraints=# insert into sessions (loginname, started_at, sessionid) values ('bob', now(), 2);
ERROR:  duplicate key violates unique constraint "one_active_session"

constraints=# select * from sessions;
 loginname |         started_at         | ended_at | sessionid 
-----------+----------------------------+----------+-----------
 bob       | 2007-07-30 20:54:53.084682 |          |         1
(1 row)

constraints=# update sessions set ended_at = now ()where sessionid=1;
UPDATE 1
constraints=# select * from sessions;
 loginname |         started_at         |          ended_at          | sessionid 
-----------+----------------------------+----------------------------+-----------
 bob       | 2007-07-30 20:54:53.084682 | 2007-07-30 21:02:41.031571 |         1
(1 row)

constraints=# insert into sessions (loginname, started_at, sessionid) values ('bob', now(), 2);
INSERT 0 1
constraints=# select * from sessions; loginname |         started_at         |          ended_at          | sessionid 
-----------+----------------------------+----------------------------+-----------
 bob       | 2007-07-30 20:54:53.084682 | 2007-07-30 21:02:41.031571 |         1
 bob       | 2007-07-30 21:02:45.952053 |                            |         2
(2 rows)