While upgrading our schema between application versions, we also had a
few constraint changes. Some of those changes were dropping NOT NULL
constraints on some columns. Our schema had a few such NOT NULL
constraints, which were created using the named variant of the column
constraint clause (something like '... col_name col_type CONSTRAINT
nn_col_name NOT NULL ...'). This syntax is happily accepted by postgres.
So our schema change script was expecting that simply dropping those
named constraints will drop the NOT NULL constraint on the relevant
columns, but the constraint is just simply not there at all, so trying
to drop it gives an error.
From the description of the pg_constraint table
http://www.postgresql.org/docs/8.2/static/catalog-pg-constraint.html
is clear that it does not hold NOT NULL constraints, which go to the
pg_attribute table, but then maybe an error should be raised if somebody
tries to create a named NOT NULL constraint ? Ignoring integral parts of
the SQL syntax feels somewhat mySQL-ish. Or at least mention this
behavior on the CREATE TABLE page (I couldn't find it if it's there):
http://www.postgresql.org/docs/8.2/static/sql-createtable.html
Cheers,
Csaba.