New answers tagged

-3

select isnull(column,1) from table


3

Mark all your existing rows as old: ALTER TABLE integrations.billables ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false; UPDATE integrations.billables SET is_old = true; And set up the constraint to ignore old rows: ALTER TABLE integrations.billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK ( NOT(("qb_id", "xero_id", ...


5

Your CHECK constraint can be much simpler: ALTER TABLE billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK (qb_id IS NOT NULL OR xero_id IS NOT NULL OR freshbooks_id IS NOT NULL OR unleashed_id IS NOT NULL OR csv_data IS NOT NULL OR myob_id IS NOT NULL) NOT VALID; Or even ...


10

Just add the constraint as NOT VALID From the manual: If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified ...


9

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value: ( (("qb_id" IS NOT NULL) :: INTEGER + ("xero_id" IS NOT NULL) :: INTEGER + ...


4

This was not possible in Postgres up to version 9.1. From 9.2 onwards you can define a check constraint as NOT VALID (equivalent to WITH NOCHECK in MS SQL Server). See http://www.postgresql.org/docs/9.2/static/sql-altertable.html for more detail. I'm not generally happy with this sort of thing where it is at all possible to avoid. A compromise if you have a ...



Top 50 recent answers are included