Constraint to prevent 'duplicates' only when column > 0

Question

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.

Answer 1

Add a conditional unique index

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

dbfiddle here