postgresqlpgloader

postgres primary key not incrementing on insert (pgloader migration)

I have been trying to migrate my db to PostgreSQL 16.3 from MariaDB since I have some issue with some tables not creating a unique id on insert. Most work fine, but a few for some reason do not work properly

Here is a basic insert:

INSERT INTO brand (name, slug) VALUES ('moudle', 'moudle');

Result:

duplicate key value violates unique constraint "idx_18070_primary"

I'm new to PostgreSQL, and I'm not sure what could be going wrong and what to check.

I used pgloader and maybe forgot a setting?

LOAD DATABASE
     FROM      mysql://root:root@database/dev
     INTO      pgsql://app:pass@postgres/dev

 WITH include drop, create tables

CAST type json to jsonb
WITH prefetch rows = 100,
    preserve index names

 ALTER SCHEMA 'dev' RENAME TO 'public';

structure

EDIT: I just noticed that my problematic table for some reason does not have id_seq file as you can see from the table listing of Postbird. All other tables have it.

enter image description here

Solution

  • EDIT: I just noticed that my problematic table for some reason does not have id_seq file as you can see from the table listing of Postbird. All other tables have it.

    The problem is an auto-generate id out of sync. To find out what kind of auto-generated id is your column you could run

    select * from information_schema.columns
where table_name='brand';

    if id column has a default value (column default in the form nextval('<sequence_name>'::regclass)), then its explicitly associated with a sequence, or if it is marked as identity its implicitly associated with a sequence, to put them back on sync

    for the first case (explicit sequence)

    select setval('<name of the sequence in the default value>',
coalesce(max(id), 0) + 1, false) from brand;

    for an identity (implicit)

    select setval(pg_get_serial_sequence('brand', 'id'), 
coalesce(max(id), 0) + 1, false) from brand;

    Fiddle to test

    And to avoid this problems after migration in the first place you could also use the WITH option "reset sequences" as appears in pgloader official documentation.