How to Update Data in PostgreSQL

Data changes over time — a customer updates their email, an order’s status moves from “pending” to “shipped,” a product’s price changes. PostgreSQL’s UPDATE statement is how you modify existing rows, and while the syntax is short, there’s real nuance in how to use it safely, efficiently, and in combination with other tables. This guide covers the full picture, with an emphasis on avoiding the mistakes that cause the most damage.

Basic UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

A concrete example:

UPDATE customers
SET email = 'new.email@example.com'
WHERE id = 5;

This changes the email column for the single row where id equals 5. That WHERE clause is doing critical work — without it, PostgreSQL updates every row in the table, which is one of the most common and costly mistakes in SQL.

Why the WHERE Clause Matters So Much

Consider this statement, missing its WHERE clause:

UPDATE customers
SET status = 'inactive';

This sets every single customer in the entire table to 'inactive', regardless of their actual status. PostgreSQL won’t warn you or ask for confirmation — it will simply do exactly what you told it to do, instantly, across every row.

Before running any UPDATE, it’s a genuinely good habit to first run the equivalent SELECT with the same WHERE clause, to confirm exactly which rows will be affected:

SELECT * FROM customers WHERE id = 5;

Only after confirming that returns the expected row (or rows) should you run the actual UPDATE.

Updating Multiple Columns at Once

UPDATE orders
SET status = 'shipped', updated_at = NOW()
WHERE id = 101;

Any number of columns can be updated in a single statement, separated by commas.

Updating Multiple Rows

The WHERE clause can match any number of rows, not just one:

UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending' AND created_at < NOW() - INTERVAL '30 days';

This cancels every order that’s still pending after 30 days — a common pattern for cleaning up abandoned orders.

Using Expressions and Existing Column Values

The new value assigned to a column doesn’t have to be a literal — it can reference the row’s existing values or use functions and expressions.

UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';

This increases the price of every product in the electronics category by 10%, calculated relative to each row’s own current price.

UPDATE inventory
SET stock_count = stock_count - 1
WHERE product_id = 42;

This decrements a stock count by one — a very common pattern in inventory systems, and importantly, this kind of relative update is safe from race conditions in a way that reading the value into application code, subtracting one, and writing it back is not.

Updating with Data from Another Table

Often you need to update a table based on values found in a related table. PostgreSQL supports this with UPDATE ... FROM:

UPDATE orders
SET customer_name = customers.name
FROM customers
WHERE orders.customer_id = customers.id;

This pulls the name value from the customers table and copies it into a customer_name column on orders, matching rows by customer_id. This pattern is common when denormalizing data for performance reasons, or fixing data that’s fallen out of sync.

Here’s a slightly more elaborate example — updating order totals based on a sum from a related order_items table:

UPDATE orders
SET total_amount = subtotal.total
FROM (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
) AS subtotal
WHERE orders.id = subtotal.order_id;

This recalculates and updates total_amount for every order based on the current sum of its line items.

Returning Updated Data with RETURNING

Just like INSERT, UPDATE supports a RETURNING clause, which gives you back the updated rows without needing a follow-up SELECT:

UPDATE customers
SET email = 'updated@example.com'
WHERE id = 5
RETURNING id, email, updated_at;

This is particularly useful in application code, where you often need to confirm exactly what changed and display the new state to a user immediately after the update.

Conditional Updates with CASE

Sometimes different rows need different new values depending on their current state. A CASE expression handles this cleanly within a single UPDATE:

UPDATE products
SET price = CASE
    WHEN category = 'electronics' THEN price * 1.10
    WHEN category = 'clothing' THEN price * 1.05
    ELSE price
END;

This applies different percentage increases depending on category, all in one pass over the table, rather than running separate UPDATE statements for each category.

Updating JSON and JSONB Columns

For JSONB columns, PostgreSQL provides functions to update specific keys without overwriting the entire value.

UPDATE user_settings
SET preferences = jsonb_set(preferences, '{theme}', '"dark"')
WHERE user_id = 10;

This updates just the theme key inside a JSONB column, leaving every other key in the JSON object untouched.

To merge in multiple new keys at once:

UPDATE user_settings
SET preferences = preferences || '{"theme": "dark", "notifications": true}'::jsonb
WHERE user_id = 10;

The || operator here merges the existing JSONB object with the new one, with keys in the new object overwriting matching keys in the old one.

