PostgreSQL INSERT, UPDATE, and DELETE Operations

Data modification (DML) is the most frequent day-to-day operation—on top of standard SQL, PostgreSQL adds two distinctive features: RETURNING and UPSERT.

1. What You'll Learn

INSERT INTO: single-row / multi-row / insert-from-query
RETURNING clause (PG feature: returns the affected rows)
UPDATE / DELETE with RETURNING
UPSERT: INSERT ON CONFLICT (PG feature)
TRUNCATE TABLE to empty a table
DML inside a transaction (BEGIN / COMMIT / ROLLBACK)

2. A Operations Person's Real Story

(1) The Pain: Duplicate Data When Bulk-Importing Products

Charlie needs to bulk-import 5,000 product records into the e-commerce database, but some products already exist (identified by name). He needs to:

For existing products: update price and stock (without raising an error)
For new products: insert normally
After importing: immediately know which rows were newly inserted and which were updated

(2) The Solution: UPSERT + RETURNING

PostgreSQL's INSERT ON CONFLICT (UPSERT) handles both insert and update in a single SQL statement, and the RETURNING clause returns the affected rows:

SQL

-- Upsert: insert new products, update existing ones
INSERT INTO products (name, price, stock, category)
VALUES ('Running Shoes', 99.99, 200, 'Footwear')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock
RETURNING id, name, price, stock,
    CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation;

(3) The Payoff

No need to SELECT first to decide INSERT or UPDATE (one fewer query)
No concurrency race condition (atomic operation)
RETURNING returns the result immediately, no second query needed
Bulk import efficiency improved more than 10x

3. INSERT

(1) Basic Syntax

SQL

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING * | column_list;

▶ Example: Single-Row Insert

SQL

-- Insert a single row
INSERT INTO users (email, name, password_hash)
VALUES ('charlie@example.com', 'Charlie', '$2a$12$hash123');

-- Insert with RETURNING (get the auto-generated id)
INSERT INTO users (email, name, password_hash)
VALUES ('diana@example.com', 'Diana', '$2a$12$hash456')
RETURNING id, email, created_at;

TEXT

 id |       email          |          created_at
----+----------------------+-------------------------------
  5 | diana@example.com    | 2026-07-13 10:30:00+00

▶ Example: Multi-Row Insert

SQL

-- Insert multiple rows in one statement (faster than multiple INSERTs)
INSERT INTO products (name, price, stock, category) VALUES
    ('Wireless Mouse', 29.99, 500, 'Accessories'),
    ('USB-C Cable', 12.99, 1000, 'Accessories'),
    ('Mechanical Keyboard', 79.99, 200, 'Input Devices'),
    ('Monitor Stand', 49.99, 150, 'Accessories'),
    ('Webcam HD', 59.99, 300, 'Video');

TEXT

INSERT 0 1

▶ Example: Insert From a Query

SQL

-- Create an archive table and copy data from the original
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

-- Insert rows from a SELECT query
INSERT INTO orders_archive
SELECT * FROM orders
WHERE status = 'cancelled'
  AND created_at < NOW() - INTERVAL '90 days';

TEXT

INSERT 0 1

4. RETURNING Clause (PG Feature)

RETURNING is a PostgreSQL-only feature—after INSERT / UPDATE / DELETE it returns the affected rows directly, with no extra SELECT needed.

(1) RETURNING Across Operations

Operation	Syntax	Returns
INSERT	`INSERT ... RETURNING *`	The newly inserted row
UPDATE	`UPDATE ... RETURNING *`	The updated row
DELETE	`DELETE ... RETURNING *`	The deleted row

Comparison	MySQL	PostgreSQL
Get the ID after INSERT	`SELECT LAST_INSERT_ID()`	`INSERT ... RETURNING id`
Get the deleted rows	SELECT first, then DELETE	`DELETE ... RETURNING *`
Get values after UPDATE	UPDATE first, then SELECT	`UPDATE ... RETURNING *`

▶ Example: The Magic of RETURNING

SQL

-- INSERT: get auto-generated values immediately
INSERT INTO users (email, name, password_hash)
VALUES ('eve@example.com', 'Eve', '$2a$12$hash789')
RETURNING id, email, created_at;

