When trying to compare data between Oracle and PostgreSQL the generated script is incorrect and wont run unless modified.
Steps to reproduce the issue:
Run this script in Oracle
CREATE TABLE HR.TestCompare
(
COLUMN_1 VARCHAR2(30 BYTE),
COLUMN_2 VARCHAR2(30 BYTE),
COLUMN_3 VARCHAR2(30 BYTE)
);
CREATE UNIQUE INDEX HR.TestCompare_PK ON HR.TestCompare
(COLUMN_1);
ALTER TABLE HR.TestCompare ADD (
CONSTRAINT PK
PRIMARY KEY
(COLUMN_1)
USING INDEX HR.TestCompare_PK
ENABLE VALIDATE);
INSERT INTO HR.TESTCOMPARE (
COLUMN_1, COLUMN_2, COLUMN_3)
VALUES ( 'd','b','c');
commit;
Run this script in PostgreSQL
CREATE TABLE public.testCompare
(
"Column_1" VARCHAR (20) NOT NULL,
"Column_2" VARCHAR (20),
"Column_3" VARCHAR (20),
CONSTRAINT testCompare_pkey PRIMARY KEY ("Column_1")
);
INSERT INTO public.testcompare(
"Column_1"
,"Column_2"
,"Column_3"
) VALUES (
'a'
, 'a'
,'a'
);
commit;
Now run a Data Compare (Tools |Compare|Data Compare) where Source is the Oracle Database and target the PostgreSQL database
Open the synchronization script
The script generated is
DELETE FROM public.testcompare WHERE Column_1 = 'a';
INSERT INTO public.testcompare (Column_1, Column_2, Column_3)
VALUES ('d', 'b', 'c');
Which fails with error "ERROR: column "column_1" does not exist;" when running it in PostgreSQL