PostgreSQL UPSERT Tutorial: Master INSERT ON CONFLICT with Complete Examples

PostgreSQL’s UPSERT functionality, implemented through INSERT ... ON CONFLICT, is one of the most powerful features for handling data insertion conflicts. Whether you’re dealing with user updates, inventory management, or any scenario where you need to insert new records or update existing ones, mastering PostgreSQL UPSERT operations is essential for efficient database programming.

In this comprehensive tutorial, we’ll explore everything you need to know about PostgreSQL UPSERT, from basic syntax to advanced use cases with practical examples you can implement immediately.

Understanding PostgreSQL UPSERT

UPSERT is a portmanteau of “UPDATE” and “INSERT” that describes an operation that either inserts a new row or updates an existing one based on a conflict condition. PostgreSQL implements this through the INSERT ... ON CONFLICT statement, which was introduced in version 9.5.

The basic concept is simple: when you try to insert a row that would violate a unique constraint (like a primary key or unique index), PostgreSQL can either update the existing row or do nothing, depending on how you configure the statement.

Why Use UPSERT?

UPSERT operations solve several common database challenges:

• Atomic Operations: Eliminates race conditions between separate INSERT and UPDATE statements
• Performance: Reduces round trips to the database
• Simplicity: Handles complex logic in a single statement
• Data Integrity: Prevents duplicate key errors while maintaining consistency

Basic UPSERT Syntax

The fundamental syntax for PostgreSQL UPSERT follows this pattern:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO UPDATE SET column1 = EXCLUDED.column1, column2 = EXCLUDED.column2;

Let’s break down each component:

• INSERT INTO: Standard insert statement
• ON CONFLICT: Specifies which constraint violation triggers the conflict resolution
• DO UPDATE SET: Defines what to do when a conflict occurs
• EXCLUDED: References the values that would have been inserted

Setting Up Sample Data

Before diving into examples, let’s create a sample table to work with:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    last_login TIMESTAMP,
    login_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Insert some initial data
INSERT INTO users (email, name, last_login, login_count) VALUES
('john@example.com', 'John Doe', '2024-01-01 10:00:00', 5),
('jane@example.com', 'Jane Smith', '2024-01-02 14:30:00', 3);

Basic UPSERT Examples

Simple INSERT or UPDATE

Here’s a basic example that inserts a new user or updates an existing one:

INSERT INTO users (email, name, last_login, login_count)
VALUES ('john@example.com', 'John Updated', NOW(), 10)
ON CONFLICT (email)
DO UPDATE SET 
    name = EXCLUDED.name,
    last_login = EXCLUDED.last_login,
    login_count = EXCLUDED.login_count,
    updated_at = NOW();

In this example, if a user with email ‘john@example.com’ already exists, PostgreSQL will update their information. If not, it will insert a new record.

Using DO NOTHING

Sometimes you want to ignore conflicts entirely:

INSERT INTO users (email, name, last_login, login_count)
VALUES ('jane@example.com', 'Jane Duplicate', NOW(), 1)
ON CONFLICT (email)
DO NOTHING;

This statement will silently ignore the insert if the email already exists, making it useful for preventing duplicate entries without throwing errors.

Advanced UPSERT Techniques

Conditional Updates with WHERE Clauses

You can add conditions to your UPSERT operations to make them more sophisticated:

INSERT INTO users (email, name, last_login, login_count)
VALUES ('john@example.com', 'John Conditional', NOW(), 15)
ON CONFLICT (email)
DO UPDATE SET 
    name = EXCLUDED.name,
    last_login = EXCLUDED.last_login,
    login_count = EXCLUDED.login_count,
    updated_at = NOW()
WHERE users.login_count < EXCLUDED.login_count;

This example only updates the user if the new login count is higher than the existing one, preventing data regression.

Using Existing Values in Updates

You can reference both new (EXCLUDED) and existing values in your update statements:

INSERT INTO users (email, name, last_login, login_count)
VALUES ('john@example.com', 'John Increment', NOW(), 1)
ON CONFLICT (email)
DO UPDATE SET 
    name = CASE 
        WHEN LENGTH(EXCLUDED.name) > LENGTH(users.name) 
        THEN EXCLUDED.name 
        ELSE users.name 
    END,
    last_login = EXCLUDED.last_login,
    login_count = users.login_count + EXCLUDED.login_count,
    updated_at = NOW();

This example increments the login count and keeps the longer name between the existing and new values.

Working with Multiple Columns and Constraints

Let's create a more complex example with composite unique constraints:

CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    device_type VARCHAR(50) NOT NULL,
    session_token VARCHAR(255) NOT NULL,
    last_activity TIMESTAMP DEFAULT NOW(),
    UNIQUE(user_id, device_type)
);

