Is there a way in POSTGRESQL to limit a resulting query given a condition?
I have tried something like this:
SELECT *
FROM table
LIMIT CASE
WHEN extract(hour from now())=9 then 143
WHEN extract(hour from now())=10 then 178 ETC.`
I need to use this because depending on the current hour and the hour in the table, dinamically limit with the condition.
Any thoughts or ideas?
You can use row_number():
SELECT t.*
FROM (SELECT t.*, ROW_NUMBER() OVER (ORDER BY ?) as seqnum
FROM table t
) t
WHERE (extract(hour from now()) = 9 AND seqnum <= 143) OR
(extract(hour from now()) = 10 AND seqnum <= 178) OR
. . .
The more likely solution would be to handle this at the application layer.
Note the ?: this represents the column to use for ordering the data. Normally when using LIMIT, you want ORDER BY.
CASE statement. Thanks.https://www.postgresql.org/docs/current/static/queries-limit.html
LIMIT { number | ALL }
you can't use expression here, like you do with ORDER BY
https://www.postgresql.org/docs/current/static/queries-order.html
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }] [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]
you need dynamic SQL here, for examplelook at PostgreSQL parameterized Order By / Limit in table function
LIMIT CASE WHEN extract(hour from now())=11 and replace(to_char(now(),'day'),' ','')='thursday' then 109 end