How to Use the SELECT Statement in PostgreSQL

How to Use the SELECT Statement in PostgreSQL

The SELECT statement returns a custom result set from tables, views, CTEs, or functions. Mastering it lets you filter, sort, join, group, and paginate data efficiently.

What is the basic SELECT syntax in PostgreSQL?

PostgreSQL evaluates SELECT in the order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET. Knowing this helps you place clauses correctly and avoid logic errors.

SELECT [DISTINCT | DISTINCT ON (col)] column_list
FROM source
[WHERE condition]
[GROUP BY grouping_cols]
[HAVING group_condition]
[ORDER BY sort_cols [ASC|DESC] [NULLS FIRST|LAST]]
[LIMIT n] [OFFSET m]
[FOR {UPDATE | SHARE} [OF table_list]];

How do I filter rows with WHERE?

WHERE eliminates rows before grouping and projection. Combine comparison, logical, pattern-matching, and range operators for precise filters.

Use comparison operators

Example: WHERE amount > 100 AND status = 'paid' returns only paid invoices above $100.

Use pattern matching

WHERE email ILIKE '%@example.com' performs a case-insensitive search for company emails.

How do I sort results with ORDER BY?

ORDER BY applies after SELECT. Sort by column index, name, alias, or expression: ORDER BY total DESC, created_at ASC. Use NULLS FIRST|LAST to control null placement.

How do I limit rows with LIMIT/OFFSET?

LIMIT returns the first n rows; OFFSET skips m. Combine both for paging: LIMIT 20 OFFSET 40 fetches the third 20-row page.

How do I aggregate data with GROUP BY?

GROUP BY collapses rows sharing the same key so aggregate functions can run. Every non-aggregated column in SELECT must appear in GROUP BY: SELECT country, COUNT(*) FROM users GROUP BY country.

How do I join tables in a SELECT query?

Use INNER, LEFT, RIGHT, or FULL joins to combine rows. Always qualify columns to avoid ambiguity: SELECT o.id, u.name FROM orders o JOIN users u ON u.id = o.user_id.

What are best practices for performant SELECT queries?

Index columns used in WHERE, JOIN, and ORDER BY. Retrieve only needed columns. Avoid SELECT *. Filter early with WHERE. Prefer LIMIT when exploring. Use EXPLAIN to inspect plans.

Frequently asked questions

How do I select the first N rows?

Add LIMIT N: SELECT * FROM logs ORDER BY ts DESC LIMIT 10;

How do I remove duplicates?

Use SELECT DISTINCT col_list FROM table; or group rows with MIN/MAX if you need deterministic picks.

Can I select into a new table?

Yes. CREATE TABLE new_tbl AS SELECT ...; copies the result set’s structure and data.