0

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?

ecp
  • 319
  • 1
  • 6
  • 18
  • What you wrote should work. What's the issue? – 404 May 03 '18 at 09:33
  • Without an ORDER BY the LIMIT makes little sense. – joop May 03 '18 at 09:33
  • @eurotrash gives me the following error: SQL Error [4856] [42601] Syntax error. `LIMIT CASE WHEN extract(hour from now())=11 and replace(to_char(now(),'day'),' ','')='thursday' then 109 end` – ecp May 03 '18 at 09:38
  • @joop I have an order by clause. – ecp May 03 '18 at 09:39
  • you probably look for function that returns query?.. e.g. https://stackoverflow.com/questions/8139618/postgresql-parameterized-order-by-limit-in-table-function – Vao Tsun May 03 '18 at 09:45
  • @ecp it works fine for me: `SELECT * FROM generate_series(1, 10) g LIMIT CASE WHEN extract(hour from now())=11 and replace(to_char(now(),'day'),' ','')='thursday' then 109 ELSE 4 end` returns 4 items. – 404 May 03 '18 at 09:46
  • @VaoTsun yes, everything that returns a result depending on day and hour, and limiting it depending on the day and hour. – ecp May 03 '18 at 09:47
  • @eurotrash the same you wrote give me this: SQl Error [4256] Only reltaions and subqueries are allowed in the FROM clause. – ecp May 03 '18 at 09:48
  • It looks like a Vertica problem. Maybe I could try to use a rank or something else. – ecp May 03 '18 at 09:51
  • 1
    Wait a minute - Vertica? Why did you use the PostgreSQL tag then? – Laurenz Albe May 03 '18 at 09:54
  • You can't have a LIMIT clause that changes for every row. It's unclear what exactly you are trying to achieve. Please **[edit]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments) –  May 03 '18 at 09:59

2 Answers2

1

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

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786