Return to Answer

NOT VALID

ALTER TABLE a DROP CONSTRAINT a_constr_1; ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID; 

Solution: NOT VALID

ALTER TABLE a  DROP CONSTRAINT a_constr_1 , ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID; 

A single statement is fastest and rules out race conditions with concurrent transactions. (Two commands in a single transaction would work, too.)

CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction (per documentation):

Now, expressions in CHECK constraints are allowed to use functions, even user-defined functions. Those should be restricted to IMMUTABLE functions, but Postgres does not currently enforce this. According to this related discussion on pgsql-hackers, one reason is to allow references to the current time, which is not IMMUTABLE by nature.

##PostgreSQL 9.2 or later

###pg_dump option --exclude-table-data

But complications with other constraints on the same table might ensue. There is an even better solution: ###NOT VALID

There is the NOT VALID modifier for constraints. Only available for FK constraint in v9.1, but this was extended to CHECK constraints in 9.2. Per documentation:

A plain postgres dump file consists of three "sections":

Here is where it gets interesting. Per documentation:

CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction in the manual:

Still, expressions in CHECK constraints are allowed to use functions, even user-defined functions. Those should be IMMUTABLE, but Postgres does not currently enforce this. According to this related discussion on pgsql-hackers, one reason is to allow references to the current time, which is not IMMUTABLE by nature.

PostgreSQL 9.2 or later

pg_dump option --exclude-table-data

But complications with other constraints on the same table might ensue. There is an even better solution:

NOT VALID

Up to Postgres 9.1, the NOT VALID modifier was only available for FK constraints. This was extended to CHECK constraints in Postgres 9.2. The manual:

A plain Postgres dump file consists of three "sections":

Here is where it gets interesting. The manual:

###pgdpump option --exclude-table-data

###pg_dump option --exclude-table-data