>On 2015-08-19 AM 02:57, Marc Mamin wrote: >>> 2. Creating a partition of a partitioned table >>> >>> CREATE TABLE table_name >>> PARTITION OF partitioned_table_name >>> FOR VALUES values_spec; >>> >>> Where values_spec is: >>> >>> listvalues: [IN] (val1, ...) >>> >> >> Would it make sense to allow one complementary partition to the listvalues? >> >> listvalues: [[NOT] IN] (val1, ...) >> >> I've thought a few times about moving data with some most common values to >> dedicated partitions >> and keeping the rest in a separate one... >> > >Thanks, that's definitely something to consider. > >I have been thinking of a sort of default list partition for the "rest" of >values. Would you rather declare that with something like the below than >having to enumerate all the values in a NOT IN list? Or the NOT IN way is >more intuitive/friendly? > >CREATE TABLE _rest PARTITION OF table_name FOR VALUES [ IN ] DEFAULT > >Of course, at most one such partition would be allowed.
On the one hand I guess it will be easies to check for partition overlapping if their definitions all contain the exact allowed values. But this could be generalized to range partitions too: CREATE TABLE _rest FALLBACK PARTITION OF table_name The need for it for range partitions seems very narrow at the first glimpse, but I remember bore administrative work in order to ensure that there always was a partition available for incoming data (from a very old time when I was still working with Oracle). To have it comfortable and nevertheless allow to define new partitions, this would require to always check/move data from the default partition to new partitions at create time. and 2 other thoughts: - In your proposal, the parent table is not materialized at all. Could it be used for the fallback partition? - what about always having a fallback partition? This would reduce the risk of unexpected failures and somewhat help Postgres stand out from the crowd :) regards, Marc Mamin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
