Edit PostgreSQL data

Editing PostgreSQL data without breaking anything.

Editing rows is easy. Editing the rows you meant to edit, and only those, is the part worth learning. Everything on this page is about making the change visible before it is permanent.

Short answer: Run every data change inside an explicit transaction. Write the WHERE clause as a SELECT first, check the count, then convert it to an UPDATE, check the reported row count matches, and only then COMMIT. In a GUI client, use one that stages changes for review rather than writing each cell as you leave it.

Download PgDeck View pricing

PgDeck PostgreSQL desktop client with schema navigation, table filtering, row editing, and SQL tools

Why developers use PgDeck

PgDeck is shaped around PostgreSQL tasks that happen every day: connect, inspect, filter, edit, query, manage structure, copy, and export.

BEGIN before any data change makes it reversible

The reported row count is a free check that your WHERE is right

RETURNING shows exactly which rows changed

NULL and empty string are different values with different behaviour

UPDATE FROM edits one table using values from another

Secure connections

Save PostgreSQL connection details with encrypted passwords, SSL settings, URL import, testing, search, edit, and delete actions.

Schema and data grid

Browse schemas and tables, search the table list, pin favorites, filter rows, sort columns, resize columns, and paginate results.

SQL workspace

Run SELECT and non-SELECT statements from multi-tab SQL editors with syntax highlighting and interactive query results.

Staged editing

Edit cells, add rows, duplicate rows, set NULL, stage deletes, review pending changes, and commit everything in one transaction.

How to edit PostgreSQL rows safely

1
Select before you update
Write the WHERE clause as a SELECT and look at what comes back. This one habit prevents most data incidents, because the mistake is almost never in the SET clause — it is in a WHERE that matches more rows than you expected, or none at all.
```
SELECT id, status FROM orders WHERE customer_id = 42 AND status = 'pending';
```
2
Open a transaction
BEGIN makes the change reversible until you commit. Without it, PostgreSQL autocommits each statement immediately and there is nothing to undo — recovery then means restoring a backup. This is the difference between a mistake and an incident.
```
BEGIN;
```
3
Update, and check the row count
PostgreSQL reports UPDATE 3 after the statement. If you expected three rows and it says three, continue. If it says 40000, you have just found the bug before it shipped. RETURNING goes further and shows you the actual rows that changed.
```
UPDATE orders
SET status = 'refunded', updated_at = now()
WHERE customer_id = 42 AND status = 'pending'
RETURNING id, status;
```
4
Commit, or roll back
COMMIT makes it permanent. ROLLBACK discards everything since BEGIN as though it never happened. If the row count surprised you, roll back — there is no cost to doing so and no penalty for being wrong.
```
COMMIT;   -- or ROLLBACK; if the count was not what you expected
```
5
Set a real NULL, not an empty string
These are different values and PostgreSQL treats them differently: NULL means unknown and fails any equality test, while '' is a known, empty value. Tools that silently turn a blank field into an empty string are a common source of quietly corrupted data.
```
UPDATE users SET middle_name = NULL WHERE id = 7;

-- Note: IS NULL, never = NULL, which is always unknown
SELECT count(*) FROM users WHERE middle_name IS NULL;
```
Or edit in a client that stages changes
A GUI grid that writes each cell as you leave it gives you the same risk as an unwrapped UPDATE. PgDeck holds every pending edit, insert, duplicate, NULL, and delete as one set, shows it for review, and commits it in a single transaction — the same discipline as the steps above, without typing them.

The WHERE clause is the dangerous part

An UPDATE with no WHERE updates every row in the table, and PostgreSQL will run it without hesitation. There is no confirmation prompt and no undo outside a transaction. Writing the WHERE as a SELECT first is not being cautious, it is just how the statement should be built — you are checking the filter before attaching an action to it.

Updating from another table

UPDATE ... FROM lets you set values based on a join, which is how you backfill a column or apply a correction supplied as a list. It is far safer than generating hundreds of individual UPDATE statements, because it is one atomic operation with one row count to check. Make sure the join cannot match multiple source rows per target row — PostgreSQL picks one arbitrarily rather than erroring.

Deleting is the one with no recovery

An UPDATE that sets the wrong value can usually be corrected by another UPDATE if you know the old values. A DELETE outside a transaction is gone. Always run deletes inside BEGIN, always check the row count, and consider a soft delete column on tables where history has any value at all.

Long transactions have a cost

Holding a transaction open while you decide what to do blocks autovacuum from cleaning up and can block other writers on the rows you touched. Open the transaction, run the statement, check the count, and commit or roll back promptly. Do not BEGIN and then go to lunch — on a busy database that is its own kind of incident.

Staging changes is the same idea in a GUI

The whole point of the transaction pattern is that you see what will happen before it is permanent. A data grid that writes immediately removes that step, which is why editing production through one is risky. A client that stages the full pending set and commits it in one transaction gives you the review step and atomicity without typing BEGIN.

How do I edit data in PostgreSQL?

With UPDATE, ideally inside a transaction: BEGIN, run the UPDATE with a WHERE clause you have already tested as a SELECT, check the reported row count, then COMMIT or ROLLBACK. In a GUI client, edit cells in the data grid and commit the staged changes.

How do I undo an UPDATE in PostgreSQL?

Only if you are inside a transaction that has not committed — then ROLLBACK discards it. Once committed there is no undo, and recovery means restoring from a backup or reconstructing the previous values. This is why BEGIN before a data change matters.

How do I see which rows an UPDATE changed?

Add a RETURNING clause listing the columns you want back. UPDATE ... RETURNING id, status returns one row per row modified, so you see exactly what changed rather than only a count.

What is the difference between NULL and an empty string?

NULL means no value is known and fails every equality comparison, so you must test it with IS NULL. An empty string is a known value of zero length. Setting a field to '' when you meant NULL is a common and quiet form of data corruption.

Can I edit PostgreSQL data without writing SQL?

Yes, in a GUI client's data grid. Prefer one that stages changes for review and commits them in a single transaction, rather than writing each cell as soon as you leave it — the review step is what makes editing production data reasonable.