Re: How to add partitions to the existing table in PostgreSQL

Naveen Kumar schrieb am 06.10.2020 um 08:31:
> How to add partitions to the existing table in PostgreSQL
>
> for ex, below is my table definition:
>
> CREATE TABLE ot.employee
> (
>     empno smallint NOT NULL,
>     ename character varying(20),
>     job character varying(20),
>     deptno smallint
> )
>
> Now I would like to add partition to deptno column
>
> I don't see any alter table command to add the partition to the existing table.

I seriously doubt a table named "employee" needs partitioning.

Partitioning serves essentially two purposes:

1) quickly delete large amount of data (millions of rows) by simply dropping a partition (I highly doubt this is a use case for a table named employee).

2) Improve performance if all queries (or nearly all) include the partitioning key in their WHERE clause and thus only a fraction of the table needs to be read - but this only shows an effect if we are talking about millions or tens of millions of rows. Again something I doubt would be necessary for a table named employee. And all queries that do not include the partitioning key will be slower on the partitioned table!

What problem are you trying to solve by partitioining that table?

Thomas