Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE OR REPLACE FUNCTION public.f_uniq_hash2(col1 text, col2 text)
  RETURNS uuid
  LANGUAGE sql IMMUTABLE COST 20 PARALLEL SAFE AS 
'SELECT md5(textin(record_out(($1,$2))))::uuid';

CREATE TABLE data (
  data_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, col1 text
, col2 text
, uniq_hash uuid GENERATED ALWAYS AS (public.f_uniq_hash2(col1, col2)) STORED
, CONSTRAINT data_bighash_uni UNIQUE (uniq_hash)
);

INSERT INTO data (col1, col2) VALUES
  ('foo', 'bar')
, ('foo', 'baz')
, ('baz', NULL)
;

CREATE FUNCTION



CREATE TABLE



INSERT 0 3


CREATE TABLE day_data (
  day text 
, data_id bigint REFERENCES data ON UPDATE CASCADE -- FK to enforce referential integrity
, PRIMARY KEY (day, data_id)      -- must be unique?
);

INSERT INTO day_data (day, data_id) VALUES
  ('Mon', 1)
, ('Tue', 1)
, ('Wed', 2)
;

CREATE TABLE



INSERT 0 3


TABLE data;
TABLE day_data;


data_id
col1
col2
uniq_hash


1
foo
bar
7ea22296-b1b2-1630-e534-8536e8372e6f

2
foo
baz
f0973580-535c-81b3-691c-3cec29806457

3
baz
null
c32b26c3-7d9d-86bf-cbb7-ca9e3c2fecc9



SELECT 3




day
data_id


Mon
1

Tue
1

Wed
2



SELECT 3


-- Can't insert duplicate:
INSERT INTO data (col1, col2) VALUES  ('baz', NULL);

ERROR:  duplicate key value violates unique constraint "data_bighash_uni"
DETAIL:  Key (uniq_hash)=(c32b26c3-7d9d-86bf-cbb7-ca9e3c2fecc9) already exists.


-- join for query:
SELECT *
FROM   day_data dd
JOIN   data d USING (data_id);


data_id
day
col1
col2
uniq_hash


1
Mon
foo
bar
7ea22296-b1b2-1630-e534-8536e8372e6f

1
Tue
foo
bar
7ea22296-b1b2-1630-e534-8536e8372e6f

2
Wed
foo
baz
f0973580-535c-81b3-691c-3cec29806457



SELECT 3

data_id	col1	col2	uniq_hash
1	foo	bar	7ea22296-b1b2-1630-e534-8536e8372e6f
2	foo	baz	f0973580-535c-81b3-691c-3cec29806457
3	baz	null	c32b26c3-7d9d-86bf-cbb7-ca9e3c2fecc9