PostgreSQL Insert, Update and Delete

Inserting, updating and deleting work like standard SQL, with two very useful PostgreSQL extras: RETURNING and ON CONFLICT.

Insert Rows

If you tried the examples of the last lesson, first run TRUNCATE students, courses RESTART IDENTITY; so both tables are empty and the ids start at 1 again.

Leave out the identity column; PostgreSQL fills it in:

INSERT INTO courses (title, duration_months)
VALUES
  ('Web Development', 6),
  ('Python Programming', 4),
  ('Graphic Designing', 3),
  ('Digital Marketing', 3);

INSERT 0 4

The 4 is the number of rows inserted. (The 0 is an old field that is always 0 today.)

INSERT INTO students (name, city, age, marks, course_id)
VALUES
  ('Ali Khan', 'Karachi', 19, 78, 1),
  ('Ayesha Siddiqui', 'Lahore', 21, 85, 2),
  ('Bilal Ahmed', 'Karachi', 22, 64, 1),
  ('Fatima Noor', 'Islamabad', 20, 92, 3),
  ('Hamza Raza', 'Karachi', 18, 70, NULL),
  ('Sana Iqbal', 'Lahore', 23, 88, 2);

INSERT 0 6

RETURNING

Normally an insert only tells you how many rows were added. Add RETURNING to get columns of the new rows back, such as the id the database created:

INSERT INTO students (name, city, age, marks, course_id)
VALUES ('Usman Ali', 'Hyderabad', 20, 81, 2)
RETURNING id, name, joined_at;

 id |   name    |           joined_at
----+-----------+-------------------------------
  7 | Usman Ali | 2026-10-15 10:30:12.482915+05
(1 row)

INSERT 0 1

Apps use this all the time: after a sign-up form is saved, the server needs the new user's id straight away.

Update with RETURNING

UPDATE students
SET marks = marks + 5
WHERE city = 'Lahore'
RETURNING name, marks;

      name       | marks
-----------------+-------
 Ayesha Siddiqui |    90
 Sana Iqbal      |    93
(2 rows)

UPDATE 2

You see the new values without running a second SELECT.

Update Using Another Table

PostgreSQL's UPDATE ... FROM lets you use another table in the condition. Give 2 grace marks to everyone in a 6-month course:

UPDATE students AS s
SET marks = s.marks + 2
FROM courses AS c
WHERE s.course_id = c.id
  AND c.duration_months = 6;

UPDATE 2

Delete with RETURNING

DELETE FROM students
WHERE id = 7
RETURNING name;

   name
-----------
 Usman Ali
(1 row)

DELETE 1

This is handy for logging what was removed.

Upsert with ON CONFLICT

An upsert means "insert, or update if it already exists". It needs a unique column to detect the conflict. courses.title is UNIQUE:

INSERT INTO courses (title, duration_months)
VALUES ('Python Programming', 5)
ON CONFLICT (title)
DO UPDATE SET duration_months = EXCLUDED.duration_months;

INSERT 0 1

Python Programming already exists, so instead of an error its duration is changed to 5. EXCLUDED means "the row you tried to insert".

To silently skip duplicates instead:

INSERT INTO courses (title, duration_months)
VALUES ('Web Development', 6)
ON CONFLICT (title) DO NOTHING;

INSERT 0 0

Summary

Exercise