Обсуждение: How to add partitions to the existing table in PostgreSQL
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.
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
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.
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
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,RjOn 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
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 casefor a table named employee). 2) Improve performance if all queries (or nearly all) include the partitioning key in their WHERE clause and thus only afraction of the table needs to be read - but this only shows an effect if we are talking about millions or tens of millionsof rows. Again something I doubt would be necessary for a table named employee. And all queries that do not includethe partitioning key will be slower on the partitioned table! What problem are you trying to solve by partitioining that table? Thomas