PostgreSQL tip of the day: getting SELECTive

Have you ever written a PostgreSQL SELECT query and wished you had more control over the rows that are returned?

It is an issue we often tackle when proofing and analyzing data for our clients. We chose PostgreSQL for data management because it is cross-platform, open source, freely available, often includes new SQL features before other databases, and closely follows the SQL standard. This platform also allows our researchers managed access to their data using a suite of analytical tools, including Microsoft Access, R, Python, and ArcGIS. Our database platform is powerful enough to manage the largest of datasets efficiently, yet flexible enough to allow for customized data deliverables to meet the needs of every client.

So back to the query…

PostgreSQL LIMIT and OFFSET

LIMIT and OFFSET are 2 useful SQL clauses for controlling the output of a select query.

LIMIT is used to reduce the number of rows returned in a select query. OFFSET specifies the number of rows to skip before applying LIMIT.

Here are a few quick examples:

LIMIT works to reduce the number of row returned. Take this series of numbers 1–5:

SELECT * FROM generate_series(1,5) AS x
ORDER BY x

x
1
2
3
3
5

If you just wanted to show the first 3 you could specify a LIMIT:

SELECT * FROM generate_series(1,5) AS x
ORDER BY x
LIMIT 3

x
1
2
3

Or if you wanted to skip the first row and limit the results to 3 rows you could use both LIMIT and OFFSET

SELECT * FROM generate_series(1,5) AS x
ORDER BY x
LIMIT 3 OFFSET 1

x
2
3
4

While these are simple examples, when working with large tables (hundreds of thousand to millions of rows) LIMIT and OFFSET can save you time by allowing you to specify the rows you want without returning the entire table.

Read more about LIMIT and OFFSET here.

ABR's triple bottom line: "Environmental stewardship, social responsibility, and economic viability"