How to Update and Delete Data in PostgreSQL (Guide)

If you searched for how to update data in PostgreSQL, here is the short answer: you use the UPDATE statement to change existing rows, and the DELETE statement to remove rows you no longer need. Both commands work with a WHERE clause that tells PostgreSQL exactly which rows to touch. Get the WHERE clause wrong (or forget it) and you can change or delete every row in the table by accident.

This guide walks through both statements from scratch. You do not need any prior experience with UPDATE or DELETE, but it helps if you already know how to create a table and insert rows. If you are new to that, read our guides on how to create a table in PostgreSQL and how to insert data into a PostgreSQL table first, then come back here.

The table we will use

Every example below uses a simple table called customers. Run this in psql or pgAdmin to follow along. A table is just a grid of rows and columns, like a spreadsheet, and SERIAL PRIMARY KEY means id will fill itself in automatically with a unique number for each row.

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  city TEXT,
  is_active BOOLEAN DEFAULT true
);

INSERT INTO customers (name, email, city, is_active) VALUES
  ('Amara Okafor', 'amara@example.com', 'Lagos', true),
  ('Liu Wei', 'liu@example.com', 'Austin', true),
  ('Sofia Rossi', 'sofia@example.com', 'Milan', false);

CREATE TABLE sets up the columns and their data types: TEXT for words, BOOLEAN for true/false. INSERT INTO then adds three sample rows in one go, listing the columns first and the matching values after VALUES. Each row is wrapped in its own parentheses and separated by a comma.

The UPDATE statement in PostgreSQL

UPDATE changes the values in existing rows. The basic shape is: UPDATE table_name SET column = new_value WHERE condition. The WHERE part is what limits the change to specific rows, so read it carefully every time.

UPDATE customers
SET city = 'Chicago'
WHERE id = 2;

Line by line: UPDATE customers tells PostgreSQL which table to change. SET city = 'Chicago' says which column to change and what to put in it. WHERE id = 2 limits the change to the one row where id equals 2, so only Liu Wei's city moves to Chicago, not every customer's.

You can update more than one column at once by separating them with commas, and PostgreSQL will apply all the changes to the matching rows at the same time.

UPDATE customers
SET city = 'Boston', is_active = true
WHERE email = 'sofia@example.com';

This finds the row where email matches sofia@example.com and updates both city and is_active in a single statement. Matching on email works here because email values are unique, so exactly one row changes.

The mistake that catches every beginner

If you leave out WHERE entirely, PostgreSQL updates every single row in the table, not just the one you meant.

-- Danger: this changes the city for ALL customers, not one
UPDATE customers
SET city = 'Chicago';

Because there is no WHERE clause, PostgreSQL has no reason to skip any row, so every customer's city becomes Chicago. Always add a WHERE clause when you update, unless changing every row really is what you want.

Preview your change with SELECT first

Before you run an UPDATE or DELETE, run the same WHERE condition inside a SELECT statement first. This shows you exactly which rows will be affected, with zero risk, before you commit to the change.

SELECT id, name, city
FROM customers
WHERE city = 'Austin';

This lists every row where city equals Austin, so you can check the row count and the names before you touch anything. If you are new to SELECT and WHERE, our SQL SELECT and WHERE guide covers the basics in more depth.

The DELETE statement in PostgreSQL

DELETE removes whole rows from a table. The shape is similar to UPDATE: DELETE FROM table_name WHERE condition. There is no SET clause, because you are not changing values, you are removing the row entirely.

DELETE FROM customers
WHERE is_active = false;

DELETE FROM customers targets the customers table. WHERE is_active = false limits the deletion to rows where that column is false. Only rows matching the condition are removed; every other row stays exactly as it was.

Just like UPDATE, leaving out WHERE is dangerous: DELETE FROM customers on its own would remove every row in the table, leaving it empty but still existing (the table structure stays, only the data is gone).

Seeing what changed with RETURNING

PostgreSQL has a feature many other databases lack: you can add RETURNING to an UPDATE or DELETE to see the affected rows immediately, without running a separate SELECT.

UPDATE customers
SET is_active = true
WHERE id = 3
RETURNING id, name, is_active;

This updates the row where id is 3, then RETURNING id, name, is_active prints those three columns for the row that was just changed, so you can confirm the update worked without a follow-up query.

UPDATE vs DELETE: when to use which

In real applications, teams often prefer UPDATE over DELETE for things like closed accounts, because it keeps the history instead of erasing it. Save DELETE for data you genuinely want gone, like duplicates or test rows.

Undoing a mistake with a transaction

A transaction lets you try a change and cancel it if something looks wrong, before it becomes permanent. Wrap your UPDATE or DELETE between BEGIN and COMMIT, and you get a chance to check the result first.

BEGIN;

UPDATE customers
SET city = 'Denver'
WHERE id = 1;

SELECT id, name, city FROM customers WHERE id = 1;

-- Looks correct, so make it permanent:
COMMIT;

-- If it looked wrong, you would run ROLLBACK; instead of COMMIT;

BEGIN starts the transaction, so nothing is final yet. The UPDATE runs as normal, and the SELECT right after lets you check the new value while you can still change your mind. COMMIT saves the change permanently. If the SELECT had shown something unexpected, running ROLLBACK instead would undo the UPDATE completely, as if it never happened.

• Always write and check your WHERE clause before you run UPDATE or DELETE.
• Preview the affected rows with a matching SELECT statement first.
• Use RETURNING to confirm exactly what changed, right in the same statement.
• Wrap risky changes in BEGIN and COMMIT (or ROLLBACK) while you are learning.
• Prefer UPDATE with a flag like is_active over DELETE when you want to keep history.