Write SELECT CASE Statement with PostgreSQL pg and Node JS

Question

I am using a RESTful API with Node.js/Express and a PostgreSQL database ( PostgreSQL version is 12.4). I'm not using an ORM just straight SQL. I am trying to create a "SELECT CASE" statement but I keep running into errors. The error messages I am getting are "confirmed_member is not a column". Can anyone see what is wrong with my syntax here:

var query = ['SELECT *, CASE WHEN confirmed = true THEN "confirmed_member" ELSE "pending" END AS "status" FROM members WHERE groupid = $1']

try {
   const member = await pool.query(query, [groupid]);
   res.status(200).json(member.rows) 
} catch (err) {
   res.status(400).json({ message: err.message })
}

Note: The query works fine in Valentina DB.

EDIT: In response to answers below, I switched to double quotes because this was the error I was getting in the IDE when I used single quotes

Tim Biegeleisen · Accepted Answer · 2021-01-18 15:46:41Z

2

Use single quotes for string literals in SQL. Some flavors of SQL (e.g. SQLite) also accept double quotes, but you should not rely on this behavior.

var query = ["SELECT *, CASE WHEN confirmed = true THEN 'confirmed_member' ELSE 'pending' END AS status FROM members WHERE groupid = $1"];

answered Jan 18, 2021 at 15:46

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

4 Comments

GraSim Over a year ago

I've included an image which showed the error I was getting in my IDE when I used single quotes but your answer has given me a brain wave. I will try enclosing the query itself in double quotes and try that : var query = ["SELECT *, CASE WHEN confirmed = true THEN 'confirmed_member' ELSE 'pending' END AS 'status' FROM members WHERE groupid = $1"]

GraSim Over a year ago

Upvoting your answer, because this also worked. I didn't realise I could write the COLUMN name without enclosing in quotes. Thanks

Tim Biegeleisen Over a year ago

@GraSim Well it is also correct to place the column name in double quotes, but it is only required if the column name be a reserved SQL keyword, contains spaces, or is some otherwise not standard name.

GraSim Over a year ago

You learn something new everyday, all my life I've been enclosing my columns. Thanks Tim!

Popeye · Accepted Answer · 2021-01-18 15:47:06Z

0

You need to use single quote for the literals as follows:

SELECT *,
       CASE WHEN confirmed = true THEN 'confirmed_member' ELSE 'pending' END AS "status" 
  FROM members WHERE groupid = $1

answered Jan 18, 2021 at 15:47

Popeye

36k4 gold badges12 silver badges31 bronze badges

1 Comment

GraSim Over a year ago

I've included an image which showed the error I was getting in my IDE when I used single quotes but your answer has given me a brain wave. I will try enclosing the query itself in double quotes and try that : var query = ["SELECT *, CASE WHEN confirmed = true THEN 'confirmed_member' ELSE 'pending' END AS 'status' FROM members WHERE groupid = $1"]

GraSim · Accepted Answer · 2021-01-18 16:21:11Z

0

For anyone who comes across the same issue: I got it working by enclosing the SQL statement in double quotes and escaping the double quotes for the column name.

var query = ["SELECT *, CASE WHEN confirmed = true THEN 'confirmed_member' ELSE 'pending' END AS \"status\" FROM members WHERE groupid = $1"]

answered Jan 18, 2021 at 16:21

GraSim

4,2901 gold badge35 silver badges41 bronze badges

Collectives™ on Stack Overflow

Write SELECT CASE Statement with PostgreSQL pg and Node JS

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related