Row numbering in PostgreSQL

Question

no - the order by in the windowing function and the order by clause of the select statement are functionally two different things.

Also, your statement produces: ERROR: window function call requires an OVER clause, so:

SELECT 30+row_number(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

should be:

SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

Note that if salaries are not unique then there is no guarantee that they will even produce the same order. Perhaps it would be better to do:

SELECT * 
FROM ( SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * 
       FROM users )
ORDER BY position LIMIT 30 OFFSET 30

Also note that if you are running this query several times with different offsets, you need to:

1. set your isolation level to serializable
2. make sure that whatever you are ordering by is unique

or you may get duplicates and missing rows. See the comments on this answer for why.