CREATE TABLE tx.t1 (
id numeric NOT NULL,
"name" varchar NOT NULL,
"desc" varchar NOT NULL,
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE TABLE tx.t2 (
"inout" serial4 NOT NULL,
id numeric NULL,
out_id timestamp NOT NULL DEFAULT LOCALTIMESTAMP,
in_id timestamp NULL,
CONSTRAINT t2_check CHECK ((in_id > out_id)),
CONSTRAINT t2_pkey PRIMARY KEY ("inout"),
CONSTRAINT t2_id_fkey FOREIGN KEY (id) REFERENCES tx.t1(id)
);
CREATE UNIQUE INDEX unique_out ON tx.t2 USING btree (id, ((in_id IS NULL))) WHERE (in_id IS NULL);
select t1.id , name , descr , (case WHEN t2.out_id IS NULL then 'В наличии' else 'Нет в наличии' END) as exist from tx.t1 left JOIN tx.t2 on t1.id = t2.id order by 1;