When working with databases, it's often useful to retrieve only a subset of rows rather than the entire dataset. PostgreSQL offers the LIMIT clause for this purpose. In this tutorial, you’ll learn how to use the LIMIT clause with PostgreSQL in Python using the psycopg2 library.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. What is LIMIT in SQL?

4. Using LIMIT in Python with PostgreSQL

5. Using LIMIT with OFFSET

6. Full Example

7. Tips and Common Pitfalls

1. What You’ll Learn

• How to use the LIMIT clause in PostgreSQL

• How to apply LIMIT with Python and psycopg2

• How to combine LIMIT with OFFSET for pagination

• Best practices when retrieving limited records

✅ 2. Prerequisites

Before you begin:

• Python installed

• PostgreSQL installed and running

• psycopg2 library installed:

pip install psycopg2-binary

• An existing table in PostgreSQL (we'll use a table called students)

3. What is LIMIT in SQL?

The LIMIT clause in SQL is used to restrict the number of rows returned by a SELECT query.

Basic Syntax:

SELECT column1, column2 FROM table_name LIMIT number;

This returns the first number rows from the result set.

4. Using LIMIT in Python with PostgreSQL

Example: Get the first 3 students

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

cur.execute("SELECT * FROM students LIMIT 3")
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

⏭️ 5. Using LIMIT with OFFSET

Use OFFSET to skip rows. This is helpful for pagination.

SELECT * FROM students LIMIT 3 OFFSET 3;

This query skips the first 3 records and returns the next 3.

Python Example:

cur.execute("SELECT * FROM students LIMIT %s OFFSET %s", (3, 3))
rows = cur.fetchall()
for row in rows:
    print(row)

 Always use placeholders (%s) to safely inject variables.

6. Full Working Example

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )

    cur = conn.cursor()

    # Retrieve 5 records starting from the 6th
    limit = 5
    offset = 5

    cur.execute("SELECT * FROM students LIMIT %s OFFSET %s", (limit, offset))
    results = cur.fetchall()

    print(f"Showing records {offset + 1} to {offset + limit}:")
    for row in results:
        print(row)

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

7. Tips and Common Pitfalls

Conclusion

The LIMIT clause is an essential tool for controlling the amount of data retrieved from your PostgreSQL database. Whether you're building a dashboard, API, or web app, using LIMIT and OFFSET efficiently helps improve performance and usability.