-- UPDATE: see before and after values
UPDATE products
SET price = 69.99, stock = stock - 10
WHERE id = 1
RETURNING id, name,
    price AS new_price,
    stock AS new_stock;

-- DELETE: archive before deleting
WITH deleted AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND created_at < NOW() - INTERVAL '1 year'
    RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

TEXT

INSERT 0 1

5. UPDATE

(1) Basic Syntax

SQL

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[RETURNING * | column_list];

▶ Example: Basic Update

SQL

-- Update a single row
UPDATE users
SET name = 'Alice Smith', updated_at = NOW()
WHERE email = 'alice@example.com';

-- Update with condition
UPDATE products
SET price = price * 0.9  -- 10% discount
WHERE category = 'Accessories' AND stock > 100;

-- Update with RETURNING
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock > 0
RETURNING id, name, stock;

TEXT

-- SQL statement executed successfully

▶ Example: UPDATE Based on a JOIN

SQL

-- Update order total based on order items
UPDATE orders o
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.id
)
WHERE o.status = 'pending';

-- Update using FROM clause (PG extension)
UPDATE orders o
SET total_amount = oi_sum.total
FROM (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
) oi_sum
WHERE o.id = oi_sum.order_id
  AND o.status = 'pending';

TEXT

  result  
----------
   42.50
(1 row)

⚠️ Note: An UPDATE without a WHERE clause updates the entire table! This is the most common dangerous operation. Always write the WHERE before the SET, as a habit.

6. DELETE

(1) Basic Syntax

SQL

DELETE FROM table_name
[WHERE condition]
[RETURNING * | column_list];

▶ Example: Delete Operations

SQL

-- Delete specific rows
DELETE FROM products
WHERE stock = 0 AND is_available = false
RETURNING id, name;

-- Delete with subquery
DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = false
);

-- Delete all rows (use TRUNCATE instead for large tables!)
-- DELETE FROM logs;  -- slow, generates WAL for each row

TEXT

DELETE 2

(2) DELETE vs TRUNCATE

Dimension	DELETE	TRUNCATE
Speed	Deletes row by row, slow	Empties in one go, extremely fast
Transaction	Can be rolled back (inside a transaction)	Can also be rolled back (inside a transaction)
Triggers	Fires row-level triggers	Does not fire row-level triggers
Sequence reset	No reset	Can reset (RESTART IDENTITY)
WHERE	Supported	Not supported (empties the whole table)
Use case	Delete some rows	Empty the whole table

▶ Example: TRUNCATE to Empty a Table

SQL

-- Truncate one table (fast, resets storage)
TRUNCATE TABLE logs;

-- Truncate multiple tables at once
TRUNCATE TABLE order_items, orders;

-- Cascade: also truncate tables with foreign key references
TRUNCATE TABLE users CASCADE;

-- Reset auto-increment sequences
TRUNCATE TABLE products RESTART IDENTITY;

TEXT

-- SQL statement executed successfully

7. UPSERT (INSERT ON CONFLICT)

UPSERT is one of PostgreSQL's most practical distinctive features—when inserted data conflicts with an existing row, it automatically performs an update instead of raising an error.

(1) UPSERT Flowchart

graph TB
    START[INSERT row] --> CONFLICT{Unique constraint<br/>conflict?}
    CONFLICT -->|No conflict| INSERT[Insert new row<br/>RETURN inserted]
    CONFLICT -->|Conflict!| ACTION{ON CONFLICT<br/>action?}
    ACTION -->|DO NOTHING| SKIP[Skip this row<br/>No error, no update]
    ACTION -->|DO UPDATE| UPDATE[Update existing row<br/>using EXCLUDED]
    UPDATE --> RET2[RETURN updated row]

(2) Syntax

SQL

INSERT INTO table_name (column_list)
VALUES (value_list)
ON CONFLICT (column_name | constraint_name)
DO NOTHING | DO UPDATE SET column = EXCLUDED.column ...
[RETURNING *];

Keyword	Description
`ON CONFLICT (column)`	The column to detect conflicts on (must have a UNIQUE constraint or primary key)
`ON CONFLICT ON CONSTRAINT name`	Specify a specific constraint name
`DO NOTHING`	Skip the row on conflict (no error, no update)
`DO UPDATE SET ...`	Perform an update on conflict
`EXCLUDED`	Virtual table holding the values that were originally to be inserted

