How to fix 23505: Unique violation in Supabase
This PostgreSQL error occurs when attempting to insert a duplicate value into a column with a unique constraint in your Supabase database. The constraint could be a primary key, unique index, or explicitly defined unique column.
Quick fix
Fixes 23505
ERROR: duplicate key value violates unique constraint "users_email_key"
Detail: Key (email)=(user@example.com) already exists.- Identify the conflicting column and value
- Query the database to verify the duplicate
- Handle duplicates in application code
- Use upsert for update-or-insert operations
ERROR: duplicate key value violates unique constraint "users_email_key"Detail: Key (email)=(user@example.com) already exists.23505: Unique violation
What this error means
Error 23505 is a PostgreSQL error code indicating a unique constraint violation in your Supabase database. This error is triggered when you attempt to insert or update a record with a value that already exists in a column that has been defined with a unique constraint. The error occurs at the database level and prevents data that would violate uniqueness rules from being stored. Unique constraints ensure data integrity by preventing duplicate entries in columns that should contain distinct values, such as usernames, email addresses, or ID fields. This is a fundamental database integrity mechanism that protects your data from logical inconsistencies and ensures that primary keys and unique indexes maintain their purpose of uniquely identifying records.
Typical symptoms
- Insert operations fail with "duplicate key value violates unique constraint" message
- Error code 23505 appears in application logs or database error responses
- Upsert operations fail when multiple unique constraints conflict
- Schema migrations fail with duplicate key errors on migration tracking tables
- Auto-incremented ID columns produce duplicate values after data imports
- Application crashes or throws unhandled exceptions on database write operations
Common causes
- Attempting to insert a record with a value that already exists in a unique or primary key column
- Database sequence is out of sync after bulk data imports or manual ID insertions
- Application logic fails to check for existing records before inserting new ones
- Concurrent insert operations attempting to create the same unique value simultaneously
- Upsert operations using incorrect conflict resolution columns
- Migration scripts attempting to insert duplicate entries into schema tracking tables
- Missing uniqueness validation in application code before database operations
How to fix "23505: Unique violation"
Examine the full error message to identify which constraint is violated:
ERROR: duplicate key value violates unique constraint "users_email_key"
Detail: Key (email)=(user@example.com) already exists.This tells you that the email column in the users table already contains the value "user@example.com".
Use the Supabase SQL Editor or your client to check for existing records:
SELECT * FROM users WHERE email = 'user@example.com';This confirms whether the duplicate value exists and helps you understand the data state.
Add proper error handling for unique constraint violations in your application:
const { data, error } = await supabase
.from('users')
.insert({ email: 'user@example.com', name: 'John Doe' })
.select();
if (error) {
// Check for unique violation error code
if (error.code === '23505') {
console.error('Email already exists:', error.message);
// Handle duplicate appropriately (show user error, use update instead, etc.)
} else {
console.error('Database error:', error);
}
}This prevents unhandled exceptions and provides better user feedback.
If you want to update existing records or insert new ones, use Supabase's upsert functionality:
const { data, error } = await supabase
.from('users')
.upsert(
{ email: 'user@example.com', name: 'John Doe', updated_at: new Date() },
{ onConflict: 'email' } // Specify the unique column
)
.select();The onConflict parameter must reference a column that has a unique constraint. This will update the existing record instead of failing.
If you're getting duplicate key errors on primary key columns after data imports, your sequence may be out of sync:
First, check the current state:
-- Get the maximum ID currently in the table
SELECT MAX(id) FROM users;
-- Get the next value the sequence will generate
SELECT nextval(pg_get_serial_sequence('users', 'id'));If the sequence value is less than or equal to the maximum ID, reset it:
-- Reset sequence to the highest ID + 1
SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users) + 1);This ensures future inserts won't conflict with existing IDs.
Prevent duplicates by checking for existence before inserting:
// Check if email already exists
const { data: existing } = await supabase
.from('users')
.select('id')
.eq('email', 'user@example.com')
.single();
if (existing) {
console.log('User already exists');
// Handle accordingly - update, skip, or show error
} else {
// Safe to insert
const { data, error } = await supabase
.from('users')
.insert({ email: 'user@example.com', name: 'John Doe' })
.select();
}This approach is useful when upsert logic doesn't fit your use case.
Advanced notes
Sequence Synchronization After Migrations: When migrating data between environments or performing bulk imports, sequences for serial/identity columns often become desynchronized. This is because bulk imports typically specify explicit ID values, which doesn't advance the sequence. Always reset sequences after imports using setval().
Multiple Unique Constraints: If a table has multiple unique constraints (e.g., unique email AND unique username), ensure your upsert onConflict parameter references all relevant columns or uses a named constraint: onConflict: 'users_email_username_key'.
Race Conditions: In high-concurrency scenarios, two simultaneous requests might both check for duplicates, find none, and then both attempt to insert, causing one to fail. Use database transactions or unique constraints at the schema level rather than relying solely on application-level checks.
Schema Migrations Table: The error duplicate key value violates unique constraint "schema_migrations_pkey" specifically occurs with Supabase CLI migrations when migration tracking gets corrupted. This usually requires manually cleaning the supabase_migrations.schema_migrations table or resetting your local database.
PostgreSQL Constraint Types: Supabase uses PostgreSQL, which supports several constraint types that can trigger 23505 errors: PRIMARY KEY, UNIQUE, and EXCLUDE constraints. Use \d+ table_name in the SQL editor to view all constraints on a table.
Error Handling in Different Languages: TypeScript/JavaScript clients receive the error in the error.code property. Python clients (using supabase-py) expose it through exception attributes. Always check your client library's documentation for proper error handling patterns.
Frequently asked questions
- What does "23505" mean?
- Error 23505 is a PostgreSQL error code indicating a unique constraint violation in your Supabase database. This error is triggered when you attempt to insert or update a record with a value that already exists in a column that has been defined with a unique constraint. The error occurs at the database level and prevents data that would violate uniqueness rules from being stored.
- How do I fix "23505"?
- This PostgreSQL error occurs when attempting to insert a duplicate value into a column with a unique constraint in your Supabase database. The constraint could be a primary key, unique index, or explicitly defined unique column.
- What causes "23505"?
- Attempting to insert a record with a value that already exists in a unique or primary key column Database sequence is out of sync after bulk data imports or manual ID insertions Application logic fails to check for existing records before inserting new ones
- Is the "23505" error serious?
- It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.