Search code examples
sqlpostgresqlcaselimit.when

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?

Solution

  • 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.