How can we declare multiple partitions in a single statement in Postgres-CodePudding

We can create multiple partitions in a single statement in Oracle, but how can we implement the same in PostgreSQL.

CodePudding user response：

Simple: run several CREATE TABLE statements in a single DO statement:

DO
'BEGIN
   CREATE TABLE part1 PARTITION OF tab FOR VALUES IN (1);
   CREATE TABLE part2 PARTITION OF tab FOR VALUES IN (2);
   CREATE TABLE part3 PARTITION OF tab FOR VALUES IN (3);
END';

But I don't see why it is so important to you do do it with a single SQL statement.

CodePudding user response：

An equivalent doesn't exist in PostgreSQL. Due to how it implements its partitioning system, it would require you to create multiple tables at once. You will have to create the partitioned table and instead of specifying its partitions and bounds right there and then, you'll need to create each partition as a separate table, specifying partition bounds for each.

As always, there is much more to it but the below is meant to be a simplified take on the topic: in Oracle table partitions are a way to divide the original, first-class table "under the hood" and within its internal structure, while in PostgreSQL it's the other way around - it is the partitions that are the standalone, first-class tables and the partitioned table only links them together. Because of this, a partition cannot exist on its own in Oracle and be detached, individually referenced or linked to, and you typically need to go through the partitioned table to interact with its partitions. In PostgreSQL, for the most part you can work with the partitions like regular tables, and the partitioned table doesn't really work without them, acting as an empty view.