Add serial column to a partitioned table in postgres

Question

We have a table which is partitioned, we now want to add a serial id column to the table.

Do we need to explicitly add the id column to all the portioned tables or we just need to add the column to the master table.

Any help in this would be really great.

Thanks.

Answer 1

After an existing regular table become a partition of a partitioned table. Then There is only one physical table.

The partitioned table itself is a “virtual” table having no storage of its own. Instead, the storage belongs to partitions, which are otherwise-ordinary tables associated with the partitioned table. Each partition stores a subset of the data as defined by its partition bounds. All rows inserted into a partitioned table will be routed to the appropriate one of the partitions based on the values of the partition key column(s). Updating the partition key of a row will cause it to be moved into a different partition if it no longer satisfies the partition bounds of its original partition.

Partitions may themselves be defined as partitioned tables, resulting in sub-partitioning. Although all partitions must have the same columns as their partitioned parent, partitions may have their own indexes, constraints and default values, distinct from those of other partitions. See CREATE TABLE for more details on creating partitioned tables and partitions.

begin;
 CREATE temp TABLE list_parted (
     a int primary key ,
     b text
) PARTITION BY range (a);
CREATE temp TABLE l_part (a int primary key, b text );
CREATE temp TABLE l_part_2 (a int primary key, b text );
insert into l_part values (1),(2);
ALTER TABLE list_parted ATTACH PARTITION l_part FOR VALUES FROM (1) TO (10);
ALTER TABLE list_parted ATTACH PARTITION l_part_2 FOR VALUES FROM (11) TO (19);
commit ;



alter table list_parted  add column test_serial bigserial;
\d l_part_2

return

                                    Table "pg_temp_3.l_part_2"
+-------------+---------+-----------+----------+--------------------------------------------------+
|   Column    |  Type   | Collation | Nullable |                     Default                      |
+-------------+---------+-----------+----------+--------------------------------------------------+
| a           | integer |           | not null |                                                  |
| b           | text    |           |          |                                                  |
| test_serial | integer |           | not null | nextval('list_parted_test_serial_seq'::regclass) |
+-------------+---------+-----------+----------+--------------------------------------------------+
Partition of: list_parted FOR VALUES FROM (11) TO (19)
Indexes:
    "l_part_2_pkey" PRIMARY KEY, btree (a)

However:

alter table list_parted  add column test_serial1 integer generated always as identity;

then error occurs:

--ERROR:  cannot recursively add identity column to table that has child tables