INSERT, UPDATE, DELETE

Once a table exists, data gets added, changed and removed. These three commands — INSERT, UPDATE, DELETE — together with SELECT are the core of everyday work. Try them in free play.

INSERT — adding

INSERT INTO books (title, author, year, price)
VALUES ('O‘tkan kunlar', 'Abdulla Qodiriy', 1926, 65000);

• in brackets — which columns;
• in VALUES — the values in the same order;
• we leave out id — the database fills it in.

Several rows at once:

INSERT INTO books (title, author, year) VALUES
  ('Mehrobdan chayon', 'Abdulla Qodiriy', 1929),
  ('Kecha va kunduz', 'Cho‘lpon', 1936);

Always list the columns. INSERT INTO books VALUES (…) expects every column of the table in exactly its order — add a column to the table and such queries break.

If a text contains an apostrophe, write it twice: 'O''tkan kunlar'. Our examples use the typographic mark (‘), which is not a quote, so there is no problem.

UPDATE — changing

UPDATE books SET price = 70000 WHERE id = 1;

Several columns: SET price = 70000, year = 1925. You can calculate from the old value:

UPDATE books SET price = price * 1.1 WHERE author = 'Abdulla Qodiriy';

DELETE — removing

DELETE FROM books WHERE year < 1900;

The most dangerous mistake: forgetting WHERE

UPDATE books SET price = 0;      -- EVERY book is now free!
DELETE FROM books;               -- EVERY book is gone!

A good habit: before an UPDATE or DELETE, run a SELECT with the same WHERE to see which rows will be touched:

SELECT * FROM books WHERE year < 1900;   -- check first
DELETE FROM books WHERE year < 1900;     -- then delete

Another safety net is a transaction: BEGIN; … check … COMMIT; or ROLLBACK;. More in the “Transactions” lesson.

How many rows were affected?

psql answers with UPDATE 3, DELETE 1, INSERT 0 2 (the last number is the rows inserted).

The number of the new row

PostgreSQL has RETURNING: INSERT INTO books (title, author) VALUES ('…', '…') RETURNING id; returns the new id straight away.

Exercise

Mission 3 (INSERT) and mission 6 (UPDATE, DELETE) in Practice are about exactly this. If you forget WHERE in mission 6, the trainer puts the database back and explains what happened.

Remember

• INSERT INTO table (columns) VALUES (…);
• UPDATE table SET column = value WHERE condition;
• DELETE FROM table WHERE condition; — without WHERE it touches every row.