4

Is it possible to have a table with four columns:

CREATE TABLE accrual (
    add_date date NOT NULL,
    user_id integer NOT NULL,
    rate integer NOT NULL,
    amount numeric(7,3)
); 

and then constrain it so that if I already have one entry like

('2016-04-01', 3, 120, 25.6)

an attempt to insert another entry with the same date and user_id but a different rate would fail? i.e.

('2016-04-01', 3, 140, 15)

would fail, but

('2016-04-02', 3, 140, 15)

or

('2016-04-01', 4, 140, 15)

or

('2016-04-01', 3, 120, 15)

would be fine?

To be clear, the date and user_id are basically like an index, except duplicate indices are totally fine as long as the rates are the same. The amount is not constrained.

I am working with a postrgesql 9.5 database, and I am using PHP. My guess is that the easiest thing to do is to write some PHP code to perform the check. The closest question I could find to this was this one:

trigger or check constraint insert values based upon existence of others

but it is about an Oracle database and is slightly different.

Thanks for any suggestions!!!

3

As @NeilMcGuigan suggested, an Exclusion Constraint is the key here. The part I was unfamiliar with was the ability to use the <> operator. As available post Postgres 9.0:

CREATE TABLE accrual (
    add_date date NOT NULL,
    user_id integer NOT NULL,
    rate integer NOT NULL,
    amount numeric(7,3),
    EXCLUDE USING gist
        (date WITH =,
        user_id WITH =,
        rate WITH <>)
);

Two helpful links are:

  1. http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm -- a short tutorial on constraints in Postgres, with an example for the Exclusion constraint that uses the <> operator

and

  1. http://www.slideshare.net/pgconf/not-just-unique-exclusion-constraints (specifically slide 23) -- a presentation on the need for and use of Exclusion constraints. Slide 23 introduces the <> operator and its usefulness.
  • Nice one! Glad you figured it out – Neil McGuigan May 4 '16 at 19:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.