CODEWITHSUNDEEP
sqlpostgresql
Bharat Bittu
Bharat Bittu

Reputation: 525

How to add new column to table with the value corresponding to the same table?

There is a status column in my table with int values. How can I assign a value to the int or do I have to create a new column in the table?

I have tried to ALTER table but what is the best method?

select status from table1;

If I run the above query we get -

id status
1  1
2  2
3  1
4  5

I want to get output -

id  status
1   Accepted
2   Completed
3   Accepted
4   Declined

Upvotes: 0

Views: 433

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

The correct case expression would be:

select id,
       (case status
          when 1 then 'Accepted'
          when 2 then 'Completed'
          when 5 then 'Declined'
        end) as status
from table1;

You can also do this with a join to a derived table:

select t1.id, v.status
from table1 t1 left join
     (value (1, 'Accepted'), (2, 'Completed'), (5, 'Declined')
     ) v(status_int, status)
     on t1.status = v.status_int;

I mention this because you should probably have a reference table for the status values. In this case, the reference table is created on the fly in the query. But it should probably be a real table in the database.

Upvotes: 1

Kemal AL GAZZAH
Kemal AL GAZZAH

Reputation: 1037

USE case expression, postgres

select status,
       case 
         when status=1 then 'Accepted'
         when status=2 then 'Completed'
         when status=3 then 'Accepted'
         when sttaus=4 then 'Declined'
       end mystatus
from table1;

Upvotes: 1

Ankit Deshpande
Ankit Deshpande

Reputation: 3604

You can use case, refer to this SO Question PostgreSQL CASE ... END with multiple conditions. The query will look something like this:

SELECT
 id, 
 CASE
  WHEN (status = 1)  THEN 'Accepted'  
  WHEN status=2 then 'Completed'
  WHEN status=3 then 'Accepted'
  WHEN sttaus=4 then 'Declined'
 END AS status

FROM table1 ;

Upvotes: 1

Related Questions