PostgreSQL Update + Examples

In this PostgreSQL tutorial, we are going to learn about PostgreSQL Update. Here we will learn how to use the update queries in PostgreSQL, and we will also cover the following list of topics.

• Postgresql update
• Postgresql update query
• Postgresql update with join
• Postgresql update all rows
• Postgresql update multiple rows
• Postgresql update json field
• Postgresql update from select
• Postgresql update jsonb
• Postgresql update column type

Postgre update query

The PostgreSQL UPDATE query is used to change the present records in a table. We can use the WHERE clause with the UPDATE query to update the selected rows. Otherwise, all the rows would be updated.

The basic syntax of the UPDATE query with WHERE clause is shown below.

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

We can link N number of conditions using AND or OR operators. Let’s consider this with the help of a table HUMAN, having records as follows.

select * from human;

The following is an example, which would update ADDRESS for the customer name, whose license number is 44. Let’s understand its query and then its implementation.

UPDATE HUMAN SET ADDRESS = 'Miami' WHERE LICENSENO = 44;

Here is the result after the updation.

If we want to modify all ADDRESS column values in the HUMAN table, you do not need to use the WHERE clause and the UPDATE query would be as follows.

UPDATE HUMAN SET ADDRESS = 'Texas';

Now, the HUMAN table will have the following records −

Also, check: Create a stored procedure in PostgreSQL

Postgres update with join

We can update the records stored inside the table in different ways. PostgreSQL will provide UPDATE JOIN to do the same task.

In some cases, we want to update one table’s records based on another table’s records.

To join other tables within that statement, we have to explain the PostgreSQL FROM clause with the joined table, along with we want to specify the PostgreSQL WHERE clause along with a JOIN condition.

We have to attach the SET clause and have to explain the PostgreSQL FROM clause instantly after it. Let’s understand its syntax.

UPDATE table1
 SET table1.col1 = expression
FROM table2
 WHERE table1.col2 = table2.col2;

Let’s understand with an example by considering the syntax which we have explained above to understand the PostgreSQL UPDATE with JOIN. According to the syntax, we will update the values of the table1 by using the values from the table2.

Here we have explained a JOIN condition on column2 of table1 and table2. So if every row in table1 and table2 contains the same value, then the UPDATE statement updates the column1 column value in table table1 and sets the value as per the expression explained in the set clause.

Now we will create two tables of names ‘studt’ and ‘deptt’ by using the CREATE TABLE statement. Let’s check its code.

create table studt
(
studt_id serial PRIMARY KEY,
studt_fname VARCHAR(60) NOT NULL,
studt_lname VARCHAR(60) NOT NULL,
studt_total_marks int NOT NULL,
deptt_id int NOT NULL
);
create table deptt
(
deptt_id serial PRIMARY KEY,
deptt_name VARCHAR(60) NOT NULL
);

Let’s check its output.

Now, we will insert some data into the deptt table by using the INSERT INTO statement as follows.

INSERT INTO studt(studt_fname,studt_lname,studt_total_marks, deptt_id)
VALUES
('Dylan','Minnette',51,1),
('Justin','Prentice',67,2),
('Miles','Heizer',87,3),
('Tommy','Dorfman',72,4),
('Ross','Butler',83,5);

Here is the result of the above INSERT INTO statement by using the following statement.

select * from studt;

Let’s check the output.

Now, we will insert the data in the deptt table.

INSERT INTO deptt(deptt_name)
VALUES
('Chemical'),
('Mining'),
('Aerospace'),
('Petroleum'),
('Robotics');

Let’s check the output.

Considering the above example where we will give extra 40 marks to each student except the Aerospace department whose dept_id is 3. So we will use the UPDATE JOIN statement as follows:

UPDATE studt
SET studt_total_marks = studt_total_marks + 40
FROM
deptt
WHERE
studt.deptt_id = deptt.deptt_id AND deptt.deptt_id <> 3;

Exemplify the above result by using the following SQL statement.

select * from studt;

Let’s check the output for the above query.

Read: PostgreSQL list databases

Postgres update multiple rows

Now, we will learn how to update multiple rows at once in PostgreSQL. We will understand this by following the above example which we have already learned using joins. Let’s check the query for updating multiple rows.

UPDATE "table_name"
SET "column1" = value1, "column2" = value2, "columnN" = valueN
WHERE condition;

In that example, we will update deptt_id and marks of the first three students from the studt table. Let’s check the code for this.

UPDATE "studt"
SET "deptt_id" = 5, "studt_total_marks" = 123
WHERE "studt_id" <= 3;

Let’s check its output.

Read PostgreSQL Subquery

Postgresql update all rows

When data is being inserted into rows within the database, those rows will have more than one of their column values modified through the use of the PostgreSQL UPDATE command. Column values may update one or the other constants, identifiers to other data sets and expressions.

They will make use of a whole column or a subset of a column’s values through described conditions. The UPDATE command will use the following code.

UPDATE "studt"
SET "deptt_id" = 5, "studt_total_marks" = 241
WHERE "studt_id" <= 5;

Let’s check its output.

Read: PostgreSQL Data Types

PostgreSQL update column type

Let us understand how we can update the column’s data type. We will use the ALTER TABLE command to modify the column’s data type. The basic syntax of updating the column datatype is shown below.

