PostgreSQL Constraints – Creating a UNIQUE Constraint from a JSON Object

jsonpostgresqlunique-constraint

Lets take some example table peoples , that got only 2 fields: id and data(json).

SELECT data FROM peoples ;
{"name": "Adam","pos":"DBA","age":22 }
{"name": "Alice","pos":"Security","age":33 }
{"name": "Bob","pos":"Manager","age":42 }

I want to create constraint for "pos" field, that must be unique.
I've searched over the internet about JSON constraints but no results.

How can I handle this problem ?

Best Answer

First and foremost: I agree with both the comments of @a_horse_with_no_name and @dezso: you should normalize your data. JSON is not for that.

However, if some reason I cannot fathom really makes this an advantage, it is possible:

Create an expression based UNIQUE INDEX:

CREATE UNIQUE INDEX people_data_pos_idx ON peoples( (data->>'pos') ) ;

If, at this point, you try to insert the following piece of data into your table (with an already existing ->>pos):

INSERT INTO peoples(data)
VALUES
    ('{"name": "Eve", "pos":"DBA", "age":34}') ;

You get this as a response:

ERROR: duplicate key value violates unique constraint "people_data_pos_idx"
SQL state: 23505
Detail: Key ((data ->> 'pos'::text))=(DBA) already exists.

NOTE: I've assumed that data.pos will always be a string. If you want to generalize, you can use ( (data->'pos') ) instead. You would index then a JSON(B) expression instead of a text. Check JSON Functions and Operators.

Related Solutions

PostgreSQL duplicate key violates unique constraint error

(I try to compile an answer from the comments.)

It looks like there is a problem in the process with which your application generates the value for the primary key. Generally it is wiser to leave this to the DBMS: define a sequence and leave it fill the PK values. If you have a sequence, you have at least to options: either set the value like

$eventid = exec(SELECT nextval('your_sequence')) // pseudocode!

and use this in the INSERT statement, or completely leave it to PostgreSQL by omitting PK from the INSERT:

INSERT INTO events (source, object, [...]) 
VALUES (2, 3, [...]);

(Personally I usually opt for the second solution.)

You can change to this behaviour by setting the data type of your PK column to bigserial (in case of an integer, it would be just serial):

ALTER TABLE events ALTER COLUMN eventid TYPE bigserial;

This will raise a notice which tells you that a sequence called (probably) events_eventid_seq is generated. You can use it as described above.

EDIT (2012-11-27)

You can set the sequence by a query like

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

In order to let the sequence produce the next value for eventid, you must not set a value explicitly in your INSERT. If you do, it will take the manually supplied value and won't advance the sequence. This will cause the same unique violation error later.

Regarding ALTER TABLE: as you could see, there is some discrepancy between ALTER TABLE - type does not exists and CREATE TABLE. Are you sure you try this on the same database? I can't remember the older versions, but already 8.3 had the bigserial shorthand (it's not a real type). (Giving it further thought, it is quite possible you can't use it in ALTER TABLE, since it is a shorthand...) You can solve the problem by following the manual:

CREATE SEQUENCE events_eventid_seq;

ALTER TABLE events 
ALTER COLUMN eventid TYPE bigint;

ALTER SEQUENCE events_eventid_seq OWNED BY events.eventid;

SELECT setval('events_eventid_seq', (SELECT max(eventid) FROM events));

Postgresql – Duplicate key violates unique constraint

Your rule does not have a WHERE clause and any update is trying to modify the whole users table.

You can/should add WHERE id = old.id:

CREATE OR REPLACE RULE students_data_update AS
    ON UPDATE TO students_data DO INSTEAD  
UPDATE users 
SET f_name = new.f_name, 
    l_name = new.l_name,
    --- ...
WHERE id = old.id ;               --- added

Note: I had never used the RULE system before in Postgres. But the above suggestion worked when tested at SQL-Fiddle.

Best Answer

Related Solutions

PostgreSQL duplicate key violates unique constraint error

EDIT (2012-11-27)

Postgresql – Duplicate key violates unique constraint

Related Question