[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.
[ ] pgloader --version
[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; $$;