Issues with Quoting Identifiers in MySQL to PostgreSQL Migration via pgloader

[joejack77]

• [ ] pgloader --version

    pgloader version "3.6.70f3557"
    compiled with SBCL 2.3.1-bp155.1.27-suse
  
  

• [x] did you test a fresh compile from the source tree? yes, built from source https://github.com/dimitri/pgloader ``

• [x] did you search for other similar issues? yes, here: https://stackoverflow.com/questions/78240630/pgloader-with-quote-identifiers-column-invoiceno-of-relation-order-d, nobody responded tho

• [x] how can I reproduce the bug? in my opinion you can reproduce it by making AUTOINCREMENT PRIMARY KEY column

LOAD DATABASE FROM mysql://root:pass@localhost/db INTO postgresql://postgres:pass@localhost/db WITH quote identifiers;

build/bin/pgloader migration.load

output: Database error 42703: column ""keyId"" of relation "ConfigKeys" does not exist CONTEXT: PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows QUERY: DO $$ DECLARE n integer := 0; r record; BEGIN FOR r in SELECT 'select ' || trim(trailing ')' from replace(pg_get_expr(d.adbin, d.adrelid), 'nextval', 'setval')) || ', (select greatest(max(' || quote_ident(a.attname) || '), (select seqmin from pg_sequence where seqrelid = (''' || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only ' || quote_ident(nspname) || '.' || quote_ident(relname) || '));' as sql FROM pg_class c JOIN pg_namespace n on n.oid = c.relnamespace JOIN pg_attribute a on a.attrelid = c.oid JOIN pg_attrdef d on d.adrelid = a.attrelid and d.adnum = a.attnum and a.atthasdef WHERE relkind = 'r' and a.attnum > 0 and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval' and c.oid in (select oid from reloids) LOOP n := n + 1; EXECUTE r.sql; END LOOP;

PERFORM pg_notify('seqs', n::text); END; $$;

  - [ ] data that is being loaded, if relevant
  CREATE TABLE IF NOT EXISTS `ConfigKeys` (
  `keyId` INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT 
  

  Problem is not only with this table.. problem is with more tables, every time with PRIMARY KEY.

[SergeyBrykulskii]

same issue for mssql

https://github.com/dimitri/pgloader/blob/096992acbce448290cafbaa944b5144ba97a04a6/src/pgsql/pgsql-create-schema.lisp#L410

Change || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), quote_ident(a.attname)) || ''')::regclass limit 1), 1) from only ' to || pg_get_serial_sequence(quote_ident(nspname) || '.' || quote_ident(relname), a.attname) || ''')::regclass limit 1), 1) from only ' and rebuild.

(removed quote_ident for a.attname)