Postgresql select from based on condition

Question

How to run a given select statement based on condition?

If a condition (which comes from table_A) is true then select from table_B otherwise from table_C. Tables have no common column.

Something like this

select case when table_A.flag=true then   
    (select * from table_B ) 
    else
    (select * from table_C ) 
    end
from table_A where ...

The above one will fail of course : more than one row returned by a subquery used as an expression

Does this answer your question? [PostgreSQL IF statement](https://stackoverflow.com/questions/11299037/postgresql-if-statement) — Shmiel, May 18 '22 at 17:20
This `if` statement is plain sql. It would work as follows `if (select from ) = then (select * from table) else (select * from other_table)` — Shmiel, May 18 '22 at 18:23
Do these tables have the exact same columns (or are the columns you want from them exactly the same)? — EdmCoff, May 18 '22 at 18:32
@Shmiel `if` is not part of standard SQL (at least for PostgreSQL). It is part of pl/pgSQL of PostgreSQL. `case` does the same job as `if` in standard SQL. — özüm, Jan 15 '23 at 09:16

score 1 · Accepted Answer · answered May 18 '22 at 19:02

Since the columns are the same, you could use a UNION. Something like:

SELECT *
FROM Table_B
WHERE (SELECT flag FROM Table_A) = true
UNION ALL
SELECT *
FROM Table_C
WHERE (SELECT flag FROM Table_A) <> true

I'm assuming here that Table_A has only one row, but you could adjust the subquery in the WHERE conditions to get the flag however you need it.

The basic idea is that you set up the two conditions so that only one of them is true at a time (based on your flag). So, even though it is a UNION, only one part of the query will return results and you either end up with Table_B or Table_C.

Postgresql select from based on condition

1 Answers1