Re: syntax error at or near "ROWS"

Hi Dave

Well, as you can see, it works occasionally and it always works with fixed values

You can read more about it here https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-fetch/

/ Kristian

> Hi Kristian,

> As far as I know PostgreSQL does not support OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

> Dave Cramer
> www.postgres.rocks

> On Thu, 19 Sept 2024 at 09:06, Kristian Ivarsson <sten(dot)kristian(dot)ivarsson(at)gmail(dot)com<mailto:sten(dot)kristian(dot)ivarsson(at)gmail(dot)com>> wrote:
> Hi Dave

> No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH FIRST ? ROWS ONLY (because OFFSET ? LIMIT ? is non standard SQL)

> The only reason I added the LIMIT-statement example was to show the differences in the behaviour

> When $1 is "abc" it seems like the rest comes out as $2::int4 and $3::int4

> When $1 is "abcde" it seems like rest comes out as $2 and $3

> Only "abcde" works with FETCH FIRST-statement

> Both "abc" and "abcde" works with the LIMIT-statement (it seems like the following parameters has different types though (as stated))

> / Kristian

> > HI Kristian,

> > So have you solved your issue ?

> > Dave Cramer
> > www.postgres.rocks

> > > I have dug into this a bit more and turned on the postmaster log

> > > This statement fails if the first parameter is "abc" but works with "abcde"

> > > SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY

> > > The postmaster log with "abc" (cbColDef and cbValueMax both 3)

> > > 2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 116
> > > 2024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLY

> > > The postmaster log with "abcde" (cbColDef and cbValueMax both 5)

> > > 2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY
> > > 2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'

> > > By changing the statement a bit (using OFFSET ? and LIMIT ?) it works with both with "abc" and "abcde"

> > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int4
> > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abc', $2 = '0', $3 = '1'
> > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>: SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $3
> > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 = 'abcde', $2 = '0', $3 = '1'

> > > There's a lot of fishy things going on (where subsequent parameters sometimes seems to be serialized as strings depending of the size of earlier string but not always), but I won't speculate of the reasons

> > > We want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement

> > > / Kristian