▶ Example: DO NOTHING (Ignore Duplicates)

SQL

-- Insert only if email doesn't already exist
INSERT INTO users (email, name, password_hash)
VALUES ('alice@example.com', 'Alice', '$2a$12$newhash')
ON CONFLICT (email) DO NOTHING;
-- No error if email already exists

TEXT

INSERT 0 1

▶ Example: DO UPDATE (Update Existing Row)

SQL

-- Upsert: insert new products, update price/stock for existing ones
INSERT INTO products (name, price, stock, category)
VALUES
    ('Running Shoes', 99.99, 200, 'Footwear'),
    ('USB-C Cable', 14.99, 800, 'Accessories'),
    ('New Product', 39.99, 50, 'Gadgets')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock,
    category = EXCLUDED.category
RETURNING id, name, price, stock;

TEXT

INSERT 0 1

▶ Example: Conditional UPSERT (Update Only in Specific Cases)

SQL

-- Only update if the new price is lower
INSERT INTO products (name, price, stock, category)
VALUES ('Running Shoes', 79.99, 100, 'Footwear')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock
WHERE EXCLUDED.price < products.price;
-- Only updates if new price is cheaper than current price

TEXT

INSERT 0 1

8. DML Inside a Transaction

(1) Basic Transaction Operations

Command	Description
`BEGIN` or `START TRANSACTION`	Start a transaction
`COMMIT`	Commit the transaction (persist permanently)
`ROLLBACK`	Roll back the transaction (undo all changes)
`SAVEPOINT name`	Create a savepoint
`ROLLBACK TO SAVEPOINT name`	Roll back to a savepoint

▶ Example: Transaction Protecting a Batch Operation

SQL

-- Begin a transaction for batch import
BEGIN;

-- Insert order and items as a unit
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 0, 'pending')
RETURNING id;

-- Assume the above returned id = 10
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (10, 1, 2, 89.99),
    (10, 2, 5, 12.99);

-- Update order total
UPDATE orders
SET total_amount = (2 * 89.99 + 5 * 12.99)
WHERE id = 10;

-- Verify before committing
SELECT * FROM orders WHERE id = 10;
SELECT * FROM order_items WHERE order_id = 10;

-- All good? Commit
COMMIT;

-- Something wrong? Roll back everything
-- ROLLBACK;

TEXT

  result  
----------
   42.50
(1 row)

9. Complete Example: Bulk Product Import

SQL

-- ============================================
-- Complete example: bulk product import with UPSERT
-- Charlie imports 5000 products, some already exist
-- ============================================

-- Step 1: Create a temporary import table
CREATE TEMP TABLE import_products (
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    category VARCHAR(50)
);

-- Step 2: Load import data (simulated with sample rows)
INSERT INTO import_products (name, price, stock, category) VALUES
    ('Running Shoes', 99.99, 200, 'Footwear'),      -- already exists
    ('Laptop Backpack', 59.99, 300, 'Accessories'),   -- already exists, price changed
    ('Smart Water Bottle', 34.99, 500, 'Gadgets'),    -- new product
    ('Wireless Earbuds', 79.99, 400, 'Audio'),        -- new product
    ('Yoga Mat', 24.99, 600, 'Fitness');              -- new product

-- Step 3: Upsert all import data in one statement
BEGIN;

INSERT INTO products (name, price, stock, category)
SELECT name, price, stock, category
FROM import_products
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock
RETURNING id, name, price, stock;

-- Step 4: Verify results
SELECT id, name, price, stock
FROM products
WHERE name IN ('Running Shoes', 'Laptop Backpack',
               'Smart Water Bottle', 'Wireless Earbuds', 'Yoga Mat')
ORDER BY name;

-- Step 5: Clean up
DROP TABLE import_products;

COMMIT;

❓ FAQ

Q What's the difference between INSERT ON CONFLICT and REPLACE INTO (MySQL)?

A MySQL's REPLACE INTO is actually DELETE + INSERT—it deletes the old row and inserts a new one, which changes the auto-increment ID, fires triggers, and breaks foreign keys. PG's ON CONFLICT DO UPDATE is a true in-place update: the ID stays the same and no DELETE trigger fires.

