How to limit query result in PostgreSQL

Limit clause used to restrict the rows returned

Limit used to find top N records

Syntax:-

SELECT select_list
FROM table_expression
[ ORDER BY … ]
[ LIMIT { number | ALL } ] [ OFFSET number ]

OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as omitting the OFFSET clause, as is OFFSET with a NULL argument.

If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned.

Below examples will give more insight

  1. LIMIT ALL
  2. LIMIT number
  3. LIMIT and OFFSET
  4. LIMIT with query count

LIMIT ALL

LIMIT ALL returns all records

postgres=# select count(*) from emp;
 count 
-------
     9
(1 row)

postgres=# select count(*) from emp limit all;
 count 
-------
     9
(1 row)

LIMIT number

LIMIT number returns same number of rows if available in table. If specified LIMIT will be applied after order by clause.

postgres=# select empno,empname from emp;
 empno | empname  
-------+----------
    11 | Solomon
    10 | Bill
    12 | Susan
    13 | Wendy
    14 | Benjamin
    15 | Tom
    16 | Henry
    17 | Robert
    18 | Paul
(9 rows)

postgres=# select empno,empname from emp order by empname asc;
 empno | empname  
-------+----------
    14 | Benjamin
    10 | Bill
    16 | Henry
    18 | Paul
    17 | Robert
    11 | Solomon
    12 | Susan
    15 | Tom
    13 | Wendy
(9 rows)

postgres=# select empno,empname from emp order by empname asc limit 3;
 empno | empname  
-------+----------
    14 | Benjamin
    10 | Bill
    16 | Henry
(3 rows)

postgres=# select empno,empname from emp limit 3;
 empno | empname 
-------+---------
    11 | Solomon
    10 | Bill
    12 | Susan
(3 rows)

postgres=# select empno,empname from emp limit 50;
 empno | empname  
-------+----------
    11 | Solomon
    10 | Bill
    12 | Susan
    13 | Wendy
    14 | Benjamin
    15 | Tom
    16 | Henry
    17 | Robert
    18 | Paul
(9 rows)

LIMIT and OFFSET

OFFSET says to skip that many rows before beginning to return rows

OFFSET 0 is the same as omitting the OFFSET clause

OFFSET applies before LIMIT

postgres=# select empno,empname from emp;
 empno | empname  
-------+----------
    11 | Solomon
    10 | Bill
    12 | Susan
    13 | Wendy
    14 | Benjamin
    15 | Tom
    16 | Henry
    17 | Robert
    18 | Paul
(9 rows)

postgres=# select empno,empname from emp limit 5 offset 2;
 empno | empname  
-------+----------
    12 | Susan
    13 | Wendy
    14 | Benjamin
    15 | Tom
    16 | Henry
(5 rows)

LIMIT with query count

LIMIT can be used with query count, see below example

postgres=# select empno,empname,deptno from emp limit (select count(*) from dept where deptno = 1);
 empno | empname | deptno 
-------+---------+--------
    11 | Solomon |      5
(1 row)


postgres=# select empno,empname,deptno from emp limit (select count(*) from dept) offset (select count(*) from dept where deptno = 1);
 empno | empname  | deptno 
-------+----------+--------
    10 | Bill     |      5
    12 | Susan    |      5
    13 | Wendy    |      1
    14 | Benjamin |      1
    15 | Tom      |      1
(5 rows)

LIMIT clause is very handy, In Oracle we use rownum clause to limit the number, this is some what similar