CODEWITHSUNDEEP
postgresql
Chau Loi
Chau Loi

Reputation: 1225

PostgreSQL - How to add a new column with a default conditioning on another column?

I have a table like this

Table 1:

Code
 1
 2
 3
 4
 1
 2

I want to add a new column conditioning on the column code.

1 -> A
2 -> B
3 -> C
4 -> D

My expected result:

Table 1:

Code      Name
 1         A
 2         B
 3         C
 4         D
 1         A
 2         B

I expecting a code like this:

alter table table_1
add column Name varchar(64) set default case when Code = 1 then "A"
                                                  Code = 2 then "B"
                                                  Code = 3 then "C"
                                                  . . .

,

Upvotes: 0

Views: 54

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522731

One straightforward way would be to just maintain a table of codes, and then join to it:

CREATE TABLE codes (Code integer, Name varchar(5));
INSERT INTO codes (Code, Name)
VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'D');

SELECT t1.Code, t2.Name
FROM table_1 t1
INNER JOIN codes t2
    ON t1.Code = t2.Code;

Note that I vote against doing this update, because as your underlying code values change, you might be forced to do the update again. The above approach doesn't have that problem, and you get the correct name when you select.

Upvotes: 2

Related Questions