Updating Array Columns

To append a value to an array column:

UPDATE products
SET tags = array_append(tags, 'on-sale')
WHERE id = 42;

To remove a specific value from an array:

UPDATE products
SET tags = array_remove(tags, 'discontinued')
WHERE id = 42;

Limiting the Impact: Updating with a Subquery for Precision

Sometimes a WHERE condition needs to be based on aggregated or filtered results from elsewhere. Subqueries work well here:

UPDATE customers
SET status = 'vip'
WHERE id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 1000
);

This promotes any customer whose total order value exceeds $1,000 to 'vip' status, based on a calculation across their full order history.

Practical Examples

Marking an order as shipped

UPDATE orders
SET status = 'shipped', shipped_at = NOW()
WHERE id = 205;

Applying a discount to all products in a category

UPDATE products
SET price = price * 0.85
WHERE category = 'seasonal';

Deactivating users who haven’t logged in for a year

UPDATE users
SET is_active = false
WHERE last_login_at < NOW() - INTERVAL '1 year';

Syncing denormalized data after a related record changes

UPDATE orders
SET customer_email = customers.email
FROM customers
WHERE orders.customer_id = customers.id
  AND orders.customer_email IS DISTINCT FROM customers.email;

Note the IS DISTINCT FROM check here — it ensures the update only touches rows where the value has actually changed, avoiding unnecessary writes (and unnecessary trigger executions) on rows that are already correct.

Common Use Cases

Status transitions. Orders moving through pending → shipped → delivered, tickets moving through open → in progress → resolved — these state machine-style updates are some of the most common UPDATE patterns in real applications.

Denormalization and caching. Copying frequently-read values from related tables into a parent table to avoid expensive joins on every read, refreshed via UPDATE whenever the source data changes.

Bulk maintenance operations. Deactivating stale accounts, applying price changes, recalculating totals — operations that touch many rows at once based on a shared condition.

User-driven edits. Someone updating their profile, changing a setting, or editing a record through an application interface — typically single-row updates identified by primary key.

Troubleshooting Common Errors

Accidentally updated every row. This is the big one — if you ran an UPDATE without a WHERE clause by mistake, your only recovery options are restoring from a backup or, if you’re inside an uncommitted transaction, running ROLLBACK immediately before committing.

ERROR: column "column_name" does not exist. Typo in the column name, or referencing a column from the wrong table when using UPDATE ... FROM. Double-check with \d table_name.

ERROR: update or delete on table violates foreign key constraint. This typically happens with DELETE, but can occur with UPDATE too if you’re changing a primary key value that other tables reference. Consider whether the foreign key should cascade, or whether the primary key should really be changing at all.

Update runs but affects zero rows. Your WHERE clause didn’t match anything. Run the equivalent SELECT first to confirm what you expect to match actually exists.

Deadlocks on concurrent updates. When multiple transactions try to update overlapping rows in different orders, PostgreSQL may detect a deadlock and abort one of the transactions. Keep transactions short, and where possible, always acquire locks on rows in a consistent order across your application.

Best Practices

• Always run the equivalent SELECT first to confirm which rows will be affected before running the real UPDATE, especially for anything touching more than a handful of rows.
• Wrap risky updates in a transaction so you can inspect the result and ROLLBACK if something looks wrong, before committing:

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE status = 'pending' AND created_at < NOW() - INTERVAL '30 days';
-- check the row count and results look right, then:
COMMIT;
-- or, if something looks wrong:
ROLLBACK;

• Use RETURNING to confirm exactly what changed, rather than assuming based on the row count alone.
• Avoid unnecessary writes by adding IS DISTINCT FROM checks when updating based on synced or denormalized data, which reduces write load and avoids triggering unrelated logic like updated_at triggers on unchanged rows.
• Index columns used in your WHERE clauses for large tables — an UPDATE still needs to find the matching rows first, and a full table scan on a large table is slow.
• Be cautious with bulk updates on production data during peak hours — large updates can hold locks and impact application performance; consider running them during low-traffic windows or in smaller batches.

Updating with LIMIT-Like Behavior

PostgreSQL’s UPDATE doesn’t support a direct LIMIT clause the way SELECT does, which surprises people coming from other databases. If you need to update only a specific number of rows (say, processing a queue in small batches), the workaround is a subquery with ctid or a unique key:

