Postgresql, insert value from a column

Question

Right now, I have this table:

id|name|status (varchar)|fk_id_test (int)
-----------------------------------------
1 |ABC | X              |
2 | DX | Y              |
3 | ZZ | X              |
4 | TU | Y              |
5 | EE | Z              |

I have to insert in fk_id_test values coming from status column.

The algorithm would be something like:

switch(status)
    case 'X': `fk_ik_test` = 1
    case 'Y': `fk_ik_test` = 2
    case 'Z': `fk_ik_test` = 3
etc.

The result should be this:

Right now, I have this table:

id|name|status (varchar)|fk_id_test (int)
-----------------------------------------
1 |ABC | X              |1
2 | DX | Y              |2
3 | ZZ | X              |1
4 | TU | Y              |2
5 | EE | Z              |3

I have 5 status. This is because I will use another table (test) with the names of those status (varchar).

It's possible to do this in postgresql?

Gordon Linoff · Accepted Answer · 2019-05-15 20:08:29Z

1

One method uses an update with a join:

update t
    set fk_id_test = v.fk_id_test
    from (values ('X', 1), ('Y', 2), ('Z', 3)) v(status, fk_id_test)
    where v.status = t.status;

answered May 15, 2019 at 20:08

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

pmiranda Over a year ago

Wonderful and flawless. Took 1:40 min to update my 16M registries :D

Zaynul Abadin Tuhin · Accepted Answer · 2019-05-15 20:03:45Z

1

do you need update

 update table_name
 set fk_id_test= case status when 'X' then 1
    when 'Y' then 2
    when 'Z' then 3 end

answered May 15, 2019 at 20:03

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Comments

jarlh · Accepted Answer · 2019-05-15 19:58:20Z

0

Are you looking for a case expression?

case status
    when 'X' then 1
    when 'Y' then 2
    when 'Z' then 3
etc.

end as fk_ik_test

edited May 15, 2019 at 19:58

answered May 15, 2019 at 19:57

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

3 Comments

pmiranda Over a year ago

I need to persist the data in the table, I need to create new data in this new column, The table didn't have it, now was recently created and empty of course.

jarlh Over a year ago

That's not adviced. You could create a view instead, adding the fk_ik_test column.

pmiranda Over a year ago

I have to fill the new field

Collectives™ on Stack Overflow

Postgresql, insert value from a column

3 Answers 3

1 Comment

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Related