CODEWITHSUNDEEP
sqlpostgresqlcaselimit.when
ecp
ecp

Reputation: 327

POSTGRESQL limit a resulting query given a condition

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?

Upvotes: 0

Views: 1661

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

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.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51649

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

Upvotes: 1

Related Questions