UPDATE in PostgreSQL

The UPDATE statement changes column values in existing rows. You target the table, declare new values with SET, and narrow which rows are affected with WHERE. Omitting WHERE updates every row in the table — always confirm the target row set with a SELECT before running an unfiltered update in production.

Syntax

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

With RETURNING to see the new values:

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

Practical Example

Create an invoices table and update a specific row:

CREATE TABLE invoices (
  id          SERIAL PRIMARY KEY,
  client      VARCHAR(150) NOT NULL,
  amount      NUMERIC(12, 2) NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'draft',
  due_date    DATE,
  paid_at     TIMESTAMPTZ
);

INSERT INTO invoices (client, amount, status, due_date)
VALUES
  ('Brightwell Media',  4200.00, 'sent',  '2026-05-01'),
  ('Ironclad Solutions', 850.00, 'draft', '2026-05-15'),
  ('Summit Analytics',  2100.00, 'sent',  '2026-04-30');

Mark a single invoice as paid:

UPDATE invoices
SET status  = 'paid',
    paid_at = now()
WHERE id = 1;

Output:

UPDATE 1

Apply an expression-based update — increase all sent invoices by 3% for a late fee:

UPDATE invoices
SET amount = amount * 1.03
WHERE status = 'sent';

Output:

UPDATE 2

Use RETURNING to see the updated amounts immediately:

UPDATE invoices
SET status = 'overdue'
WHERE due_date < CURRENT_DATE
  AND status = 'sent'
RETURNING id, client, amount, status;

Inspect the result:

SELECT id, client, amount, status, paid_at
FROM invoices
ORDER BY id;

RETURNING Clause

RETURNING is a PostgreSQL extension that returns column values from the updated rows after the change is applied — no additional SELECT query required. The values reflect the post-update state.

UPDATE invoices
SET status = 'paid',
    paid_at = now()
WHERE id = 3
RETURNING id, client, amount AS final_amount, paid_at;

RETURNING is useful for:

• Capturing the updated primary key or computed columns in application code.
• Writing an audit log row in the same transaction without a second round-trip.
• Confirming the exact rows changed when running partial batch updates.

Testing with Vela

Before running UPDATE statements that touch large portions of a production table, use Vela database branching to create an instant branch from your live data. Run the UPDATE on the branch, check affected row counts and RETURNING output, then apply to production confidently. This is especially valuable for bulk price changes, status migrations, or backfills where a mistake is hard to undo.

Production Tips

• Always run a SELECT with the same WHERE clause first to confirm which rows will be affected and how many.
• Wrap updates in an explicit transaction when they are part of a multi-step operation — ROLLBACK undoes everything if a later step fails.
• To update rows using data from another table, use UPDATE ... FROM (update join) rather than a correlated subquery.
• RETURNING reflects new values after the update — use it for audit logging or passing updated data to application logic.
• For large batch updates, process in smaller chunks to avoid holding row locks for extended periods, which can block concurrent queries.
• Be careful with expressions that reference the current column value (e.g. amount = amount * 1.03) — verify the row set before running to avoid unintended cascading changes.