Subject: Re: How to add partitions to the existing table in PostgreSQL
To: pgsql-admin@lists.postgresql.org
References: 
 <CAN+uLsHX4mQq3m5Ev8szQQwU4gxijMMoO8XDAubm0GdmEKEdsg@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <f0b04f2b-1eab-4123-5593-90e494a2a274@gmx.net>
Date: Tue, 6 Oct 2020 15:33:46 +0200
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <CAN+uLsHX4mQq3m5Ev8szQQwU4gxijMMoO8XDAubm0GdmEKEdsg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-GB
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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
> (
> =C2=A0 =C2=A0 empno smallint NOT NULL,
> =C2=A0 =C2=A0 ename character varying(20),
> =C2=A0 =C2=A0 job character varying(20),
> =C2=A0 =C2=A0 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 droppi=
ng a partition (I highly doubt this is a use case for a table named employ=
ee).

2) Improve performance if all queries (or nearly all) include the partitio=
ning 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 millio=
ns 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 parti=
tioning key will be slower on the partitioned table!

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

Thomas