ALTER TABLE table_name  
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;  

Let’s understand the above syntax.

• First, explain the name of the table to which the column we want to update belongs in the ALTER TABLE clause.
• Then, assign the name of the column whose data type will be updated in the ALTER COLUMN clause.
• Afterthat, provide the new data type for the column after the TYPE keyword. It is possible that we can use SET DATA TYPE or TYPE.

Example: Let’s create a table and insert a few rows into it for the demonstration using the statement below

CREATE TABLE gadget (
    id serial PRIMARY KEY,
    name TEXT NOT NULL,
    gadget_no VARCHAR NOT NULL,
    description TEXT,
    LOCATION TEXT,
    acquired_date DATE NOT NULL
);

INSERT INTO gadget (NAME, gadget_no, location, acquired_date)
 VALUES('Ipad','11','room','2020-07-14'),
       ('Laptop','22','room','2021-07-06');

After this, we have to change the data type of the name column to VARCHAR, using the statement below.

ALTER TABLE gadget ALTER COLUMN name TYPE VARCHAR;

We have to change the data type of description and location columns from TEXT to VARCHAR using the statement below.

Now we will check the table for the changes which have been made using the statement below.

SELECT * FROM gadget;

Let’s check the output by implementing the above query.

Read: Postgresql create user with password

Postgres update json / jsonb

JSON which stands for JavaScript Object Notation is an open standard format that contains key-value pairs. The vital role of JSON is to transport data between a server and a web application. JSON is a human-readable text which is different from other formats.

PostgreSQL consists of a native JSON data type that carries various functions and operators for manipulating JSON data. Now, we will learn how to update jsonb in PostgreSQL but first, we will understand its basic syntax.

CREATE TABLE users(id serial, data json);

Now, we will create a table and then we will update jsonb data.

 CREATE TABLE users (
 id serial NOT NULL PRIMARY KEY,
 info json NOT NULL
);

The user table has two columns i.e. id column which is the primary key column that identifies the order. Another is the info column that stores the data in the form of JSON.

Now, we will insert the data into tables but first, we will understand the syntax. For inserting the data into a JSON column, we will check that data is in a valid JSON format.

INSERT INTO users(info) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Let’s insert the data in the user table.

INSERT INTO users(info)
VALUES  
('{ "buyer": "Brandon Flynn","items": {"product": "Bags","qty": 20}}'),
('{ "buyer": "Tyler Barnhardt","items":{"product": "Bottle","qty":12}}');

Now, we will check its output by implementing the following query.

select info from users;

Let’s check its output.

Now, we will update the ‘data’ column by the following syntax:

UPDATE users
SET info = replace(info::TEXT,': "Brandon Flynn"',': "Brandon Abc"')::jsonb
Where id = 1;

Let’s check the output after updation.

Read: How to create a view in PostgreSQL

Postgresql update from select

In this section, we will how to use a SELECT statement while updating column data in PostgreSQL. Now, using the SELECT statement in PostgreSQL update can only be implemented with a subquery. Firstly, we will create two tables and then, we will implement the SELECT statement with the help of subquery. Let’s create the first table.

create table User_Data (
user_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(50)
);
insert into User_Data (user_id, first_name, last_name, gender) values (1, 'Flemming', 'Andreopolos', 'Male');
insert into User_Data (user_id, first_name, last_name, gender) values (2, 'Luz', 'Winear', 'Male');
insert into User_Data (user_id, first_name, last_name, gender) values (3, 'Ashely', 'de Mendoza', 'Male');

Let’s check the output for the first table.

Let’s check the query for another table.

create table Customer_Data (
user_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(50)
);
insert into Customer_Data (user_id, first_name, last_name, gender) values (1, 'Valerye', 'Whitehorne', 'Male');
insert into Customer_Data (user_id, first_name, last_name, gender) values (2, 'Adrian', 'Bompas', 'Female');
insert into Customer_Data (user_id, first_name, last_name, gender) values (3, 'Golda', 'Sarle', 'Male');

Let’s check its implementation.

Now, we will implement the Postgresql update from select with the help of the subquery. Let’s check its query.

WITH subquery AS (
SELECT user_id, first_name, last_name, gender
FROM User_Data
)
UPDATE Customer_Data
SET first_name = subquery.first_name,
last_name = subquery.last_name,
gender = subquery.gender
FROM subquery
WHERE customer_data.user_id = subquery.user_id;

Let’s check the implementation.

In the above example, we have updated the table data of Customer_Data. For this, we have used the values from the User_Data table. Now, to fetch the values from the User_Data table, we have used the SELECT statement as a subquery. And then, updated the values of Customer_Data using the selected data of the User_Data table.

You may also like to read the following PostgreSQL tutorials.

• Postgresql ilike case insensitive
• Postgresql listen_addresses
• Postgresql current_timestamp
• Postgresql group_concat
• Postgresql Sum
• Postgres date range
• Postgresql if else

In this PostgreSQL tutorial, we have learned about PostgreSQL Update. Here we have learned how to update the queries available in PostgreSQL, and we have also covered the following list of topics.

• Postgresql update
• Postgresql update query
• Postgresql update with join
• Postgresql update all rows
• Postgresql update multiple rows
• Postgresql update json field
• Postgresql update from select
• Postgresql update jsonb
• Postgresql update column type