LuMa LuMa - 5 months ago 13
MySQL Question

IF-ELSE in Postgres

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?

Answer

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;