Q What is EXCLUDED?

A EXCLUDED is a virtual table PG provides in UPSERT, holding the values that were originally to be INSERTed but failed to insert due to a conflict. Use it to refer to the new values, distinguishing them from the existing old values in the table. For example, EXCLUDED.price is the new price and products.price is the old price.

Q How many rows are best for a bulk insert?

A For a single multi-value INSERT, 100–1000 rows is recommended. Beyond 1000 rows you may hit SQL parsing performance issues. For larger batches, use the COPY command (5–10x faster than INSERT, covered later in the backup lesson).

Q What happens if I forget the WHERE in an UPDATE?

A It updates the entire table! This is one of the most dangerous SQL mistakes. Protective measures: 1) Always write WHERE before SET; 2) Operate inside a transaction—SELECT to confirm the scope first, then UPDATE; 3) Set sql_safe_updates = on (run in psql).

Q Can RETURNING be used inside a CTE?

A Yes! This is a very powerful PG pattern—DELETE ... RETURNING combined with INSERT ... SELECT implements data migration: WITH moved AS (DELETE FROM active WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.

Q Can TRUNCATE be rolled back?

A Yes! Inside a transaction, TRUNCATE followed by ROLLBACK restores the data. This differs from MySQL (MySQL's TRUNCATE is DDL and cannot be rolled back). PG's TRUNCATE is safe inside a transaction.

📖 Summary

INSERT supports single-row, multi-row, and insert-from-query; multi-row inserts are far more efficient than row-by-row inserts
The RETURNING clause (PG feature) lets INSERT/UPDATE/DELETE return affected rows directly, with no second query
UPDATE supports JOIN-based updates (FROM clause), clearer than a subquery
DELETE removes rows one by one (slow); TRUNCATE empties in one go (fast); TRUNCATE can be rolled back inside a transaction
UPSERT (INSERT ON CONFLICT) is a core PG feature: one SQL statement handles "insert if not exists, update if exists"
The EXCLUDED virtual table references the newly inserted values, distinct from the existing values in the table
Transactions (BEGIN/COMMIT/ROLLBACK) protect batch operations; on error you can roll back

📝 Exercises

Basic (★): Create a tags table (id SERIAL primary key, name VARCHAR(50) UNIQUE), insert 5 tags, and use RETURNING to return the id and name of each inserted row.
Intermediate (★★): Perform an UPSERT on the products table from this lesson—insert 3 product rows, one of which has a name that already exists. For the existing product, update the price; for new products, insert normally. Use RETURNING to show the operation result.
Challenge (★★★): In a single transaction, complete the following: 1) Create a products_backup table (same structure as products); 2) Migrate products with price < 30 from products into products_backup using DELETE ... RETURNING + INSERT; 3) After verifying the migration result, COMMIT. Write the complete SQL script.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL INSERT, UPDATE, and DELETE Operations

1. What You'll Learn

2. A Operations Person's Real Story

(1) The Pain: Duplicate Data When Bulk-Importing Products

(2) The Solution: UPSERT + RETURNING

(3) The Payoff

3. INSERT

(1) Basic Syntax

▶ Example: Single-Row Insert

▶ Example: Multi-Row Insert

▶ Example: Insert From a Query

4. RETURNING Clause (PG Feature)

(1) RETURNING Across Operations

▶ Example: The Magic of RETURNING

5. UPDATE

(1) Basic Syntax

▶ Example: Basic Update

▶ Example: UPDATE Based on a JOIN

6. DELETE

(1) Basic Syntax

▶ Example: Delete Operations

(2) DELETE vs TRUNCATE

▶ Example: TRUNCATE to Empty a Table

7. UPSERT (INSERT ON CONFLICT)

(1) UPSERT Flowchart

(2) Syntax

▶ Example: DO NOTHING (Ignore Duplicates)

▶ Example: DO UPDATE (Update Existing Row)

▶ Example: Conditional UPSERT (Update Only in Specific Cases)

8. DML Inside a Transaction

(1) Basic Transaction Operations

▶ Example: Transaction Protecting a Batch Operation

9. Complete Example: Bulk Product Import

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好