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
– Gordon Linoff

2020-12-08 22:07:32 +00:00
Commented Dec 8, 2020 at 22:07 — Gordon Linoff
– Gordon Linoff, Commented Dec 8, 2020 at 22:07

GMB · Accepted Answer · 2020-12-08 22:07:36Z

1

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
;

answered Dec 8, 2020 at 22:07

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Creating Temporary Columns in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related