Unique Constraint Ignored by Null

Question

I am using Postgres 9.6, and have an issue where I am using jsonb_populate_recordset.

I created an UNIQUE constraint on the table, but I am able to bypass this when performing an INSERT with null values.

Is there was a way to force the unique constraint to keep only 1 record, even if it has null values, and not allow duplicates afterward?

Here is a quick example:

CREATE TABLE person(
    person_id SERIAL PRIMARY KEY,
    person_name TEXT,
    CONSTRAINT unq_person UNIQUE(person_name)
);

INSERT INTO person (person_name) VALUES ('Frank');

CREATE TABLE locations(
  location_id SERIAL PRIMARY KEY,
  city TEXT,
  state TEXT,
  address TEXT,
  address_country TEXT,
  postal_code TEXT,
  person_id INTEGER REFERENCES person(person_id)
  ON DELETE CASCADE,
  CONSTRAINT unq_location UNIQUE(city, state, address, address_country, postal_code, person_id)
);

In this example, city and address are null (but theoretically, they could all be null, or any combination of record properties).

Every time I run the following query, a new record gets inserted. I don't want more than one of these records.

INSERT INTO locations (city, state, address, address_country, postal_code, person_id)
SELECT city, state, address, address_country, postal_code, person_id
FROM jsonb_populate_recordset(NULL::locations, '[{"city": null, "address": null, "address_country": "USA", "state": "NY", "person_id": 1, "postal_code": "10001"}]'::jsonb)

How can I only allow 1 record, and not multiple when inserting a JSONB object into Postgres?

Duplicate of https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns — L. Scott Johnson, Feb 08 '18 at 17:33
Possible duplicate of [Create unique constraint with null columns](https://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns) — L. Scott Johnson, Feb 08 '18 at 17:33
@L.ScottJohnson - based on that, I would create 5 partial indexes? — unseen_damage, Feb 08 '18 at 19:20
@VaoTsun - That suggested solution does not appear to work: `ALTER TABLE locations ADD CONSTRAINT unq_location UNIQUE(COALESCE(city, state, address, address_country, postal_code, person_id))` gives a syntax error. Did I miss something? — unseen_damage, Feb 08 '18 at 19:45
@VaoTsun - Still having syntax error when doing `CONSTRAINT unq_location UNIQUE(coalesce(city, null),coalesce(state, null),coalesce(address,null))`. Does `COALESCE` work with `UNIQUE CONSTRAINTS`? — unseen_damage, Feb 08 '18 at 20:13
@unseen_damage try `CREATE UNIQUE INDEX unq_location on locations ( coalesce(city,'null') , coalesce("state",'null') , coalesce(address,'null') , coalesce(address_country,'null') , coalesce(postal_code,'null') , coalesce(person_id,0) ) ; ` — Vao Tsun, Feb 08 '18 at 20:44

score 0 · Answer 1 · answered Feb 08 '18 at 17:48

0

To your existing query:

insert into locations
(fields)
select values
from etc

add this filter

where not exists 
(select 1
from locations l2
where locations.person_id = l2.person_id
)

It has nothing to do with null values.

answered Feb 08 '18 at 17:48

Dan Bracuk

20,699
4
26
43

I added that `WHERE` clause to the end of my `INSERT` statement, and got an error - `ERROR: invalid reference to FROM-clause entry for table "locations" SQL state: 42P01 Hint: Perhaps you meant to reference the table alias "l2". Character: 380 ` Did I miss something? – unseen_damage Feb 08 '18 at 19:59

Unique Constraint Ignored by Null

1 Answers1