patternsqlMinor
Constraint to prevent 'duplicates' only when column > 0
Viewed 0 times
preventcolumnconstraintwhenonlyduplicates
Problem
I have a table with a
Adding a new row to the table with a
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.
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.
Solution
Add a conditional unique index
dbfiddle here
CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0);dbfiddle here
Code Snippets
CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0);Context
StackExchange Database Administrators Q#166477, answer score: 6
Revisions (0)
No revisions yet.