Now we can use UPSERT with the composite constraint:

INSERT INTO user_sessions (user_id, device_type, session_token, last_activity)
VALUES (1, 'mobile', 'abc123token', NOW())
ON CONFLICT (user_id, device_type)
DO UPDATE SET 
    session_token = EXCLUDED.session_token,
    last_activity = EXCLUDED.last_activity;

Bulk UPSERT Operations

PostgreSQL UPSERT works efficiently with multiple rows:

INSERT INTO users (email, name, last_login, login_count) VALUES
('alice@example.com', 'Alice Johnson', NOW(), 2),
('bob@example.com', 'Bob Wilson', NOW(), 4),
('john@example.com', 'John Updated Again', NOW(), 20)
ON CONFLICT (email)
DO UPDATE SET 
    name = EXCLUDED.name,
    last_login = EXCLUDED.last_login,
    login_count = users.login_count + EXCLUDED.login_count,
    updated_at = NOW();

This approach is much more efficient than handling each row individually, especially for large datasets.

UPSERT with RETURNING Clause

You can combine UPSERT with the RETURNING clause to get information about the affected rows:

INSERT INTO users (email, name, last_login, login_count)
VALUES ('charlie@example.com', 'Charlie Brown', NOW(), 1)
ON CONFLICT (email)
DO UPDATE SET 
    name = EXCLUDED.name,
    last_login = EXCLUDED.last_login,
    login_count = users.login_count + 1,
    updated_at = NOW()
RETURNING id, email, name, login_count, 
    CASE 
        WHEN xmax = 0 THEN 'INSERTED'
        ELSE 'UPDATED'
    END as action;

The xmax = 0 condition helps identify whether a row was inserted or updated, which can be valuable for logging and analytics.

Common UPSERT Patterns and Use Cases

User Profile Management

-- Update user profile or create if doesn't exist
INSERT INTO user_profiles (user_id, bio, avatar_url, preferences)
VALUES ($1, $2, $3, $4)
ON CONFLICT (user_id)
DO UPDATE SET 
    bio = COALESCE(EXCLUDED.bio, user_profiles.bio),
    avatar_url = COALESCE(EXCLUDED.avatar_url, user_profiles.avatar_url),
    preferences = user_profiles.preferences || EXCLUDED.preferences,
    updated_at = NOW();

Inventory Management

-- Update stock levels or add new products
INSERT INTO inventory (product_id, quantity, last_updated)
VALUES ($1, $2, NOW())
ON CONFLICT (product_id)
DO UPDATE SET 
    quantity = inventory.quantity + EXCLUDED.quantity,
    last_updated = NOW()
WHERE inventory.quantity + EXCLUDED.quantity >= 0;

Performance Considerations

When working with UPSERT operations, keep these performance tips in mind:

• Index Strategy: Ensure proper indexes on conflict columns
• Batch Operations: Use multi-row UPSERT for better performance
• Lock Contention: Be aware of potential locking issues in high-concurrency scenarios
• Statistics: Keep table statistics updated for optimal query planning

Monitoring UPSERT Performance

-- Check for lock waits related to UPSERT operations
SELECT 
    query,
    wait_event_type,
    wait_event,
    state,
    query_start
FROM pg_stat_activity 
WHERE query LIKE '%ON CONFLICT%' 
AND state != 'idle';

Error Handling and Best Practices

Handling Non-Unique Constraint Violations

UPSERT only works with unique constraints. For other constraint violations, you'll need different approaches:

-- This will still raise an error if there are other constraint violations
INSERT INTO users (email, name, last_login, login_count)
VALUES ('invalid-email', 'Test User', NOW(), -1)  -- Assuming login_count has CHECK >= 0
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

Best Practices

• Always specify which constraint triggers the conflict
• Use meaningful column updates in DO UPDATE SET
• Consider using COALESCE for optional fields
• Test UPSERT operations thoroughly with concurrent access
• Monitor performance impact in production environments

Conclusion

PostgreSQL's UPSERT functionality through INSERT ... ON CONFLICT provides a powerful, atomic way to handle data conflicts elegantly. Whether you're managing user profiles, inventory systems, or any application requiring robust data handling, mastering these techniques will significantly improve your database operations' reliability and performance.

The examples in this tutorial demonstrate practical patterns you can adapt to your specific use cases. Remember to always test your UPSERT operations thoroughly, especially in high-concurrency environments, and monitor their performance impact on your PostgreSQL database.

By implementing these PostgreSQL UPSERT techniques, you'll write more robust applications that handle data conflicts gracefully while maintaining excellent performance characteristics.