search

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
978 stars 341 forks source link

[Feature Request] Add ON CONFLICT [...] to INSERT statements #1648

Closed clemensrieder closed 1 year ago

clemensrieder commented 1 year ago

Hi everyone,

I am in the process of exporting a really, really messy Oracle database via your fantastic tool. Unfortunately, the export takes quite some time. Since there is live data written to the database I get incongruencies when I try to add my foreign keys at the end.

An example: Table1.fk is linked to Table2.pk. Table1 is exported. The user deletes the corresponding Table2.pk. It seems there is no CASCADE setting in place. Table2 gets exported a bit later. Adding the foreign key constraint after importing the data will tell me that Table1.fk cannot be set because there is no corresponding Table2.pk.

PostgreSQL's COPY statement seems somewhat limited to work with errors but I read that the INSERT statement has an ON CONFLICT clause.

It would be super interesting to see this clause implemented in the ora2pg.conf options. Although it is slower it would help a lot in importing such data. As of now, I will write a short Python script adding the clause to each statement 🙂

What's your thought on that?

darold commented 1 year ago

Hi,

If I understand well, you are asking for a possibility to add an ON CONFLICT DO NOTHING clause at end of the insert?

clemensrieder commented 1 year ago

Thank you for your quick answer! I was thinking about that - yes!

darold commented 1 year ago

Commit c6a83c8 adds this feature.

    INSERT_ON_CONFLICT
        When enabled this instruct Ora2Pg to add an ON CONFLICT DO NOTHING
        clause to all INSERT statements generated for this type of data
        export.