Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issues with Quoting Identifiers in MySQL to PostgreSQL Migration via pgloader #1617

Open
3 of 4 tasks
joejack77 opened this issue Oct 24, 2024 · 0 comments
Open
3 of 4 tasks

Comments

@joejack77
Copy link

joejack77 commented Oct 24, 2024

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant