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? — user330315
– user330315, Commented Jul 15, 2020 at 14:52
I meant adding the same rows in the table named TableColumnns. — Shaurya Chaudhuri
– Shaurya Chaudhuri, Commented Jul 15, 2020 at 15:25

Laurenz Albe · Accepted Answer · 2020-07-15 15:00:17Z

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, 2020 at 15:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Shaurya Chaudhuri Over a year ago

I added this into my liquibase script. The Unique Indexes seemed to be have created but the rows still get populated. ``` <sql> CREATE UNIQUE INDEX on tablecolumns(name, data_type, col_type, repeated, is_public, fieldowner) WHERE visibility=NULL </sql> ```

Laurenz Albe Over a year ago

That cannot work: anything = NULL can never be TRUE.

Shaurya Chaudhuri Over a year ago

Okay, I understand now. Let me check with IS NULL . That should work, right?

Laurenz Albe Over a year ago

If that's the only nullable column, it may be what you need.

Shaurya Chaudhuri Over a year ago

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.

|

Collectives™ on Stack Overflow

Postgres uniqueness constraint in is not working

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related