Sometimes you want some fields of a record to be filled out, but only if some condition is met. For example, if a user registers as a company, you'll want his company name.
In MySQL you can do this using a trigger, which works just fine but in PostgreSQL you can do it in a much more compact way, using CHECK constraints.
In this example, all records added to foo must meet the requirement that is_admin must be false if the firstname or lastname are left empty. If is_admin is true then the firstname and lastname may not be null.
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (id INTEGER, firstname VARCHAR(100), lastname VARCHAR(100), is_admin BOOLEAN NOT NULL DEFAULT 'f');
ALTER TABLE foo ADD CONSTRAINT adminfields CHECK (is_admin = 'f' OR (is_admin='t' and firstname IS NOT NULL AND lastname IS NOT NULL));
These inserts meet the requirements:
INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id, firstname, lastname, is_admin) VALUES (1,'kees','pekinees','t');
And this one will fail:
INSERT INTO foo (id, is_admin) VALUES (2, 't');
You can expand on this by making sure the name is not just NOT NULL, but actually contains sane data, using a regexp.
The major advantage of CHECK over triggers is that the CHECK shows up in the CREATE statement of the table, giving you single overview of the table's behaviour.
Again; there is nothing really wrong with triggers, this is just more convenient.