postgresqlHow do I use the PostgreSQL row_number function?
The PostgreSQL row_number() function is used to assign a unique integer value to each row in a result set. This can be useful for pagination, ordering, and other operations.
Example code
SELECT row_number() OVER (ORDER BY name) AS rn, name FROM customer;
Output example
rn | name
----+--------
1 | Alice
2 | Bob
3 | Charlie
Code explanation
SELECT row_number()
- This is the function call. It assigns a unique integer value to each row.OVER (ORDER BY name)
- This is an optional clause that specifies the order in which the row numbers should be generated. In this example, the rows are numbered in ascending order of thename
column.AS rn
- This is an optional clause that assigns an alias to the row number column. In this example, the row number column is namedrn
.name FROM customer
- This is the source data. In this example, the row numbers are generated for thename
column from thecustomer
table.
Helpful links
More of Postgresql
- How do I use the PostgreSQL hash function?
- How can I retrieve data from PostgreSQL for yesterday's date?
- How can I troubleshoot zero damaged pages in PostgreSQL?
- How can I use PostgreSQL's "zero if null" feature?
- How do I use PostgreSQL's XMLTABLE to parse XML data?
- How can I set a PostgreSQL interval to zero?
- How can I use PostgreSQL with YAML?
- How can I use PostgreSQL and ZFS snapshots together?
- How do I use PostgreSQL's XMIN and XMAX features?
- How do I set the PostgreSQL work_mem parameter?
See more codes...