Issues with Quoting Identifiers in MySQL to PostgreSQL Migration via pgloader

Other Tech Topics
1

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.

2

PostgreSQL defaults to lowercase for unquoted names, so if MySQL stored MapModelObjects with camel case, but pgloader converts it to lowercase, PostgreSQL won’t recognize it unless quoted exactly the same way. Try disabling quote identifiers in pgloader to let PostgreSQL handle names properly. If that doesn't work, manually rename the columns after migration using ALTER TABLE mapmodelobjects RENAME COLUMN mapModelObjId TO map_model_obj_id;.

Also, MySQL’s AUTO_INCREMENT should be converted to SERIAL (for INTEGER) or BIGSERIAL (for BIGINT) in PostgreSQL. If the column is already there, you may need to create and attach a sequence manually.