darold / ora2pg

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.
http://www.ora2pg.com/
GNU General Public License v3.0
1.02k stars 343 forks source link

23.2 Generates wrong CONSTRAINTS_table.sql if REPLACE_COLS contains (when:when2) #1597

Closed Gnafu closed 1 year ago

Gnafu commented 1 year ago

WHEN is a reserved word in PostgreSQL so I want to replace it with "WHEN2"

ora2pg.conf

REPLACE_COLS mytable(oid:keyid,ref:keyref,when:when2)

CONSTRAINTS_table.sql

ALTER TABLE mytable ADD PRIMARY KEY (keyid);
ALTER TABLE mytable ALTER COLUMN WHEN SET NOT NULL;

Note "oid" has been correctly replaced with "keyid" but the "WHEN" is not "WHEN2" so when applying the CONSTRAINTS, the SQL fails.

Version 23.1 did not generate the "ALTER COLUMN" line, and this constraint is correctly included in the table.sql file in both versions: table.sql

CREATE TABLE mytable (
    keyid bigint NOT NULL,
        when2 timestamp NOT NULL
) ;
darold commented 1 year ago

This has been fixed:

CREATE TABLE repl_when (
        when2 bigint NOT NULL
) ;
ALTER TABLE repl_when ADD PRIMARY KEY (when2);