Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
While migrating List Partitions from Oracle, the CREATE TABLE DDLs have an extra casting which is erroring out in PG.
Example -
Oracle DDL -
CREATE TABLE partition_table
( id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
, description VARCHAR2(255)
)
PARTITION BY LIST (region)
( PARTITION p_west VALUES ('CA', 'OR', 'WA')
, PARTITION p_east VALUES ('NY', 'NJ', 'PA')
);
DDL dumped by export schema ( the key (region::text) is expected to be as region only)-
CREATE TABLE partition_table (
id numeric NOT NULL,
region varchar(2) NOT NULL,
status varchar(1),
description varchar(255),
PRIMARY KEY (id,region)
) PARTITION BY LIST ((region::text)) ;
Error in Postgres while executing -
ERROR: unsupported PRIMARY KEY constraint with partition key definition
DETAIL: PRIMARY KEY constraints cannot be used when partition keys include expressions.
Hi team,
While migrating List Partitions from Oracle, the CREATE TABLE DDLs have an extra casting which is erroring out in PG. Example - Oracle DDL -
DDL dumped by export schema ( the key
(region::text)
is expected to be asregion
only)-Error in Postgres while executing -
Thank you for your help! cc: @darold