ERROR: unique constraint on partitioned table must include all partitioning columns-CodePudding

I am migrating DB from Oracle to PostgreSQL14 , Oracle partitioned table was being used which i am trying to migrate to PG.

Below script i am using to create partition with index and tablespace

and getting below error

ERROR: unique constraint on partitioned table must include all partitioning columns

In oracle below index was being created on the table which i am now using at partitioned level as i come to know there is limitation , but is there any alternative as this error will cause change in the logic .

Oracle index

  CREATE UNIQUE INDEX COL_IDX_ID
     ON TABLE1(COL_ID,ERROR_TIME_DT,ERROR_TIME_TM);

Postgres script

          create table TABLE1
            (
                COL_DT date constraint N_COL_DT not null
                ,COL_TM numeric(9) constraint N_COL_TM not null
                ,COL_INPROC_ID numeric(1) constraint N_COL_INPROC_ID not null
                ,COL_SUB_ID numeric(7) constraint N_COL_SUB_ID not null
                ,COL_RECORD_DT date constraint N_COL_RECORD_DT not null
                ,COL_RECORD_TM numeric(9) constraint N_COL_RECORD_TM not null
                ,COL_TIMEOFFSET varchar(6) constraint N_COL_TIMEOFFSET not null
                ,COL_TYPE numeric(1) constraint N_COL_TYPE not null
                ,COL4 numeric(1) DEFAULT 0 not null
                ,ERROR_TIME_DT DATE
                 ,ERROR_TIME_TM numeric(9)
                ,ERROR__ID numeric(6)
                ,COL_ID VARCHAR(250)
                ,CONSTRAINT PK_COL primary key (COL_DT,COL_TM,COL_INPROC_ID,COL_SUB_ID) using index tablespace ${TABLESPACE_INDEX}
            )   partition by range (COL_DT);
            
            
            CREATE TABLE P_TABLE1_1 PARTITION OF TABLE1 FOR VALUES FROM (MINVALUE) TO (to_date('${PARTITION_DATE_LIMIT}', 'YYYYMM')) partition by list (COL_INPROC_ID);
    
    CREATE UNIQUE INDEX COL_COLID
        ON P_TABLE1_1 ( ERROR_TIME_DT ,ERROR_TIME_TM,COL_ID);
    
     
            
            CREATE TABLE P_TABLE1_1_P0 PARTITION OF P_TABLE1_1 FOR VALUES IN (0) ;
            CREATE TABLE P_TABLE1_1_P1 PARTITION OF P_TABLE1_1 FOR VALUES IN (1) ;
            
            
            CREATE TABLE PMAXVALUE PARTITION OF TABLE1 FOR VALUES FROM (to_date('${PARTITION_DATE_LIMIT}', 'YYYYMM')) TO (MAXVALUE) partition by list (COL_INPROC_ID);
    
           CREATE UNIQUE INDEX COL_ID_PMAX
        ON PMAXVALUE ( ERROR_TIME_DT ,ERROR_TIME_TM,COL_ID);
            
            CREATE TABLE PMAXVALUE_P0 PARTITION OF PMAXVALUE FOR VALUES IN (0) ;
            CREATE TABLE PMAXVALUE_P1 PARTITION OF PMAXVALUE FOR VALUES IN (1) ;

is there any workaround of this problem where i can keep the index same as in oracle "ERROR: unique constraint on partitioned table must include all partitioning columns"

CodePudding user response：

You can only have a unique index on a partitioned table if the partitioning key is part of the index keys. There is no other solution for this, and there is no good workaround either (see below for a small remedy).

Since an index on a partitioned table is always a partitioned index, it can only be UNIQUE if it is by definition, that is, if the partitioning key is an index key. The index partitions can only guarantee uniqueness within the partition itself.

You will have to live without the uniqueness of that index. You can instead define a unique index on each partition, which is almost as good, but does not enforce uniqueness across partitions.