IF-ELSE in Postgres

Question

I'm failing to translate a SQL query which was designed for MySQL into Postgres syntax. This is the query:

select if(sendonly = true, 'REJECT', 'OK') AS access from accounts where username = '%u' and domain = '%d' and enabled = true LIMIT 1;

This nice little function "if()" is not available in Postgres. My first attempts with some CASE clauses failed. What needs to be modified to make this query run in Postgres?

Did you do any research? Googling "postgres if else" reveals postgresql.org/docs/9.1/static/plpgsql-control-structures.html which indicates there's a IF-THEN-ELSE structure. — ceejayoz
– ceejayoz, Commented Jun 12, 2016 at 20:08
@ceejayoz You might want to look at the page a little closer, that's about the server-side PL/pgSQL language, not SQL queries. — mu is too short
– mu is too short, Commented Jun 12, 2016 at 20:36

Mureinik · Accepted Answer · 2016-06-12 20:08:58Z

2

As you noted, you could use a case expression:

SELECT CASE WHEN sendonly = true THEN 'REJECT' ELSE 'OK' END AS access
FROM   accounts
WHERE  username = '%u' AND domain = '%d' AND enabled = true
LIMIT  1;

Or, as Postgres can evaluate booleans directly, you could trim this query down a bit:

SELECT CASE WHEN sendonly THEN 'REJECT' ELSE 'OK' END AS access
FROM   accounts
WHERE  username = '%u' AND domain = '%d' AND enabled
LIMIT  1;

answered Jun 12, 2016 at 20:08

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

Collectives™ on Stack Overflow

IF-ELSE in Postgres

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