PostgreSQL - create a function that is using data from two columns to update another column

Question

I need to create a PostgreSQL function that will use the values from two columns - "column1" and "column2" - to update data in "column3".

The data in "column1" and "column2" already exists. I just need to use the data included in those two columns (with some kind of loop) to update "column3" (like "column1" space "column2").

All columns are part of the same table and they are VARCHAR. Any help would be greatly appreciated.

RichardTheKiwi · Accepted Answer · 2011-01-16 23:19:35Z

3

PostgreSQL uses || for string concatenation (joining). So you can use a single update statement that will affect multiple rows at the same time

update tbl
set column3 = column1 || ' ' || column2
where ... (optional clause to identify records to update)

answered Jan 16, 2011 at 23:19

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

avatar Over a year ago

Do I just use the column1 and column2 names? How does it know I'm referring to the columns form the same table and not some other values?

user330315 Over a year ago

It knows which table to update because you are writing UPDATE tbl - which specifies the table. You should really go through a good SQL tutorial first before proceeding any further. The one in the PostgreSQL manual is a good start.

RichardTheKiwi Over a year ago

To add to that, it is updating, for each row found (all, if not limited by the WHERE clause), the column3 value with the values of column1+space+column2 of the same row.

avatar Over a year ago

I was getting an error that's why I asked. I think the problem is related to something else.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-01-16 23:21:33Z

2

This will update column3 in all rows.

update your_table
set column3 = column1 || ' ' || column2

answered Jan 16, 2011 at 23:21

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - create a function that is using data from two columns to update another column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related