SQLite - primary keys not transfered, unique index creation fails, reset sequences fail

[x] pgloader --version

master built locally and also latest **docker** file

[x] did you test a fresh compile from the source tree?
[x] did you search for other similar issues? https://github.com/dimitri/pgloader/issues/563 https://github.com/dimitri/pgloader/issues/1038

Hi, I tried transferring data from sqlite to postgres using command

docker run --net=host --rm -v ***:/app -it locally-build-version pgloader --with "include drop" --with "foreign keys" --with "create tables" --with "create indexes" --with "quote identifiers"  --with "no truncate" --with "preserve index names" sqlite:///app/yessql.db pgsql://****

and have encountered following errors:

1/ unique index has missing quotes

2023-11-27T16:42:47.660031Z ERROR PostgreSQL Database error 42703: column "id" does not exist
QUERY: CREATE UNIQUE INDEX "idx_444511_OpenId_OpenIdScopeIndex_pkey" ON "OpenId_OpenIdScopeIndex" (Id);

I fixed this locally in "sqlite-schema.lisp" by using(clist (mapcar #'apply-identifier-case (mapcar #'coldef-name pk-fields)))) on line 163.

2/ reset sequences does not work, seems that it double quotes filed name, when it already has quotes (-with "quote identifiers"). I tried to play a little with quote_ident function but without success

Database error 42703: column ""Id"" of relation "LayerMetadataIndex" does not exist
CONTEXT: PL/pgSQL funkce inline_code_block řádek 6 na FOR nad SELECT(em)
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;
$$;

seems to be fixed by https://github.com/dimitri/pgloader/pull/1509/files

3/ Primary keys are not transferred - after migration is complete, primary keys from sqlite are not transferred into postgres. DB Columns are created, they just do not have PK assigned.

dimitri / pgloader

SQLite - primary keys not transfered, unique index creation fails, reset sequences fail #1547