Postgresql – GIST exclude constraint with a specific value

postgresql

Is there a way to represent a constraint, where I prevent date range overlaps, but only when a column equals a specific value? So I can have zero or many inactive, but only ever one active?

I'm running into a problem where i need a gist constraint to check date range overlaps but only if a column equals a specific value ('active' in this case).

We have a table which has dates and there can be overlap, if a column equals a specific value (active can only ever have one, but inactive can be zero or many).

GIST seems to be able to say prevent any duplicate (2 active, but also excludes 1 active, 1 inactive, or inactive > 1).

Putting this in two constraints fails, because the gist only covers the range overlap and join id.

e.g.

ALTER TABLE "a_table" 
  ADD CONSTRAINT "CHK_some_name" 
  EXCLUDE USING gist ("a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

ALTER TABLE "a_table" 
  ADD CONSTRAINT "CHK_some_name" 
  EXCLUDE USING gist ("an_enum" with =, "a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

Any help/insight would be greatly appreciated

Best Answer

It sounds like you need a partial constraint.

alter table a_table add constraint foobar exclude using gist 
    (tstzrange("start_time", "end_time") WITH &&) where (active);

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));

How to Make a Column Mandatory in Every Insert/Update in PostgreSQL

Your check NEW.last_change_user is null in the trigger will only be true if the UPDATE statement explicitly contains a set last_change_user = NULL, not if the value did not change (then new and old are simply identical).

You can check if the value changed using if new.last_change_user is distinct from old.last_change_user, but you can not check if the value was specified at all, possibly supplying the same value that is already in the column.

Something like Oracle's updating(column_name) function would be needed here, but Postgres does not have that.

So I think the answer to your question is: no, that's not possible.

Best Answer

Related Solutions

PostgreSQL duplicate key violates unique constraint error

EDIT (2012-11-27)

How to Make a Column Mandatory in Every Insert/Update in PostgreSQL

Related Question