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);
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
CREATE UNIQUE INDEX bank_uni_idx ON bank
(col1, col2, COALESCE(col3, ''), col4, col5, col6, COALESCE(col7, ''), col8);
col7 are nullable string type columns, where the empty string (
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:
UNIQUE index or constraint based on a cheap and sufficiently unique hash value of the row (reduced to defining columns).
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
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:
CREATE OR REPLACE FUNCTION public.f_bank_bighash(col1 text, col2 text, col3 text, col4 text
, col5 text, col6 text, col7 text, col8 text)
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);
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
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
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
uuid with a 16-byte key space (
2^128 distinct values):
SELECT sqrt(2^129 * ln(1/(1 - 0.000001)))::int8 AS p00001 -- 26087642172564964
At around 26 quadrillion rows, the chance for a collision becomes 0.000001.