Swapping column values in PostgreSQL

Question

In my PostgreSQL database, I have a table with two text values, t1 and t2:

|   id   |   t1   |   t2   |  
|   1    |  abcd  |   xyz  |  
|   2    |  aazz  |   rst  |  
|   3    |  fgh   | qwerty |

I would like to swap the values of the columns t1 and t2 for every row in the table in a way that, using the above example, this would be the result:

|   id   |   t1   |   t2   |
|   1    |  xyz   |   abcd |
|   2    |  rst   |   aazz |
|   3    | qwerty |   fgh  |

Also, let's suppose the values from all rows with id=4 onwards (4, 5, 6...) are already correct, is it possible to filter which rows I want to swap?
I've tried this (for MySQL Databases) but none of the solutions worked.

user330315 · Accepted Answer · 2020-07-08 20:27:57Z

26

That's a simple UPDATE:

update the_table
  set t1 = t2, 
      t2 = t1
where id < 4;

Unlike MySQL, Postgres does this correctly.

answered Jul 8, 2020 at 20:27

user330315

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

Comments

Mike Organek · Accepted Answer · 2020-07-08 20:28:31Z

19

select * from swapit;
 id |  t1   |   t2
----+-------+--------
  1 | abcd  | xyz
  2 | aazz  | rst
  3 | fgh   | qwerty
  4 | first | second
  5 | first | second
(5 rows)

update swapit set t1 = t2, t2 = t1 where id <= 3;
UPDATE 3

select * from swapit order by id;
 id |   t1   |   t2   
----+--------+--------
  1 | xyz    | abcd
  2 | rst    | aazz
  3 | qwerty | fgh
  4 | first  | second
  5 | first  | second
(5 rows)

answered Jul 8, 2020 at 20:28

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

4 Comments

Abelisto Over a year ago

Everybody forgets about tasty PostgreSQL feature: set (t1, t2) = (t2, t1)

taffit Over a year ago

Worth mentioning that @abelisto 's comment is for the normal UPDATE-statemtent. I searched for a PL/PGSQL-statement SET for assigning multiple variables at once, which obviously doesn't exist.

Abelisto Over a year ago

@taffit select t1, t2 into t2, t1; If you mean C-style a = b = 1; then yes, pl/sql based on Pascal/Ada syntax where the assignment statement does not returns value.

taffit Over a year ago

@abelisto Correct, thanks for the clarification. Indeed I checked for a PL/PGSQL SET-command, which doesn't exist. Using SELECT ... INTO ... helps with the swap, while the syntax you mentioned works perfectly on an UPDATE ... SET ...-statement.

Collectives™ on Stack Overflow

Swapping column values in PostgreSQL

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related