"duplicate key value violates unique constraint" error when adding a new field

[gvocale]

Bug report

Describe the bug

Sometimes, when adding a new field to a component, the following error appears.

Steps to reproduce the behavior

1. I add a field to a short text field to a component
2. Push save
3. See error

I have tried deleting the created field, running rm -rf .cache and yarn dev again, but the error keeps happening and can't start yarn dev successfully.

Finally closing and reopening terminal, running yarn dev again, fixed it. Although adding again the field textColor reproduce again the issue.

Expected behavior

A clear and concise description of what you expected to happen.

Screenshots

Code snippets

Error

{
  method: 'insert',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [
    'plugin::content-manager.explorer.update',
    '[]',
    2022-02-11T16:12:20.526Z,
    '{"fields":["title","slug","wpDatabaseId","type","featuredImage","description","excerpt","expertise","logo","makers","makersList","products","events","exhibits","addresses.address1","addresses.address2","addresses.city","addresses.name","addresses.state","addresses.postcode","addresses.country","addresses.phone","links.website","links.instagramHandle","rows","hero.title","hero.logo","hero.image","hero.backgroundColor","hero.matterport.image","hero.matterport.matterTag","hero.matterport.modelId"]}',
    'api::gallery.gallery',
    2022-02-11T16:12:20.526Z
  ],
  __knexQueryUid: 'P3aBnXksM5ayxxL5WG2JZ',
  sql: 'insert into "admin_permissions" ("action", "conditions", "created_at", "properties", "subject", "updated_at") values (?, ?, ?, ?, ?, ?) returning "id"',
  returning: 'id'
}
[2022-02-11 11:12:20.529] debug: ⛔️ Server wasn't able to start properly.
[2022-02-11 11:12:20.529] error: insert into "admin_permissions" ("action", "conditions", "created_at", "properties", "subject", "updated_at") values ($1, $2, $3, $4, $5, $6) returning "id" - duplicate key value violates unique constraint "admin_permissions_pkey"
error: insert into "admin_permissions" ("action", "conditions", "created_at", "properties", "subject", "updated_at") values ($1, $2, $3, $4, $5, $6) returning "id" - duplicate key value violates unique constraint "admin_permissions_pkey"
    at Parser.parseErrorMessage (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:394:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:312:12)
    at readableAddChunk (node:internal/streams/readable:287:9)
    at Socket.Readable.push (node:internal/streams/readable:226:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
error Command failed with exit code 1.

System

• Node.js version: v16.6.0
• NPM version: 7.19.1
• Strapi version: 4.0.7
• Database: Postgres
• Operating system: MacOs

Additional context

Add any other context about the problem here.

[derrickmehaffy]

What version of PostgreSQL are you using as this seems kinda related to #11723

[gvocale]

I'm using postgres (PostgreSQL) 14.1

[gvocale]

Running in the same issue also when I try to change permission for public user on one single type:

[2022-02-15 12:50:43.440] error: insert into "up_permissions" ("action", "created_at", "updated_at") values ($1, $2, $3) returning "id" - duplicate key value violates unique constraint "up_permissions_pkey"
error: insert into "up_permissions" ("action", "created_at", "updated_at") values ($1, $2, $3) returning "id" - duplicate key value violates unique constraint "up_permissions_pkey"
    at Parser.parseErrorMessage (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/gvocale/Sites/strapi/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:394:28)
    at Socket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:312:12)
    at readableAddChunk (node:internal/streams/readable:287:9)
    at Socket.Readable.push (node:internal/streams/readable:226:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

[gvocale]

By the way just tried version Strapi 4.0.8 and the issue persists.

[ibnlanre]

• Open Terminal (if you're on Mac) or CMD (if you're using Windows)

• If you have psql installed, then login
  psql -U <db_user>

• Select the database you have been working with. If you don't know the name, use \dl to list available DBs
  \c <db_name>

• Run the below code. You may want to make a backup if you please.
  SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"<table_name>"', '<column_name>')), (SELECT MAX(<column_name>) FROM <table_name>) + 1, FALSE);

I should also add that it is not a Strapi bug. It's rather a PostgreSQL bug by design, that occurs after dump restores or mass imports. It occurs when the primary key sequence in the table you're working with goes out of sync - that is, when the current id_seq is less or equal to the max value. So, you technically would need to manually increment it (with the code above).

[gvocale]

@ibnlanre that helped! Thank you so much!

[derrickmehaffy]

Marking as closed as it was made clear this isn't a Strapi bug but a PostgreSQL issue on imports.

[Eli-Nathan]

@ibnlanre's answer did the trick for me.
After trying to import some data, it hit a conflict and stopped the import leaving some IDs in a bad state in the DB.

I had to tweak it a bunch so if anyone is hitting repeated errors such as:

column "X" does not exist

Then try wrapping the table name in single quotes as well as doubles.

My table was called facilities and the column in question was id so my query had to be:

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"facilities"', 'id')), (SELECT MAX(id) FROM facilities) + 1, FALSE);

[rokal]

@Eli-Nathan I got the same error and it seems that the fix is updating the table sequence.
alter sequence <sequence_table probably facilities_id_seq> restart with <the-max-value>; fixed my issue.
You might want to look into your data to know what was the last id inserted.

[everythinginjs]

@ibnlanre and @derrickmehaffy Hello, I am wondering it is the same issue I am facing now, after 216 products imports using import/export strapi plugin. Should I run the query you said after every mass imports?!!! that would be so frustrating if so. does switching to MySQL fix the issue? thanks a lot for any response.

My environment:
Strapi version: v4.1.9
Node version v14.19.3
strapi-plugin-import-export-entries 1.6.9
AWS RDS Postgres 14.3

[hoang-innomize]

Not sure why we close this issue, but it seems this is an issue of the native Strapi entity service/query. After inserting the record with the id value into the database, it should automatically increase the sequence. It worked properly on the previous version, but after we upgrade to the latest version this issue is occurs.

The code below is used to insert a new record into the database

await strapi.entityService.create(api::${model}.${model}, { data: entry, });

I think the root cause might be because the id field that we paste into the entry object has a value, so the sequent didn't get increased. But we need to predefine the id value in order to we can associate with other models when seeding initial data.

Worked version: 3.6.8
The current version that not working: 4.11.1

[jakubjanousek]

We are also seeing this error on multiple tables, despite not doing any importing. After implementing the fix in the db it works for a bit but then the sequence gets out of sync again somehow

[IvoNet]

We see the same problem. For us this happens after we used a migration file to fill a table with initial data. Now we can edit existing items but not create new ones with exactly this the above problem.

[arashkevich25]

The same issue during migration from v4.5.1 to v4.6.1

[IvoNet]

Please this issue is NOT resolved.

[mdxprograms]

@derrickmehaffy will this be merged in soon if it hasn't? I keep getting this after creating a new collection type locally and pushing to production. Granted I'm using sqliite locally and postgres in production (which is default).

[andreasaker]

@ibnlanre Thank you!
Had this problem after transferring data from Strapi 3 to 4. ibnlanre:s solution solved it for me, hope there is a way to fix it without meddling with postgresql in the future.

[donkeycomm]

This worked for me, but of course you empty the table completely;
TRUNCATE TABLE admin_permissions CASCADE;