Hello! In this article we will discuss how to update multiple columns in PostgreSQL? This is a SQL developer question and usually considered as a beginner question.

Updates in PostgreSQL

UPDATE is a verb for updating values of rows. We use update to change values in a table.

As a side note, just to mention here, update operation generates transaction log and logged to Write Ahead Logging (WAL) by default.

You can find more information about UPDATE documentation of PostgreSQL.

UPDATE statements are consist of TWO parts in general, first part is UPDATE clause, which points which columns to be updated with new values. Second part of the the UPDATE sentence is a WHERE clause which is used to filter the rows to be updated.

PostgreSQL Update Statement Performance

As mentioned in previous chapter, update statement consist of two parts, those two parts effect the performance of the update statement. Performance is an important factor of question how to update multiple columns in PostgreSQL topic

To write performant update statements, you need to keep where clause as clean as possible. If where clause of the statement includes many joins, aggregations and/or data manipulations, that will effect of the statement. If a table’s sort performance is low, rather not to join that table in our update statement.

The next sentence can be speculative for new generation, but for the previous generations will accept with this. First sentence of the update is being effected by the columns to be updated, distance between those columns effects the performance. New generations only experienced servers with flash / ssd storage and distance impact is minimal.

Considering performance effect of the update where statement is important.

Updating Multiple Columns in PostgreSQL

As a demonstration i will create an imaginary table.

CREATE TABLE public.users (
	id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE),
	mail varchar(100) NULL,
	"name" varchar(150) NULL,
	CONSTRAINT users_pk PRIMARY KEY (id),
	CONSTRAINT users_un UNIQUE (mail)
)

Let’s insert some data into our imaginary users table.

INSERT INTO public.users (id, mail, "name") VALUES(1, 'yigit@openbasesystems.com', 'Yiğit Özdemir');
INSERT INTO public.users (id, mail, "name") VALUES(2, 'yigit2@openbasesystems.com', 'Yiğit Özdenir');
INSERT INTO public.users (id, mail, "name") VALUES(3, 'yigit3@openbasesystems.com', 'Yiğit Özdezir'); 
INSERT INTO public.users (id, mail, "name") VALUES(4, 'testmail6@test.com', 'Test Name');
INSERT INTO public.users (id, mail, "name") VALUES(6, 'testmail5@test.com', 'Test Name');
INSERT INTO public.users (id, mail, "name") VALUES(7, 'testmail4@test.com', 'Test Name');
INSERT INTO public.users (id, mail, "name") VALUES(8, 'testmail3@test.com', 'Test Name');
INSERT INTO public.users (id, mail, "name") VALUES(9, 'testmail2@test.com', 'Test Name');
INSERT INTO public.users (id, mail, "name") VALUES(10, 'testmail1@test.com', 'Test Name');

Alright! We have the data.

Performing the Update

Our purpose was updating multiple columns, at once.

update users set mail ='UpdatedMail@multiple.com', "name"='Updated Name' where id = 6

If you run this statement, your tool will tell you how many rows is effected by this query.

The example above shows how to update multiple columns in PostgreSQL.

The Syntax

Syntax we use to update tables is very clear.

UPDATE table_name SET column1=value1, …., columnn=valuen WHERE filter condition;

This syntax applies to all database engines, as well as postgresql.

To Sum Up

You can update multiple columns in PostgreSQL at once, you can update multiple rows at once via manipulating the filter.

As a suggestion, run your filter as a SELECT statement before manipulating the production data and check your filter.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch