14

I have this UNIQUE constraint:

ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE
("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8");

Then I do:

INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") 
VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true);
INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") 
VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true);

Both work. Two rows are added to the table. The second is logically supposed to fail. But it doesn't.

What am I doing wrong? This is driving me insane.

Note: If this were my own data, I would have a truly unique column and not this "crazy" UNIQUE constraint. The issue is that this table holds the records from my bank account, and they stupidly don't have an actually "unique" column in their CSV dump which I could use to actually make sure that duplicate rows aren't inserted, so I have to come up with one which combines all the columns in the entire table to determine uniqueness.

1
  • As a side note, I wouldn't be at all surprised if the CSV dump you're getting is a denormalized view anyways, which would explain the apparent lack of a unique key. Sep 6, 2021 at 6:53

4 Answers 4

24

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.

0
10

Your problem stems from the NULL values.

From the documentation (emphasis added)

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

3
  • It makes sense. NULL represents an unknown value. Can you say that one unknown value is the same or different from a different unknown value?
    – Zikato
    Sep 7, 2021 at 7:07
  • @Zikato The problem is that NULL is not always "unknown", depending on your use case null can be either "unknown", or "not existing". Lets think about a car park, if car = NULL, then you don't have an unknown car standing on the car park slot, car is not unknown, there is no car on specific slot, a known state. On the other hand if you have an address with state = NULL then your state is unknown, but it still exists, so null = unknown. In fact null represents lack of something more often than it represents something unknown.
    – HubertNNN
    Jun 28, 2022 at 15:25
  • A state is only sometimes applicable, a lot of ignorant septics get this wrong.
    – Jasen
    Sep 11, 2022 at 6:07
2

Here is another solution for when you have multiple nullable columns and Postgres version < 15:

create unique index mytable_uk on mytable(
(array[col1, col2, col3, col4, col5, col6, col7, col8::text]) -- col8 is boolean!
); 

This works because Postgres is able to create unique indexes over arrays, and it uses the memberwise DISTINCT FROM semantics for array comparison, i.e. two arrays are considered the same if their members are the same, with NULL considered equal to NULL. Unlike the COALESCE solution above, this one is "strict", i.e. it distinguishes NULLs from empty strings (if that's important for some reason).

A potential downside of this solution is that the resulting index will be of no use to the optimizer; its only purpose is to enforce uniqueness. Also, I'd do a performance test before implementing this on a large table.

1

In Oracle, whence PostgreSQL inherited this behavior, the standard solution is to use a unique Function-Based Index, so perhaps use a Functional Index that replaces NULL with something else.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.