In my experiements with a new RDBMS, I have tried to replicate what I am familiar with from Oracle, to Postgres.
Postgres supports partitioning: RANGE, LIST and HASH.
Here are some findings regarding LIST partitioning, which is extensively used in Oracle by my customers:
To create a list partitioned table in Postgres:
CREATE TABLE orders(
order_id integer,
country_code VARCHAR(5),
customer_id integer,
order_date DATE,
order_total numeric(8,2)
)
PARTITION BY LIST (country_code);
Add the desired partitions:
CREATE TABLE orders_NO
PARTITION OF orders FOR VALUES IN ('NO');
CREATE TABLE orders_SE
PARTITION OF orders FOR VALUES IN ('SE');
When working with Oracle, the equivalent table in Oracle could have been defined with a primary key on the column order_id:
CREATE TABLE orders(
order_id number primary key,
country_code varchar2(5),
customer_id number,
order_date date,
order_total number(8,2)
)
PARTITION BY LIST (country_code);
With PostgreSQL however, doing so will result in an error:
Error: ERROR: insufficient columns in PRIMARY KEY constraint definition
Detail: PRIMARY KEY constraint on table "orders" lacks column "country_code" which is part of the partition key
|
If the primary key is changed to use the partitioning column, you'll get a valid primary key:
CREATE TABLE orders(
id integer,
country_code VARCHAR(5) primary key,
customer_id integer,
order_date DATE,
order_total numeric(8,2)
)
PARTITION BY LIST (country_code);
But this is not what I want, since my table is designed to store multiple values for each country.
To create a partitioned table including a valid primary key:
CREATE TABLE orders(
id integer,
country_code VARCHAR(5),
customer_id integer,
order_date DATE,
order_total numeric(8,2),
PRIMARY KEY (id,country_code)
)
PARTITION BY LIST (country_code);
As long as the primary key includes the partition key, the syntax is accepted by the postgres server.
This is different from Oracle, where you define the primary key independently of the partition key.
As with Oracle, a primary key constraint will automatically create a unique B-tree index.
Note that
PRIMARY KEY (id,country_code)
can alternatively be replaced by
UNIQUE (id,country_code)
The result is the same, either way.