Postgres 13

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

-- your original table
CREATE TABLE bank(col1 text, col2 text, col3 text, col4 text
                , col5 text, col6 text, col7 text, col8 text);

CREATE TABLE


-- my additions
CREATE OR REPLACE FUNCTION public.f_bank_bighash(col1 text, col2 text, col3 text, col4 text
                                               , col5 text, col6 text, col7 text, col8 text)
  RETURNS bigint 
  LANGUAGE sql IMMUTABLE COST 25 PARALLEL SAFE AS 
'SELECT hashtextextended(textin(record_out(($1,$2,$3,$4,$5,$6,$7,$8))), 0)';

COMMENT ON FUNCTION public.f_bank_bighash(text, text, text, text, text, text, text, text)
IS 'Fast, practically unique signature for the set of defining columns in table bank.
IMMUTABLE for use in index. "record_out"() is only stable, but with only text input it is effectively immutable
See also f_bank_bighash(bank) taking the row type';


ALTER TABLE bank
  ADD COLUMN bank_bighash bigint NOT NULL GENERATED ALWAYS AS (public.f_bank_bighash(col1, col2, col3, col4, col5, col6, col7, col8)) STORED  -- appends column in last position
, ADD CONSTRAINT bank_bighash_uni UNIQUE (bank_bighash);

CREATE FUNCTION



COMMENT



ALTER TABLE


-- once ok
INSERT INTO bank(col1, col2, col3, col4, col5, col6, col7, col8) VALUES ('1', '2', '3', '4', '5', '6', '7', '8');

INSERT 0 1


-- twice nope
INSERT INTO bank(col1, col2, col3, col4, col5, col6, col7, col8) VALUES ('1', '2', '3', '4', '5', '6', '7', '8');

ERROR:  duplicate key value violates unique constraint "bank_bighash_uni"
DETAIL:  Key (bank_bighash)=(2937395560705972887) already exists.


-- this works with NULL, too:
INSERT INTO bank(col1, col2, col3, col4, col5, col6, col7, col8) VALUES ('1', '2', NULL, '4', '5', NULL, '7', '8');

INSERT 0 1


INSERT INTO bank(col1, col2, col3, col4, col5, col6, col7, col8) VALUES ('1', '2', NULL, '4', '5', NULL, '7', '8');

ERROR:  duplicate key value violates unique constraint "bank_bighash_uni"
DETAIL:  Key (bank_bighash)=(-1715083720741530461) already exists.


-- Silently drop dupes
INSERT INTO bank(col1, col2, col3, col4, col5, col6, col7, col8) VALUES
  ('1', '2', '3', '4', '5', '6', '7', '8')
, ('1', '2', NULL, '4', '5', NULL, '7', '8')
ON CONFLICT (bank_bighash) DO NOTHING;

INSERT 0 0


TABLE bank;


col1
col2
col3
col4
col5
col6
col7
col8
bank_bighash


1
2
3
4
5
6
7
8
2937395560705972887

1
2
null
4
5
null
7
8
-1715083720741530461



SELECT 2

col1	col2	col3	col4	col5	col6	col7	col8	bank_bighash
1	2	3	4	5	6	7	8	2937395560705972887
1	2	null	4	5	null	7	8	-1715083720741530461