How to test in select clause using CASE if a value pertains to a list of predefined string in Postgr-CodePudding

I'd like to write postgresql code that defines a table B based on table A values, according to A field of string type named d, if it matches any element of predefined string lists. Table A contains 2 fields: c and d, d is a string.

Here is what I've wrote: create table B as select c, CASE when d in ("A1", "A2", "A3") then 1 end as TYPE1, CASE when d in ("B1", "B2", "B3") then 1 end as TYPE2 from A;

I'd like TYPE1 field to be set to 1 if d is any value among "A1", "A2","A3" and TYPE2 field to be set to 1 if d is any value among "B1","B2",B3".

Here's the error I get: column "A1" does not exist.

In the code that I will finally write the string values for which TYPE1 or TYPE2 are to be set to 1, cannot be simply described by a matching pattern as in this snippet Thanks for your help

CodePudding user response：

String literals are enclosed with ':

create table B as select c, CASE  when d in ('A1', 'A2', 'A3') then 1 end as TYPE1, CASE  when d in ('B1', 'B2', 'B3') then 1 end as TYPE2 from A;

CodePudding user response：

It works just changing '"' surrounding strings "A1", "A2" ... by single quotes 'A1', 'A2' , 'A3'