Hi everyone,
I'm currently working on migrating a MySQL database to PostgreSQL using pgloader, and I've run into a couple of issues related to quoting identifiers.
In my MySQL database, I have a table defined like this:
CREATE TABLE IF NOT EXISTS \MapModelObjects(mapModelObjId INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT .....);
When I use the WITH quote identifiers; option in pgloader, its shows in terminal this error:
Database error 42703: column ""mapModelObjId"" of relation "MapModelObjects" does not exist
CONTEXT: PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows.
It seems like the column names, particularly those involving AUTO_INCREMENT primary keys, are being incorrectly converted. I suspect pgloader is adding extra quotes around the column names, which might be causing this issue. Has anyone experienced something similar when migrating from MySQL to PostgreSQL? I'm wondering if there's a specific way to handle quoted identifiers in this case or if there’s an alternative method to correctly convert MySQL’s AUTO_INCREMENT to PostgreSQL’s SERIAL or BIGSERIAL types. Thank you very much.