How to add partitions to the existing table in PostgreSQL

pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed

How to add partitions to the existing table in PostgreSQL
4+ messages / 3 participants
[nested] [flat]

* How to add partitions to the existing table in PostgreSQL
@ 2020-10-06 06:31  Naveen Kumar <naveenmcp@gmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Naveen Kumar @ 2020-10-06 06:31 UTC (permalink / raw)
  To: pgsql-admin

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.

Please help me on this.

Thank you
Naveen

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to add partitions to the existing table in PostgreSQL
@ 2020-10-06 07:50  Nagaraj Raj <nagaraj.sf@yahoo.com>
  parent: Naveen Kumar <naveenmcp@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Nagaraj Raj @ 2020-10-06 07:50 UTC (permalink / raw)
  To: Naveen Kumar <naveenmcp@gmail.com>; +Cc: pgsql-admin

 
There is no way to add a partition to an existing table. All you need to do create a new table with the partition on the desired column and copy data from old to new then do alter table name. 


Thanks,Rj




    On Monday, October 5, 2020, 11:31:28 PM PDT, Naveen Kumar <naveenmcp@gmail.com> wrote:  
 
 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.

Please help me on this.

Thank youNaveen
  

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to add partitions to the existing table in PostgreSQL
@ 2020-10-06 08:21  Naveen Kumar <naveenmcp@gmail.com>
  parent: Nagaraj Raj <nagaraj.sf@yahoo.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Naveen Kumar @ 2020-10-06 08:21 UTC (permalink / raw)
  To: Nagaraj Raj <nagaraj.sf@yahoo.com>; +Cc: pgsql-admin

Thanks Raj.

Regards
Naveen


On Tue, Oct 6, 2020 at 1:20 PM Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:

>
> There is no way to add a partition to an existing table. All you need to
> do create a new table with the partition on the desired column and copy
> data from old to new then do alter table name.
>
>
>
> Thanks,
> Rj
>
>
>
>
>
> On Monday, October 5, 2020, 11:31:28 PM PDT, Naveen Kumar <
> naveenmcp@gmail.com> wrote:
>
>
> 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.
>
> Please help me on this.
>
> Thank you
> Naveen
>
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to add partitions to the existing table in PostgreSQL
@ 2020-10-06 13:33  Thomas Kellerer <shammat@gmx.net>
  parent: Naveen Kumar <naveenmcp@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Thomas Kellerer @ 2020-10-06 13:33 UTC (permalink / raw)
  To: pgsql-admin@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2020-10-06 13:33 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-06 06:31 How to add partitions to the existing table in PostgreSQL Naveen Kumar <naveenmcp@gmail.com>
2020-10-06 07:50 ` Nagaraj Raj <nagaraj.sf@yahoo.com>
2020-10-06 08:21   ` Naveen Kumar <naveenmcp@gmail.com>
2020-10-06 13:33 ` Thomas Kellerer <shammat@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox