I was recently migrating a Laravel app to deploy into Laravel Cloud Hosting. Since I was using their Serverless Postgres database option, I had to move everything from MySQL as part of the migration; I exported the SQL from TablePlus, modified it to make it PostgreSQL compatible, and imported it into the new environment database. Everything seemed to go smoothly — tables were created, data imported, and everything showed up in the admin panel with no visible issues.
Then I tried to insert a new record and was hit with this:
SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "sortable_gallery_images_pkey"
DETAIL: Key (id)=(4) already exists.At first, it didn’t make sense – I wasn’t manually setting the ID, and everything previously worked fine. After a brief moment of blind panic, I noticed that the key part of the error was that the duplicate key violated the unique constraint. PostgreSQL was trying to insert a row using an ID that already existed.
I am not the most skilled Postgres user, but after a few Google searches and consulting with ChatGPT, I figured out what the culprit was. A PostgreSQL concept that often trips up even experienced developers called sequences.
Understanding Sequences in PostgreSQL
When working with PostgreSQL, one of the lesser-known quirks developers run into is how PostgreSQL handles auto-incrementing primary keys. Unlike MySQL’s AUTO_INCREMENT, PostgreSQL uses something called a sequence behind the scenes.
🧬 What Is a Sequence?
A sequence in PostgreSQL is a special kind of object that generates a series of unique numeric values. When you define a column as SERIAL or BIGSERIAL, PostgreSQL will:
Create an integer column.
Create a sequence object (usually named something like
table_column_seq).Set the column’s default value to
nextval('table_column_seq').
Whenever a new row is inserted without a specified ID, PostgreSQL fetches the next value from the sequence to use as the id.
CREATE TABLE photos (
id SERIAL PRIMARY KEY,
name VARCHAR(255)
);Behind the scenes, this creates a sequence named photos_id_seq.
Why This Matters Using Eloquent in Laravel
When using Eloquent to insert records:
Photo::create(['name' => 'Sunset']);Eloquent will not set the id manually — it relies on PostgreSQL’s sequence to assign it. That means if the sequence is out of sync (i.e. it tries to use an ID that already exists), PostgreSQL will throw a unique constraint violation error.
This becomes especially important when importing data.
The Problem: Importing Data Breaks the Sequence
When you’re migrating an existing project or importing data into a new PostgreSQL database, it’s common to use tools like:
psql CLI to import a .sql or .dump file,
TablePlus or pgAdmin to run bulk insert queries, or
Laravel’s own DB::insert() for manual seeding.
Everything might appear to go smoothly — the data shows up, the tables look correct, and your app loads. But then when you try to insert a new record, you'l get hit with the duplicate key value violates unique constraint error.
This error tells you PostgreSQL tried to use an id that already exists. That usually means the sequence used to auto-increment your table’s primary key is no longer aligned with the actual data in the table.
Here’s why:
When you import rows (either via SQL inserts or restoring a backup), you’re often manually setting the id values.
PostgreSQL does not automatically update the sequence to match the highest ID that was inserted.
So even if your table now has IDs up to 323, the sequence might still be at 4, or even 1.
PostgreSQL doesn’t “see” the imported IDs and has no idea your sequence needs to catch up.
Diagnosing the Issue
Before rushing to fix anything, it’s a good idea to confirm that the sequence is out of sync — and understand exactly how far off it is. PostgreSQL gives us all the tools we need to inspect both the table and the sequence.
Step 1: Find the Max ID in Your Table
Let’s assume the table causing trouble is sortable_gallery_images.
Run this SQL to see the highest id currently in the table:
SELECT MAX(id) FROM sortable_gallery_images;If you’ve imported existing data, this will probably return a high number like 323.
Step 2: Find the Sequence Name
PostgreSQL automatically creates a sequence when you use SERIAL or BIGSERIAL. But if you’re not sure what it’s called, you can run:
SELECT pg_get_serial_sequence('sortable_gallery_images', 'id');This will return something like:
'sortable_gallery_images_id_seq'Step 3: Check the Sequence’s Current Value
To see what the sequence thinks the next id should be:
SELECT last_value FROM sortable_gallery_images_id_seq;If it returns something lower than the max ID in your table (e.g. 4 instead of 323), you’ve found the issue.
The Fix: Resyncing the Sequence
Once you’ve confirmed the sequence is out of sync, the fix is thankfully straightforward. The goal is to tell PostgreSQL:
“Hey, this sequence needs to pick up where the data left off.”
This way, the next time you use Eloquent to insert a record, PostgreSQL will generate an id that doesn’t collide with existing ones.
Let’s say your max id is 323, and your sequence is sortable_gallery_images_id_seq. To bring them in sync:
SELECT setval('sortable_gallery_images_id_seq', 323);Or, if you want the next inserted row to be 324, explicitly:
SELECT setval('sortable_gallery_images_id_seq', 324, false);🧠 The third argument false tells Postgres: “The sequence hasn’t been used yet, so return this value next time.”
Wrapping Up
Sequence mismatch errors in PostgreSQL are one of those subtle issues that can sneak up on you, especially during a database migration or when importing legacy data into a new environment. They’re easy to overlook, but once they appear, they can break otherwise stable functionality in unexpected ways.
If you're setting up a new environment using PostgreSQL, it’s worth taking the time to understand how sequences behave and where they can drift out of sync. Whether you’re importing data manually, restoring backups, or running seeders that include explicit IDs, a quick sequence reset can save you hours of debugging later.
The fix is simple once you know it, and even better, it’s easy to script and include as part of your deployment or post-import process.
If you’re working on a similar migration or just ran into this problem yourself, I hope this helped.