Postgres uniqueness constraint in is not working

Question

I have been created a new table and I am inserting data from my no-sql database.

The weird thing is I have a Uniqueness Constraint setup for it.

ALTER TABLE public.tablecolumns
    ADD CONSTRAINT common_col UNIQUE (name, data_type, col_type, repeated, visibility, is_public, fieldowner);

Liquibase script

<addUniqueConstraint
    columnNames="name, data_type, col_type, repeated, visibility, is_public, fieldowner"
    constraintName="common_col"
    tableName="tablecolumns"
    />

But when I subsequently add the same columns, all get populated without an issue. Can you tell me why is this happening?

What exactly do you mean with "add the same columns"? Are you adding column to the table? Or to the unique constraint? — , Jul 15 '20 at 14:52
I meant adding the same `rows` in the table named TableColumnns. — Shaurya Chaudhuri, Jul 15 '20 at 15:25

score 0 · Accepted Answer · answered Jul 15 '20 at 15:00

0

At least one of the values you inserted must have been a NULL value.

When it comes to unique constraints, the SQL standard decrees that NULL values are not to be considered equal:

CREATE TABLE uniq (a integer, b integer, UNIQUE (a, b));

INSERT INTO uniq VALUES (1, 2);

INSERT INTO uniq VALUES (1, 2);
ERROR:  duplicate key value violates unique constraint "uniq_a_b_key"
DETAIL:  Key (a, b)=(1, 2) already exists.

INSERT INTO uniq VALUES (1, NULL);

INSERT INTO uniq VALUES (1, NULL);  -- works without error

Perhaps you can use this question and its answer to get what you want.

answered Jul 15 '20 at 15:00

Laurenz Albe

209,280
17
206
263

I added this into my liquibase script. The Unique Indexes seemed to be have created but the rows still get populated. ``` CREATE UNIQUE INDEX on tablecolumns(name, data_type, col_type, repeated, is_public, fieldowner) WHERE visibility=NULL ``` – Shaurya Chaudhuri Jul 15 '20 at 18:12
1

That cannot work: `anything = NULL` can never be `TRUE`. – Laurenz Albe Jul 15 '20 at 18:49
Okay, I understand now. Let me check with `IS NULL` . That should work, right? – Shaurya Chaudhuri Jul 15 '20 at 20:34
If that's the only nullable column, it may be what you need. – Laurenz Albe Jul 16 '20 at 02:42
Thanks. This worked. I had 2 Nullable Columns for which I made 4 Unique Indexes. 1. Where none is null. 2. One each for where either of them are null. 3. One where both of them are null. And that did the trick. – Shaurya Chaudhuri Jul 16 '20 at 10:07
Ugh, but maintaining 4 indexes is very expensive for data modifications. Have you looked at the link in my answer? – Laurenz Albe Jul 16 '20 at 10:46

Postgres uniqueness constraint in is not working

1 Answers1