Postgres partition avoiding redundant column Say I have a table, say project_data with lots of rows where the data may be grouped by a project_id integer column, and partition it onto one table for each project_id value.
Do I then need the project_id column in every partition table, when the value is implicit?
It seems that having an extra column in project_data_5 project_id integer not null default 5 would be redundant as it will never have a value other than 5 in that table.
This is for postgresql 10.
Yes, the partitioning column must be part of the table.
I assume that you are using list partitioning. Now with list partitioning it is possible to have several values for the partitioning column in the same partition (FOR VALUES IN (1, 2, 27)), so one couldn't omit the partitioning column from the table.
Also, one of the key features of partitioning is that you can quickly attach and detach partitions. Now if the partitioning key were not stored, both attaching and detaching partitions would require a table rewrite.