10

I have created a new master table with multiple partitions on basis of a column value using declarative partitioning of postgres 10. How can i add new columns to the tables?

Shreya Batra
  • 730
  • 1
  • 6
  • 15

1 Answers1

20

You only need to add that column to the base table: 

alter table master_table add new_column integer;

All partitions will automatically get that new column.

  • Does the documentation say anything about this? – moi Oct 02 '19 at 10:18
  • Although the documentation seems to suggest this is not possible, I've just verified that it does work. Adding a column to the master table did create a column in the partition. For reference, the documentation says: "It is not possible to specify columns when creating partitions with CREATE TABLE, nor is it possible to add columns to partitions after-the-fact using ALTER TABLE." (https://www.postgresql.org/docs/12/ddl-partitioning.html) – matmat Nov 17 '19 at 23:27
  • drop column works as well; just change the base table w/ `ALTER TABLE table_name DROP COLUMN column_name` and all partitioned tables will reflect the change – Suvro Choudhury Jun 15 '21 at 22:00
  • 3
    Note that the documentation talks about the _partitions_, not the parent table. It is completely correct that you can not change the layout of just a partition as it must match the parent. The documentation could be more clear about altering the parent table though; apparently the authors simply assumed that the reader takes a partitioned table just like any other table (things that are working differently on partitioned tables are explicitly mentioned in the documentation on ALTER TABLE, but it does not explicitly mention that everything else should work as expected – which it should). – scravy Aug 25 '21 at 04:43