”;
While executing a PostgreSQL SELECT statement you can limit the number of records in its result using the LIMIT clause.
Syntax
Following is the syntax of the LMIT clause in PostgreSQL −
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
Example
Assume we have created a table with name CRICKETERS using the following query −
postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=#
And if we have inserted 5 records in to it using INSERT statements as −
postgres=# insert into CRICKETERS values (''Shikhar'', ''Dhawan'', 33, ''Delhi'', ''India''); INSERT 0 1 postgres=# insert into CRICKETERS values (''Jonathan'', ''Trott'', 38, ''CapeTown'', ''SouthAfrica''); INSERT 0 1 postgres=# insert into CRICKETERS values (''Kumara'', ''Sangakkara'', 41, ''Matale'', ''Srilanka''); INSERT 0 1 postgres=# insert into CRICKETERS values (''Virat'', ''Kohli'', 30, ''Delhi'', ''India''); INSERT 0 1 postgres=# insert into CRICKETERS values (''Rohit'', ''Sharma'', 32, ''Nagpur'', ''India''); INSERT 0 1
Following statement retrieves the first 3 records of the Cricketers table using the LIMIT clause −
postgres=# SELECT * FROM CRICKETERS LIMIT 3; first_name | last_name | age | place_of_birth | country ------------+------------+-----+----------------+------------- Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | 38 | CapeTown | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka (3 rows)
If you want to get records starting from a particular record (offset) you can do so, using the OFFSET clause along with LIMIT.
postgres=# SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2; first_name | last_name | age | place_of_birth | country ------------+------------+-----+----------------+---------- Kumara | Sangakkara | 41 | Matale | Srilanka Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India (3 rows) postgres=#
Limit clause using python
Following python example retrieves the contents of a table named EMPLOYEE, limiting the number of records in the result to 2 −
Example
import psycopg2 #establishing the connection conn = psycopg2.connect( database="mydb", user=''postgres'', password=''password'', host=''127.0.0.1'', port= ''5432'' ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ''''''SELECT * from EMPLOYEE LIMIT 2 OFFSET 2'''''' #Executing the query cursor.execute(sql) #Fetching the data result = cursor.fetchall(); print(result) #Commit your changes in the database conn.commit() #Closing the connection conn.close()
Output
[(''Sharukh'', ''Sheik'', 25, ''M'', 8300.0), (''Sarmista'', ''Sharma'', 26, ''F'', 10000.0)]
”;