9. Modifying Data

Insert

(guide you on how to insert a single row into a table.)

• allows you to insert a new row into a table.

• Its syntax is

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …);

• Its return format is

INSERT oid count

• OID is an object identifier. PostgreSQL used the OID internally as a primary key for its system tables. Typically, the INSERT statement returns OID with value 0

• The count is the number of rows that the INSERT statement inserted successfully

Returning Clause

• The INSERT statement also has an optional RETURNING clause that returns the information of the inserted row.

Example 1 (return the entire inserted row)

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING *;

Example 2 (returns the id of the inserted row)

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING id;

Example 3 (rename the returned value)

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …)
RETURNING output_expression AS output_name;

Examples

1. Inserting a single row into a table

INSERT INTO links (url, name)
VALUES('https://google.com', 'Google');

It returns

INSERT 0 1

2. Inserting character string that contains a single quote

you have to use an additional single quote (') to escape it.

INSERT INTO links (url, name)
VALUES('http://www.oreilly.com','O''Reilly Media');

3. Inserting a date value

To insert a date value into a column with the DATE type, you use the date in the format 'YYYY-MM-DD'.

INSERT INTO links (url, name, last_update)
VALUES ('https://google.com', 'Google', '2013-06-01')

---

Insert multiple rows

(show you how to insert multiple rows into a table.)

• To insert multiple rows into a table using a single INSERT statement, you use the following syntax:

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

Example (Inserting multiple rows and returning inserted rows)

INSERT INTO
    links(url,name, description)
VALUES
    ('https://duckduckgo.com/','DuckDuckGo','Privacy & Simplified Search Engine'),
    ('https://swisscows.com/','Swisscows','Privacy safe WEB-search')
RETURNING *;

Update

(update existing data in a table.)

• The PostgreSQL UPDATE statement allows you to modify data in a table.

• Its syntax is

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

• it returns the following command tag

UPDATE count

• where count is the number of rows updated including rows whose values did not change.

Returning updated rows

It also have optional RETURNING clause like INSERT statement that returns the updated rows

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition
RETURNING * | output_expression AS output_name;

Example (updating a row and returning the updated row)

UPDATE courses
SET published_date = '2020-07-01'
WHERE course_id = 2
RETURNING *;

Update join

(update values in a table based on values in another table.)

• Sometimes, you need to update data in a table based on values in another table. In this case, you can use the PostgreSQL UPDATE join syntax as follows:

UPDATE t1
SET t1.c1 = new_value
FROM t2
WHERE t1.c2 = t2.c2;

Example

Calculate the net price of every product based on the discount of the product segment

UPDATE
	product p
SET
	net_price = price - price * discount
FROM
	product_segment s
WHERE
	p.segment_id = s.id;

Delete

(delete data in a table.)

• It allows us to delete one or more rows from a table

• Its syntax is

DELETE FROM table_name
WHERE condition;

• The DELETE statement returns the number of rows deleted.

• To return the deleted row(s) to the client, you use the RETURNING clause as follows:

DELETE FROM table_name
WHERE condition
RETURNING (select_list | *)

Example

Deletes the rows with ids 6 and 7 and returns the deleted row to the client

DELETE FROM links
WHERE id IN (6, 7)
RETURNING *;

Upsert

(insert or update data if the new row already exists in the table.)

• The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).

• We can use the INSERT ON CONFLICT statement as follows

INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT target action;

• target can be

  • (column_name) – a column name.

  • ON CONSTRAINT constraint_name – where the constraint name could be the name of the UNIQUE constraint.

  • WHERE predicate – a WHERE clause with a predicate.

• action can be

  • DO NOTHING – means do nothing if the row already exists in the table.

  • DO UPDATE SET column_1 = value_1, .. WHERE condition – update some fields in the table.

Example

Concatenate the new email with old email when inserting a customer that already exists

INSERT INTO customers (name, email)
VALUES('Microsoft', '[email protected]')
ON CONFLICT (name)
DO
	UPDATE SET email = EXCLUDED.email || ';' || customers.email;