UPDATE jobs
SET status = 'processing'
WHERE id IN (
    SELECT id FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 100
    FOR UPDATE SKIP LOCKED
);

The FOR UPDATE SKIP LOCKED combination here is particularly useful in queue-processing systems with multiple concurrent workers — it locks the selected rows and skips any that another worker has already locked, allowing multiple processes to pull from the same table without stepping on each other or waiting on each other’s locks.

Optimistic Locking with UPDATE

In systems where multiple users might edit the same record concurrently, a common pattern to avoid silently overwriting someone else’s changes is optimistic locking using a version column:

UPDATE documents
SET content = 'Updated content here', version = version + 1
WHERE id = 10 AND version = 3;

Check the number of affected rows after running this. If it’s zero, that means the version had already changed since the record was last read (someone else updated it first), and your application should handle that case — typically by reloading the current data and asking the user to reconcile the conflict, rather than blindly overwriting.

Updating Large Tables Without Long Lock Times

A full-table UPDATE on a very large table can hold locks and generate a large amount of write-ahead log activity all at once, which can affect other queries running concurrently. For large-scale updates, batching is a common mitigation:

DO $$
DECLARE
    rows_updated INTEGER;
BEGIN
    LOOP
        UPDATE orders
        SET archived = true
        WHERE id IN (
            SELECT id FROM orders
            WHERE archived = false AND created_at < NOW() - INTERVAL '2 years'
            LIMIT 5000
        );
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;
        COMMIT;
    END LOOP;
END $$;

This processes the update in chunks of 5,000 rows at a time, committing after each batch, which keeps individual transactions small and avoids holding locks or accumulating dead tuples across the entire table in one enormous operation. Note that COMMIT inside a DO block like this requires PostgreSQL to be running the block outside of an explicit surrounding transaction, and this specific pattern is more commonly implemented as a proper procedure or handled in application code with explicit transaction control.

Updating with Triggers in Play

If a table has BEFORE UPDATE or AFTER UPDATE triggers attached (commonly used to maintain an updated_at timestamp automatically, or to log changes to an audit table), your UPDATE statement will fire them automatically without needing anything extra in the statement itself. A very common trigger pattern:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

With this trigger in place, any UPDATE on orders automatically refreshes updated_at, without the application needing to set it explicitly in every update statement — a common and genuinely convenient pattern.

Updating Columns Based on a Calculation Across Rows

Sometimes an update needs to reference an aggregate calculated across other rows, not just the row being updated:

UPDATE products p
SET is_low_stock = (
    SELECT COALESCE(SUM(quantity), 0) < 10
    FROM inventory i
    WHERE i.product_id = p.id
);

This recalculates a flag for every product based on a live aggregate from a related inventory table, in a single statement rather than looping through products in application code.

Frequently Asked Questions

Why doesn’t my UPDATE show any error but also doesn’t seem to change anything? Check the row count reported after the statement runs (UPDATE 0 means nothing matched your WHERE clause). This usually means the condition didn’t match any rows — confirm with an equivalent SELECT using the same WHERE clause.

Can I update a column that’s part of a primary key? Yes, though it requires care — if other tables reference that column via a foreign key without ON UPDATE CASCADE, the update will fail with a foreign key violation. Adding ON UPDATE CASCADE to the foreign key definition allows dependent rows to automatically follow the change.

Does UPDATE create a new row internally, or modify the existing one in place? Internally, PostgreSQL’s MVCC (multi-version concurrency control) architecture means an UPDATE actually creates a new row version and marks the old one as no longer current, rather than modifying data in place. This is why frequent updates on a table can lead to table bloat over time, and why VACUUM (usually handled automatically by autovacuum) is an important routine maintenance process.

How can I see exactly what a large UPDATE will change before running it? Wrap it in a transaction, run it, review the RETURNING output or row count, and either COMMIT or ROLLBACK based on what you see — this lets you inspect real results without permanently committing until you’re confident.

Wrapping Up

UPDATE is a short, simple-looking command that carries real responsibility — it’s the difference between fixing one customer’s email and accidentally wiping out a status field across your entire customer base. The habit of testing your WHERE clause with a SELECT first, wrapping meaningful updates in transactions you can roll back, and batching large-scale updates on sizable tables will save you from the vast majority of update-related mistakes and performance headaches. From here, the natural next step is learning to remove data entirely with DELETE, which carries many of the same considerations.