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'.
