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.
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.
Examine the full error message to identify which constraint is violated:
ERROR: duplicate key value violates unique constraint "users_email_key"
Detail: Key (email)=([email protected]) already exists.This tells you that the email column in the users table already contains the value "[email protected]".
Use the Supabase SQL Editor or your client to check for existing records:
SELECT * FROM users WHERE email = '[email protected]';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: '[email protected]', 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: '[email protected]', 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', '[email protected]')
.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: '[email protected]', name: 'John Doe' })
.select();
}This approach is useful when upsert logic doesn't fit your use case.
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.
email_conflict_identity_not_deletable: Cannot delete identity because of email conflict
How to fix "Cannot delete identity because of email conflict" in Supabase
mfa_challenge_expired: MFA challenge has expired
How to fix "mfa_challenge_expired: MFA challenge has expired" in Supabase
conflict: Database conflict, usually related to concurrent requests
How to fix "database conflict usually related to concurrent requests" in Supabase
phone_exists: Phone number already exists
How to fix "phone_exists" in Supabase
StorageApiError: resource_already_exists
StorageApiError: Resource already exists