cancel
Showing results for 
Search instead for 
Did you mean: 

Primary key supported on partitioned tables in EPAS 11

In EPAS 11, the primary key is enforced on a partition and partition ID for a particular row and not for the parent partitioned table. You will only get the constraint violation error when the duplicate data is inserted in the same partition ID again. However, if it is directed to a partition ID that is different than where the first row was inserted, the insert will go through.
 

From EPAS 11, onward we do have the same error when a user tries to add PRIMARY/UNIQUE key on an existing partitioned table but that error is suppressed for redwood CREATE TABLE syntax by skipping PRIMARY/UNIQUE key on the root table:

 

EPAS 11:

 

CASE 1: Adding primary key on existing partitioned table:

 

      edb=# create table foo (a int, b int) partition by hash(a)(partition p1);
      CREATE TABLE

      edb=# alter table foo add primary key (b);
      ERROR: insufficient columns in PRIMARY KEY constraint definition
      DETAIL: PRIMARY KEY constraint on table "foo" lacks column "a" which is part of the partition key.

 

CASE 2: Adding primary key via redwood CREATE TABLE syntax:

 

      edb=# create table foo (a int, b int primary key) partition by hash(a)(partition p1);
      CREATE TABLE

 

https://www.postgresql.org/docs/11/ddl-partitioning.html

 

EPAS 10:

 

Adding primary key on existing partitioned table:

 

        edb=# create table foo (a int, b int) partition by hash(a)(partition p1);
        CREATE TABLE

       edb=# alter table foo add primary key (b);
       ERROR: primary key constraints are not supported on partitioned tables
       LINE 1: alter table foo add primary key (b);

 

https://www.postgresql.org/docs/10/ddl-partitioning.html

Version history
Revision #:
4 of 4
Last update:
3 weeks ago
Updated by: