INSERT, UPDATE, and DELETE

INSERT, UPDATE, and DELETE change rows. psql reports how many rows each one touched, and that count is your first check that the statement did what you meant.

-- setup.sql
CREATE TABLE inventory (
    sku    TEXT PRIMARY KEY,
    name   TEXT NOT NULL,
    qty    INT  NOT NULL CHECK (qty >= 0),
    price  NUMERIC(8, 2) NOT NULL
);

$ psql -d sqldemo -f setup.sql
CREATE TABLE

INSERT and RETURNING

One INSERT can add many rows; RETURNING hands back what was written.

-- insert.sql
INSERT INTO inventory (sku, name, qty, price) VALUES
    ('P-1', 'Pen',      100, 1.50),
    ('N-1', 'Notebook',  40, 4.00),
    ('S-1', 'Stapler',    5, 12.00)
RETURNING sku, qty * price AS stock_value;

$ psql -d sqldemo -f insert.sql
 sku | stock_value 
-----+-------------
 P-1 |      150.00
 N-1 |      160.00
 S-1 |       60.00
(3 rows)

INSERT 0 3

RETURNING works like a SELECT over the new rows, including expressions. Its most common use is fetching a generated id without a second round trip. Listing the column names in the INSERT keeps it working if someone later adds or reorders columns.

UPDATE

UPDATE changes the rows its WHERE matches.

-- update.sql
UPDATE inventory
SET price = price * 1.10
WHERE qty < 50
RETURNING sku, price;

$ psql -d sqldemo -f update.sql
 sku | price 
-----+-------
 N-1 |  4.40
 S-1 | 13.20
(2 rows)

UPDATE 2

Two items had fewer than 50 in stock, and the report says UPDATE 2. Both new prices happen to fit in two decimals; a result like 4.444 would be stored as 4.44, because the column type NUMERIC(8, 2), not the expression, decides the stored precision.

UPDATE … FROM Another Table

A delivery table says how much stock arrived; apply it with a join.

-- update_from.sql
CREATE TABLE deliveries (sku TEXT, received INT);
INSERT INTO deliveries VALUES ('N-1', 60), ('S-1', 20);

UPDATE inventory i
SET qty = i.qty + d.received
FROM deliveries d
WHERE d.sku = i.sku
RETURNING i.sku, i.qty;

$ psql -d sqldemo -f update_from.sql
CREATE TABLE
INSERT 0 2
 sku | qty 
-----+-----
 N-1 | 100
 S-1 |  25
(2 rows)

UPDATE 2

Each inventory row was updated from its matching delivery; the pen had no delivery and was not touched. If two delivery rows existed for the same SKU, PostgreSQL would apply only one of them, so aggregate the source first when duplicates are possible.

DELETE

DELETE removes the rows its WHERE matches. The file first tries to sell more staplers than exist.

-- delete.sql
UPDATE inventory SET qty = qty - 30 WHERE sku = 'S-1';
DELETE FROM inventory WHERE price > 10 RETURNING sku, name;
SELECT sku, qty FROM inventory ORDER BY sku;

$ psql -d sqldemo -f delete.sql
psql:delete.sql:2: ERROR:  new row for relation "inventory" violates check constraint "inventory_qty_check"
DETAIL:  Failing row contains (S-1, Stapler, -5, 13.20).
 sku |  name   
-----+---------
 S-1 | Stapler
(1 row)

DELETE 1
 sku | qty 
-----+-----
 N-1 | 100
 P-1 | 100
(2 rows)

The stapler had 25 in stock, so subtracting 30 would make it negative, and the CHECK (qty >= 0) rejected the update. The DELETE then removed it anyway because its price was over 10, and RETURNING shows exactly what disappeared.

Upsert With ON CONFLICT

ON CONFLICT decides what happens when an insert would break a unique constraint.

-- upsert.sql
INSERT INTO inventory (sku, name, qty, price) VALUES
    ('P-1', 'Pen',   25, 1.60),
    ('E-1', 'Eraser', 30, 0.80)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty,
    price = EXCLUDED.price
RETURNING sku, qty, price, (xmax <> 0) AS was_update;

$ psql -d sqldemo -f upsert.sql
 sku | qty | price | was_update 
-----+-----+-------+------------
 P-1 | 125 |  1.60 | t
 E-1 |  30 |  0.80 | f
(2 rows)

INSERT 0 2

P-1 already existed, so its row was updated: inventory.qty is the stored 100 and EXCLUDED.qty is the 25 being inserted. E-1 was new and was inserted normally. The xmax <> 0 trick is a PostgreSQL internal that reveals which rows took the update path; it is handy for debugging, not something to build logic on.

A Missing WHERE, Caught by a Transaction

Forgetting WHERE updates every row. Inside a transaction, the count warns you in time.

-- missing_where.sql
BEGIN;
UPDATE inventory SET price = 0;
SELECT sku, price FROM inventory ORDER BY sku;
ROLLBACK;
SELECT sku, price FROM inventory ORDER BY sku;

$ psql -d sqldemo -f missing_where.sql
BEGIN
UPDATE 3
 sku | price 
-----+-------
 E-1 |  0.00
 N-1 |  0.00
 P-1 |  0.00
(3 rows)

ROLLBACK
 sku | price 
-----+-------
 E-1 |  0.80
 N-1 |  4.40
 P-1 |  1.60
(3 rows)

UPDATE 3 on a statement meant for one product is the alarm. ROLLBACK discarded the change, and the final SELECT shows the real prices untouched. Outside a transaction, psql commits each statement immediately and the only way back is a backup.

Practice

1. Insert a product R-1, “Ruler”, 15 units at 2.25, and return its stock value.

-- practice_insert.sql
INSERT INTO inventory (sku, name, qty, price)
VALUES ('R-1', 'Ruler', 15, 2.25)
RETURNING sku, qty * price AS stock_value;

$ psql -d sqldemo -f practice_insert.sql
 sku | stock_value 
-----+-------------
 R-1 |       33.75
(1 row)

INSERT 0 1

2. Cut the price of every item with more than 50 units by 0.10 and show the new prices.

-- practice_discount.sql
UPDATE inventory SET price = price - 0.10
WHERE qty > 50
RETURNING sku, qty, price;

$ psql -d sqldemo -f practice_discount.sql
 sku | qty | price 
-----+-----+-------
 N-1 | 100 |  4.30
 P-1 | 125 |  1.50
(2 rows)

UPDATE 2

3. Upsert E-1 with 10 more units, but only if the incoming price is lower than the stored one; otherwise leave the row unchanged.

-- practice_conditional_upsert.sql
INSERT INTO inventory (sku, name, qty, price) VALUES ('E-1', 'Eraser', 10, 0.95)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty, price = EXCLUDED.price
WHERE EXCLUDED.price < inventory.price;
SELECT sku, qty, price FROM inventory WHERE sku = 'E-1';

$ psql -d sqldemo -f practice_conditional_upsert.sql
INSERT 0 0
 sku | qty | price 
-----+-----+-------
 E-1 |  30 |  0.80
(1 row)