Find the row violating the constraint to be added

I’m trying to add a check constraint to a table like

ALTER TABLE foo ADD CONSTRAINT bar CHECK (...);

​x
 
ALTER TABLE foo ADD CONSTRAINT bar CHECK (...);​

and I get the error:

ERROR:  check constraint "bar" is violated by some row
********** Error **********

ERROR: check constraint "bar" is violated by some row
SQL state: 23514

 
ERROR:  check constraint "bar" is violated by some row********** Error **********​ERROR: check constraint "bar" is violated by some rowSQL state: 23514​

Is there a way to have postgres (9.6) tell me at which row it fails? It would help a lot.

Answer

There sure is.

Select * from bar where NOT <desired check constraint>;

 
Select * from bar where NOT <desired check constraint>;​

Advertisement

Answer