---
title: "Data Manipulation: INSERT, UPDATE, DELETE"
type: concept
tags: [insert, update, delete, upsert, returning]
updated: 2026-06-25
confidence: high
sources: [raw/web_community-postgresql-documentation-18-chapter-6-data-manipulation.md, raw/web_community-postgresql-documentation-18-6-1-inserting-data.md, raw/web_community-postgresql-documentation-18-6-2-updating-data.md, raw/web_community-postgresql-documentation-18-6-3-deleting-data.md, raw/web_community-postgresql-documentation-18-6-4-returning-data-from-modified.md, raw/web_community-postgresql-documentation-18-13-2-transaction-isolation.md]
---
# Data Manipulation: INSERT, UPDATE, DELETE

Data manipulation language (DML) fills and changes table contents. Each statement runs inside a transaction (see [[concepts/transactions-and-mvcc]]).

## INSERT

`INSERT` adds rows. Values may be given positionally or with an explicit column list; omitted columns take their defaults:

```sql
INSERT INTO products VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;
```

Insert multiple rows, or the result of a query:

```sql
INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products WHERE release_date = 'today';
```

For bulk loads, `COPY` is faster than `INSERT`.

## UPDATE

`UPDATE ... SET ... WHERE` changes columns of matching rows; the new value can be any expression and may reference the existing value. Omitting `WHERE` updates every row.

```sql
UPDATE products SET price = 10 WHERE price = 5;
UPDATE products SET price = price * 1.10;
UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;
```

## DELETE

`DELETE FROM ... WHERE` removes matching rows; without `WHERE` it removes them all.

```sql
DELETE FROM products WHERE price = 10;
DELETE FROM products;   -- removes every row
```

## Upsert: INSERT ... ON CONFLICT

An `ON CONFLICT` clause handles uniqueness conflicts instead of erroring: `ON CONFLICT DO NOTHING` skips conflicting rows, while `ON CONFLICT (...) DO UPDATE` performs an "upsert" (the recommended alternative to a manual `UPDATE`-then-`INSERT` loop). Under Read Committed, each proposed row is guaranteed to either insert or update.

## RETURNING

`INSERT`, `UPDATE`, and `DELETE` accept a `RETURNING` clause (same form as a `SELECT` list, or `RETURNING *`) to return modified-row data without a separate query — handy for generated keys:

```sql
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price;
DELETE FROM products WHERE obsoletion_date = 'today' RETURNING *;
```

`RETURNING` can also reference `old.` and `new.` values, and works for data-modifying statements inside a `WITH` query (see [[concepts/queries-and-joins]]).