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';
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.
2
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.
4