NULL is the culprit, because two NULL values are considered distinct in a UNIQUE constraint - in accordance with the SQL standard.
Postgres 15 or newer
Postgres 15 adds an option to change this behavior, allowing for a simple solution:
ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE NULLS NOT DISTINCT
(col1, col2, col3, col4, col5, col6, col7, col8);
See:
This works out of the box now.
However, the underlying unique index is big and inefficient for many and/or wide columns. I would still consider an index on a hash value like outlined below.
Alternative solutions (original answer)
Do you need all columns to make rows unique? Typically, combining just a few should suffice. Bank data should have plenty of notnull columns ...
To make it work including a single nullable column, you could use a partial index as outlined here:
But that gets impractical quickly with more than one nullable column.
With multiple nullable columns, a simple solution would be a unique expression index with COALESCE like:
CREATE UNIQUE INDEX bank_uni_idx ON bank
(col1, col2, COALESCE(col3, ''), col4, col5, col6, COALESCE(col7, ''), col8);
That's assuming col3 & col7 are nullable string type columns, where the empty string ('') and NULL are semantically equivalent.
The same can be used for a single nullable column as well, obviously.
You need a safe replacement for NULL that won't conflict with other legal values (empty string in my example).
The downside of all solutions so far (including your original) is the large index on so many columns. Can make it rather expensive. This leads me to the answer I really want to give:
Efficient solution
Create a UNIQUE index or constraint based on a cheap and sufficiently unique hash value of the row (reduced to defining columns).
Postgres 14
comes with a built-in hash function for records (including anonymous records!), which is substantially cheaper than my custom function below.
hash_record_extended(record, bigint) --> bigint
See:
It belongs to the same family of functions as hashtextextended() ( details below). Now, an expression index seems more attractive than a generated column. So just:
CREATE UNIQUE INDEX bank_hash_uni ON bank (hash_record_extended((col1, col2, col3, col4, col5, col6, col7, col8),0));
That's all. Most of the below still applies.
Postgres 13 (original answer)
Store the hash value in a generated column and create a UNIQUE constraint on that. See:
Assuming all text columns.
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.';
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);
db<>fiddle here
Works with NULL values.
Requires Postgres 12 or later, where extended hash functions and generated columns were added.
hashtextextended() as well as hastext() are internal functions used for fast and reliable hashing for hash partition or hash indexes. They are undocumented. But they are not going away.
They may not be stable across different hardware platforms, as Tom Lane points out. Recreate hashes after moving your DB cluster from a little-endian to a big-endian system (if something like that should ever happen.)
The second argument for hashtextextended() is a salt for the hash. Use any bigint constant, just make sure to use the same everywhere. Stick with 0, unless you know better.
Also, while hash collisions are extremely unlikely with the vast bigint key space, the theoretical possibility is always there. If that happens, you get a unique violation for two distinct rows. If uncomfortable with that, use md5() instead, and store uuid values. See:
16 bytes for uuid instead of 8 bytes for bigint. A bit more expensive to compute, store, and compare. Collisions are still theoretically possible, but you'd have to be paranoid.
Older (or any) versions could do the same with hashtext() returning integer. Makes collisions much more likely. Still very unlikely up to a couple of thousand entries.
And use triggers to keep the hash column up to date, or a unique index on the expression instead of a constraint on the generated column.
Probability of a hash collision?
TL;DR: pretty damn safe up to a couple million rows.
You can compute the actual probability with math formulas for the "birthday problem". Assuming a perfect hash function, the numbers for a bigint hash (2^64 - 1, rounded to 2^64 distinct values) are:
SELECT sqrt(2^65 * ln(1/(1 - 0.1)))::int AS p10 -- 1971577271
, sqrt(2^65 * ln(1/(1 - 0.01)))::int AS p1 -- 608926881
, sqrt(2^65 * ln(1/(1 - 0.001)))::int AS p01 -- 192124822
, sqrt(2^65 * ln(1/(1 - 0.0001)))::int AS p001 -- 60741529
, sqrt(2^65 * ln(1/(1 - 0.00001)))::int AS p0001 -- 19207726
, sqrt(2^65 * ln(1/(1 - 0.000001)))::int AS p00001 -- 6074003
Reading the last calculation for p00001:
With around 6 million entries, the probability for at least a single hash collision is below 0.000001 (= 0.0001 %).
IOW, when applied to one million tables with 6M rows each, we can expect a single one to encounter a hash collision.
With around 600 million entries (p1), the probability for at least a single hash collision is 0.01.
The calculation for md5() / uuid with a 16-byte key space (2^128 distinct values):
SELECT sqrt(2^129 * ln(1/(1 - 0.000001)))::int8 AS p00001 -- 26087642172564964
Read:
At around 26 quadrillion rows, the chance for a collision becomes 0.000001.
