CODEWITHSUNDEEP
mysqlsqlpostgresql
Bertaud
Bertaud

Reputation: 2918

the limit clause with postgresql

how can I transform this mysql limit clause for Postgresql:

limit $a,$b

when possible not a specific postgresql solution but rather a sql standard

Query failed: ERREUR: la syntaxe LIMIT #,# n'est pas supportée

EDITION

"select * from preference where (id_membre ='184') order by 1 LIMIT 0 OFFSET 8";

return 4 rows with mysql, 0 rows with postgresl !

Upvotes: 0

Views: 4294

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

Limit 0 is doing exactly what it says it is doing. Limiting to 0 rows! The query probably runs really fast too!

It isn't surprising to me at all that PostgreSQL has a very literal view of what limit 0 means. Nor, unfortunately, is it surprising to me that MySQL has a rather idiosyncratic view of the same

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The Postgres syntax is limit x offset y as explained here.

If you still have a problem with the variables, you can replace this with a where clause. If you have a query like this:

select . . .
order by <something>

You can get what you want with:

select t.*  -- or list of columns without seqnum
from (select . . ., row_number() over (order by <something>) as seqnum
     ) t
where seqnum between $a and $a + $b

Upvotes: 1

Related Questions