Q:

Deferrable check constraint in PostgreSQL

I have function checking mandatory participation as follows:
CREATE FUNCTION member_in_has_address()
RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS (SELECT *
       FROM address a, member_details b
       WHERE b.member_id = a.member_id);
END;
$$  LANGUAGE plpgsql;

Then called from CHECK constraint
ALTER TABLE member_details
 ADD CONSTRAINT member_in_has_address_check
  CHECK (member_in_has_address());

To create deferable constraint in Standard SQL it would be:
ALTER TABLE member_details
 ADD CONSTRAINT member_in_has_address_check
  INITIALLY DEFERRED
  CHECK (member_in_has_address()); 

How can I do the same in PostgreSQL? 

A:

You can defer constraints in PostgreSQL in the same way as in other RDBMSs, but for current version (9.2) you can only defer UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES. Extract from this page of the manual:

DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be
deferred. A constraint that is not deferrable will be checked
immediately after every command. Checking of constraints that are
deferrable can be postponed until the end of the transaction (using
the SET CONSTRAINTS command). NOT DEFERRABLE is the default.
Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign
key) constraints accept this clause. NOT NULL and CHECK constraints
are not deferrable.
INITIALLY IMMEDIATE
INITIALLY DEFERRED
If a constraint is deferrable,
this clause specifies the default time to check the constraint. If the
constraint is INITIALLY IMMEDIATE, it is checked after each statement.
This is the default. If the constraint is INITIALLY DEFERRED, it is
checked only at the end of the transaction. The constraint check time
can be altered with the SET CONSTRAINTS command.

You can create a simple deferred foreign key from member_details to address instead of your current constraint to check, if every member has an address.
UPDATE: You need to create 2 foreign keys. One regular one from address(member_id) to member_details(member_id). The other one - deferred from member_details(member_id) to address(member_id).
With this two foreign keys you will be able to:

Create a member in member_details.
Create an address in address for member from step 1
Commit (with no errors)

OR

Create a member in member_details.
Commit (and get error from deferred foreign key).

A:

Wrap your queries in a transaction, and then use a deferred foreign key and deferred constraint triggers if at least one address is needed:
CREATE CONSTRAINT TRIGGER member_details_address_check_ins
  AFTER INSERT ON member_details
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE member_details_address_check_ins();

ALTER TABLE address
ADD CONSTRAINT address_member_details_member_id_fkey
FOREIGN KEY (member_id) REFERENCES member_details(member_id)
ON UPDATE NO ACTION ON DELETE NO ACTION
DEFERRABLE INITIALLY DEFERRED;

CREATE CONSTRAINT TRIGGER address_member_details_check_del
  AFTER DELETE ON address
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE address_member_details_check_del();

-- also consider the update cases for the inevitable merge of duplicate members.

On a separate note, normalized and pretty, but putting addresses and contact details such as emails in a separate address table occasionally introduces very colorful UI/UX issues. E.g. an untrained secretary changing the company and address of all of her boss' contacts at company A when one of them switched to company B. Yeah, seen it happen for real when the UI behaved differently from Outlook...
Anyway, and fwiw, I've found that it's usually more convenient to store this stuff in the same table as the contact, i.e. address1, address2, email1, email2, etc. It makes other things simpler for a variety of other reasons -- namely running checks like the one you're looking into. The extremely rare case where you'd want to store more than two such pieces of information are, in practice, simply not worth the hassle.

