OFFSET and FETCH FIRST - Row Limiting - SQL Server to PostgreSQL Migration

In SQL Server 2012 or later, you can use the OFFSET and FETCH FIRST (or NEXT) clause to limit the rows returned by the query. In PostgreSQL you can use the OFFSET and LIMIT clauses.

Consider the following sample table:

  CREATE TABLE products (name VARCHAR(30));
 
  INSERT INTO products VALUES ('Apple');
  INSERT INTO products VALUES ('Melon');
  INSERT INTO products VALUES ('Orange');

SQL Server:

  -- Select 1 row only but with offset 2
  SELECT name FROM products ORDER BY name OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY;
  /* Orange */
 
  -- Select all rows but with offset 1
  SELECT name FROM products ORDER BY name OFFSET 1 ROWS;
  /* Melon */  
  /* Orange */

PostgreSQL:

  -- Select 1 row only but with offset 2
  SELECT name FROM products ORDER BY name OFFSET 2 LIMIT 1;
  /* Orange */
 
  -- Select all rows but with offset 1
  SELECT name FROM products ORDER BY name OFFSET 1;
  /* Melon */  
  /* Orange */

For more information, see SQL Server to PostgreSQL Migration.