3

I have a table with a fk column that should be unique when another int column is > 0. If that sentence doesn't make sense, here is an example of valid rows:

CREATE TABLE foo
AS
  SELECT fk,int
  FROM ( VALUES
    ( 1, -12 ),
    ( 1,  20 ),
    ( 1,   0 )
  ) AS (fk, int);

Adding a new row to the table with a fk value of 1 and a int column that is > 0 would be invalid.

Is there a way to do this with a constraint or index? I feel like an exclude constraint nearly gets me there, but I couldn't figure out a way to get it to work.

I'm using Postgres 9.6.2.

6

Add a conditional unique index

CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0);

dbfiddle here

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.