CODEWITHSUNDEEP
postgresql
user124123
user124123

Reputation: 1683

Create 2 new conditional columns with dependence postgresql

I want to create two new columns from a query in postgresql, one depending on existing data, and the other depnding on the new column, i.e

existing_col   new_col   new_col2
a              1         2
b              0         0

I have tried:

select existing_col, 
case when existing_col like 'a' then 1 else 0 end  as new_col  
case when new_col like 1 then 2 else 0 end as new_col2
from table

however this is giving me the error that new_col doesn't exist, how can I achieve this?

Upvotes: 1

Views: 86

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

updated:

(I modified your qry a little no avoid like operator for integers)

t=# create table "table" (existing_col text);
CREATE TABLE
Time: 50.189 ms
t=# insert into "table" values('a'),('b');
INSERT 0 2
Time: 0.911 ms
t=# select *,case when new_col like 1 then 2 else 0 end as new_col2
t-#     from (
t(#       select existing_col,
t(#       case when existing_col like 'a' then 1 else 0 end as new_col
t(#     from "table") al
t-# ;
ERROR:  operator does not exist: integer ~~ integer
LINE 1: select *,case when new_col like 1 then 2 else 0 end as new_c...
                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
Time: 0.514 ms
t=#     select *,case when new_col = 1 then 2 else 0 end as new_col2
t-#     from (
t(#       select existing_col,
t(#       case when existing_col like 'a' then 1 else 0 end as new_col
t(#     from "table") al
t-# ;
 existing_col | new_col | new_col2
--------------+---------+----------
 a            |       1 |        2
 b            |       0 |        0
(2 rows)

Time: 0.347 ms

as in docs:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

Upvotes: 1

Related Questions