Using CASE in PostgreSQL to SELECT different FROMs

Question

I'll try to create a query where the result can be from two different tables depending on the size of the first table. Is something comparable even possible?

SELECT 
    CASE WHEN COUNT(table1.column1) > 5
    THEN
        column1,
        column2,
        column3
        FROM table1
    ELSE
        column1,
        column2,
        column3
        FROM table2
    END

With this code I got something like this:

ERROR:  syntax error at or near ","
LINE 4:   column1,

Did you tried put selected columns inside a simple bracket? SELECT CASE WHEN COUNT(var1) > 5 THEN (column1, column2, column3 FROM table1) ELSE ( column1, column2, column3 FROM table2) END. I have not tried, it is a wild guess. — Santhucool
– Santhucool, Commented Aug 1, 2016 at 10:02
@Santhucool with brackets the error message slightly differs but it still doesn't work : ERROR: syntax error at or near "FROM" LINE 4: FROM table1) — Qvery
– Qvery, Commented Aug 1, 2016 at 10:18
What is var1? Is it a table column? If yes, from which table? — Laurenz Albe
– Laurenz Albe, Commented Aug 1, 2016 at 10:20
@Qvery did you tried sub query inside brackets I mean select. — Santhucool
– Santhucool, Commented Aug 1, 2016 at 11:04

Clodoaldo Neto · Accepted Answer · 2016-08-01 11:12:54Z

4

with c (c) as (select count(c1) from t)
select c1, c2, c3
from t
where (select c from c) > 5
union all
select c1, c2, c3
from r
where (select c from c) <= 5

The corresponding columns must be of the same type. Or be casted to the same type.

WITH clause

UNION clause

edited Aug 1, 2016 at 11:12

answered Aug 1, 2016 at 11:06

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

Using CASE in PostgreSQL to SELECT different FROMs

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