The Postgres update query is used to modify the table data. Update query changes the values of all rows satisfying the specified criteria. You need to specify column names that you want to modify. Column names that are omitted will carry their old values.
Let’s see update query syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition RETURNING * | output_expression AS output_name;
In the above syntax,
table_name is name of the table you want to update.
columns names which we want to update are specified after SET keyword.
condition is the criteria which you specify to update specific rows in the table as per requirement.
RETURNING is the optional clause that returns the updated row.
WHERE clause is optional. If you omit the
WHERE clause, the
UPDATE statement will update all rows in the table.
When update statement returns successfully it returns:
count is the number of rows updated.
Let’s see its example:
CREATE TABLE coding( language_id serial primary key, language_name VARCHAR(255) NOT NULL ); INSERT INTO coding(language_name) VALUES ('JAVA'); UPDATE coding SET language_name = 'PHP' WHERE language_id = 1;
The above update statement changes language_name to PHP.
UPDATE coding SET language_name = 'PHP' WHERE language_id = 1; RETURNING *;
Above statement returns updated rows from the coding table. You can also use join clause in Postgres update query.
We can modify the column(s) to the DEFAULT value(s) of the respected columns, by specifying the value(s) as DEFAULT for the column(s) in the SET clause of the UPDATE query.