Constraint name for named NOT NULL constraints is ignored

Csaba Nagy wrote:

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

I don't think we can throw an error for such cases because it would
prevent us from accepting valid SQL statements. I even see an example
in our CREATE TABLE manual page:

CREATE TABLE distributors (
did integer CONSTRAINT no_null NOT NULL,
name varchar(40) NOT NULL
);

Added to TODO:

o Have CONSTRAINT cname NOT NULL record the contraint name

Right now pg_attribute.attnotnull records the NOT NULL status
of the column, but does not record the contraint name

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +