Re: Need guidance on partioning

On Wed, May 22, 2024 at 8:50 AM Muhammad Salahuddin Manzoor <
salahuddin(dot)m(at)bitnine(dot)net> wrote:

> Dear Srinivasan,
>
> Implementing partitioning in PostgreSQL can significantly improve the
> performance of your database
>
> 1. Deleting Records Older Than 18 Months
>
> Create the Parent Table: Define your main table as partitioned by range on
> the timestamp column.
>
> CREATE TABLE your_table (
> id SERIAL PRIMARY KEY,
> timestamp TIMESTAMPTZ NOT NULL,
> account_id INT,
> user_id INT,
> -- other columns
> ) PARTITION BY RANGE (timestamp);
>
>
That's invalid syntax (as of PG 15):

psql (15.7)
Type "help" for help.

dba=#
dba=# CREATE TABLE your_table (
dba(# id SERIAL PRIMARY KEY,
dba(# timestamp TIMESTAMPTZ NOT NULL,
dba(# account_id INT,
dba(# user_id INT
dba(# ) PARTITION BY RANGE (timestamp);
ERROR: unique constraint on partitioned table must include all
partitioning columns
DETAIL: PRIMARY KEY constraint on table "your_table" lacks column
"timestamp" which is part of the partition key.