Search code examples
sqlpostgresqlindexinguniqueunique-index

unique index in postgreSQL

I have a PostgreSQL table with unique index

CREATE SEQUENCE public.batterysensorhistory_id_seq NO MINVALUE NO 
MAXVALUE NO CYCLE;
CREATE TABLE batterysensorhistory
(
    id integer DEFAULT 
nextval('batterysensorhistory_id_seq'::regclass) PRIMARY KEY NOT NULL,
batteryid uuid NOT NULL,
sensorid integer NOT NULL,
isactivelink boolean DEFAULT false NOT NULL,
...
);

create unique index on batterysensorhistory (batteryid, sensorid, isactivelink)
where isactivelink = TRUE;

I want to set a constraint that only one row with same batteryid && sensorid can have isactivelink = true, but my unique index don't work.

Also, I create this example in sqlfiddle: http://sqlfiddle.com/#!15/c3b37/1

Any idea, whats wrong, with my code?

Solution

  • I think you want:

    create unique index on batterysensorhistory (batteryid, sensorid)
    where isactivelink = TRUE;

    But your version should work.

    Here is an example of it failing.