SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'

by

The common short term is just “Row values”. Or “Row value comparison” for the operation you demonstrate. That feature has been in the SQL standard since SQL-92 (!). Postgres is currently the only major RDBMS that supports it in all aspects – especially also with optimal index support.

In particular, the expression (col1, col2) < (1, 2) is just shorthand for ROW(col1, col2) < ROW(1, 2) in Postgres.
The expression ROW(col1, col2) is also called row constructor – just like ARRAY[col1, col2] is an array constructor.

It is conveniently short for the more verbose, equivalent expression:

col1 < 1 OR (col1 = 1 AND col2 < 2)

… and Postgres can use an index on (col1, col2) or (col1 DESC, col2 DESC) for this.

And notably distinct from: (!)

col1 < 1 AND  AND col2 < 2

Consider example: (1,1) …

Here is a presentation by Markus Winand that discusses the feature for pagination in detail:

“Pagination done the PostgreSQL way” on use-the-index-luke.com.

Row value comparison starts on page 20. The support matrix I have been referring to is on page 45.
I am in no way affiliated to Markus Winand.

More Related Contents:

  1. Optimize query with OFFSET on large table
  2. Redundant data in update statements
  3. Run a query with a LIMIT/OFFSET and also get the total number of rows
  4. How do I limit the number of rows returned by an Oracle query after ordering?
  5. Refactor a PL/pgSQL function to return the output of various SELECT queries
  6. Any downsides of using data type “text” for storing strings?
  7. Index for finding an element in a JSON array
  8. Query for array elements inside JSON type
  9. Calculating Cumulative Sum in PostgreSQL
  10. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  11. Is it possible to make a recursive SQL query?
  12. Change PostgreSQL columns used in views
  13. Way to try multiple SELECTs till a result is available?
  14. How to set auto increment primary key in PostgreSQL?
  15. PostgreSQL IF statement
  16. Get the default values of table columns in Postgres?
  17. Division of integers returns 0
  18. PostgreSQL: how to convert from Unix epoch to date?
  19. Postgres not using index when index scan is much better option
  20. SQL update fields of one table from fields of another one
  21. Postgres – Function to return the intersection of 2 ARRAYs?
  22. How to store array or multiple values in one column
  23. convert Postgres geometry format to WKT
  24. COPY with dynamic file name
  25. Table or column name cannot start with numeric?
  26. Count cumulative total in Postgresql
  27. PostgreSQL column ‘foo’ does not exist
  28. PostgreSQL: Full Text Search – How to search partial words?
  29. How to bulk insert only new rows in PostreSQL
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment