I have been looking at the new syntax in create table such as unique and
primary key constraints (new as in I just noticed it, I don't know when it
was added). It seems to me there is a minor gotcha when using pg_dump/psql
to reload a database.
When indexes were created separately pg_dump would put the index creation at
the end of its output, after the data was loaded. My understanding is that
this is faster than creating the indexes first and then populating the
table.
When using table constraints the constraints are created as part of the
table creation and there will be a performance hit when loading a dumped
database. This discourages using these new features when the table may
become large.
The solution it would seem is to expand 'alter table' to allow adding the
constraints after the copy and change pg_dump accordingly. On the surface
these changes to 'alter table' don't look to complex.
The only issue is what to do if the constraint is not met by pre-existing
data. The alter statement should probably fail in these cases or maybe an
option to force it to accept the existing data. The issues are the same as
in 'create unique index'.
