How to modify data in PostgreSQL using INSERT, UPDATE, UPDATE JOINS, DELETE and UPSERT

January 24, 2023

SUMMARY: This article reviews how to use the basic data manipulation language (DML) types INSERT, UPDATE, UPDATE JOINS, DELETE, and UPSERT to modify data in tables.
 

Data Manipulation Language (DML) 

A DML statement is executed when you:

  • Add new rows to a table.
  • Modify existing rows in a table.
  • Remove existing rows from a table.

DML Statement Types

  • INSERT
  • UPDATE
  • DELETE

INSERT Statement 

You can add new rows to a table by using the INSERT statement:

Syntax 

INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

With the above syntax, only one row is inserted at a time. 

a) Insert New Rows: Insert new rows for each column.

Table Structure 

postgres=# \d departments 

                        Table "public.departments"

     Column      |         Type          | Collation | Nullable | Default

-----------------+-----------------------+-----------+----------+---------

 department_id   | integer               |           |          | 

 department_name | character varying(20) |           |          | 

 manager_id      | integer               |           |          | 

 location_id     | integer               |           |          | 

 

Example

INSERT INTO departments(department_id,department_name,manager_id, location_id) VALUES (10, 'IT', 100, 1100);

 

Or

INSERT INTO departments VALUES (10, 'IT', 100, 1100);

 

b) Insert Rows with null values: 

Example

INSERT INTO departments(department_id,department_name) values (20,'HR');

 

Or

INSERT INTO departments values (30,'Sales',null,null);

 

UPDATE Statement

Using an UPDATE statement a user can modify an existing row.

Syntax

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

 

Example 1

Modify a value department id to 50 for an employee whose id is 100 using the WHERE clause::

postgres=# select * from departments ;

 department_id | department_name  | manager_id | location_id 

---------------+------------------+------------+-------------

            70 | Public Relations |        100 |        1700

            10 | IT               |        100 |        1100

(2 rows)



postgres=# update departments set department_id=50 where department_name='IT';

UPDATE 1



postgres=# select * from departments ;

 department_id | department_name  | manager_id | location_id 

---------------+------------------+------------+-------------

            70 | Public Relations |        100 |        1700

            50 | IT               |        100 |        1100

(2 rows)

 

Example 2

Without the WHERE clause the entire table would get updated:

postgres=# update departments set location_id = 2000;

UPDATE 2



postgres=# select * from departments ;

 department_id | department_name  | manager_id | location_id 

---------------+------------------+------------+-------------

            70 | Public Relations |        100 |        2000

            50 | IT               |        100 |        2000

(2 rows)

Example 3

Update MULTIPLE ROWS. 

We can update more than one row using an UPDATE statement:

postgres=# select * from departments ;

 department_id | department_name | manager_id | location_id 

---------------+-----------------+------------+-------------

            10 | IT              |        100 |        1100

            20 | HR              |        110 |        1200

            30 | SALES           |        130 |        1400

            40 | OPERATIONS      |        140 |        1600

(4 rows)



postgres=# update departments

SET department_name = 

 CASE department_id

   WHEN 10 THEN 'SALES'

   WHEN 20 THEN 'RESEARCH'

   WHEN 30 THEN 'ACCOUNT'

END

WHERE department_id in (10,20,30);

UPDATE 3



postgres=# select * from departments ;

 department_id | department_name | manager_id | location_id 

---------------+-----------------+------------+-------------

            40 | OPERATIONS      |        140 |        1600

            10 | SALES           |        100 |        1100

            20 | RESEARCH        |        110 |        1200

            30 | ACCOUNT         |        130 |        1400

(4 rows)

 

UPDATE JOINS

We can use UPDATE  JOINS to add values from a separate table.

In the below example, we have updated the values in the second table by joining the values from the first table specifying the condition in the WHERE clause.

Update the values in the second table by joining values from the first table:

Create two tables with data:

create table countries (id int, name varchar(20));

create table states (id int, name varchar(20));




insert into countries values (1,'America') , (2,'Brazil') , (3,'Canada') ;

insert into states values (1,'Washington') , (2,'Yardley') , (3,'Zimbabwe') ;

 

Update the values in the second table by joining values from the first table:

postgres=# UPDATE states

  SET Name = countries.Name

           FROM countries

           WHERE states.ID = countries.ID;

 

Result: 

postgres=# select * from states ;

 id |  name   

----+---------

  1 | America

  2 | Brazil

  3 | Canada

(3 rows)

 

DELETE Statement

The DELETE statement is used to remove existing rows from a table.

Syntax

DELETE [FROM] table [WHERE condition];

 

Example 1

Delete rows by restricting a condition using a WHERE clause. If the WHERE clause is omitted, all the rows from the table would be deleted.

postgres=# delete from departments where department_name = 'HR';

 

Example 2 

A subquery will retrieve an output first and then the WHERE condition will be executed: 

postgres=# DELETE FROM departments

WHERE department_id = (SELECT department_id FROM departments where location_id=1200);

 

UPSERT Statement

Using an UPSERT statement, you can update a record if it already exists or insert a new record if it does not. This can be done in a single statement.

Example

Below sample example for usage of INSERT...ON CONFLICT:

postgres=# select * from tab1 ;

 pid | sales | status 

-----+-------+--------

   1 |    20 | CURR

   2 |    40 | CURR

   3 |    60 | ABS

   4 |    30 | NEW

(4 rows)





postgres=# insert into tab1 (sales,status) 

values (30,'HOLD')

ON CONFLICT (sales) 

DO NOTHING;

INSERT 0 0





postgres=# select * from tab1 ;

 pid | sales | status 

-----+-------+--------

   1 |    20 | CURR

   2 |    40 | CURR

   3 |    60 | ABS

   4 |    30 | NEW

(4 rows)



postgres=# insert into tab1 (sales,status) 

values (30,'HOLD')

ON CONFLICT (sales) 

DO

UPDATE

SET status = 'PROD';

INSERT 0 1



postgres=# select * from tab1 ;

 pid | sales | status 

-----+-------+--------

   1 |    20 | CURR

   2 |    40 | CURR

   3 |    60 | ABS

   4 |    30 | PROD

(4 rows)

 

 

Share this

More Blogs