I've looked into what it would take to add an INCLUDING CONSTRAINTS option to
CREATE TABLE LIKE. This would make it much more practical to use CREATE TABLE
LIKE to create new partitions instead of having to create new children then
remove them from the inheritance before loading and manipulating data to be
added to the partitioned table.
It doesn't look very hard. But currently analyze.c does no direct table scans
on system tables like pg_attribute or pg_constraint -- it only uses the
relcache. Is that an important property to maintain?
If we want to keep direct table scans out of analyze.c it means not being able
to copy the DEFERRABLE, and INITIALLY DEFERRED properties of any constraints.
For check constraints that may not be a major issue since I think they're not
meaningful. But I had hoped to include foreign key constraints as well.
I had also hoped to implement an INCLUDING INDEXES option as well since I
think DBAs would find it quite surprising to create a new partition and find
it doesn't have the same indexes as other partitions.
Again that seems like it would be quite doable though again I think it would
require doing a direct index scan. The one major gotcha is that it won't be
possible to create partial indexes this way. The trick partial indexes use to
parse their wHERE clause doesn't work if the table isn't already created.
Perhaps there's some way to work around this.
--
greg