Creating Temporary Columns in PostgreSQL

Question

I have a table with the following:

| Customer | Order Count|
 -----------------------
|     1    |      1     |
|     2    |      2     |
|     3    |      1     |

and I want to create an additional column so I end up with the following:

| Customer | Order Count| Status |
 --------------------------------
|     1    |      1     |  new   |
|     2    |      2     |  old   |
|     3    |      1     |  new   |

How can I structure my query in order to do so?

edit: the logic for the status labeling is that new customers only have one order, and old customers have > 1

And what is the logic for `status`? – Gordon Linoff Dec 08 '20 at 22:07 — Gordon Linoff, Dec 08 '20 at 22:07

score 1 · Accepted Answer · answered Dec 08 '20 at 22:07

Assuming that 1 means "new" and 2 means "old", you can use a case expression:

select t.*, 
    case order_count 
        when 1 then 'new' 
        when 2 then 'old'
        else '??'
    end as status
from mytable t

Or, if you want to create a computed column:

alter table mytable 
    add column status varchar(10)
    generated always as (
        case order_count 
            when 1 then 'new' 
            when 2 then 'old'
            else '??'
        end 
    )
    stored
;

Creating Temporary Columns in PostgreSQL

1 Answers1