If CREATE TABLE and INSERT are how data gets into PostgreSQL, SELECT is how it comes back out — and it’s easily the command you’ll type more than any other while working with a database. This guide covers the full range of what SELECT can do, from the most basic queries to joins, aggregations, sorting, and some of PostgreSQL’s more powerful querying features.
Basic SELECT Syntax
SELECT column1, column2 FROM table_name;
The simplest possible query, selecting every column from every row:
SELECT * FROM customers;
The * wildcard is convenient for quick exploration, but in real application code, it’s better to name the columns you actually need:
SELECT id, name, email FROM customers;
This is more efficient (especially on wide tables), and it protects your code from breaking if someone adds a new column to the table later.
Filtering Rows with WHERE
SELECT * FROM customers
WHERE country = 'Pakistan';
WHERE supports the full range of comparison operators: =, != (or <>), <, >, <=, >=, along with logical operators AND, OR, and NOT.
SELECT * FROM orders
WHERE status = 'pending' AND total_amount > 100;
Sorting Results with ORDER BY
SELECT * FROM customers
ORDER BY name ASC;
Use DESC for descending order:
SELECT * FROM orders
ORDER BY created_at DESC;
You can sort by multiple columns, with each one resolving ties in the previous:
SELECT * FROM orders
ORDER BY status ASC, created_at DESC;
Limiting Results with LIMIT and OFFSET
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10;
This returns just the 10 most recent orders. OFFSET skips a number of rows before returning results, useful for pagination:
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;
This returns rows 21 through 30 — the third “page” of results if each page shows 10 rows.
Selecting Distinct Values
SELECT DISTINCT country FROM customers;
This returns each unique country value exactly once, regardless of how many customers share it. DISTINCT ON goes further, letting you get the first row per group based on a specified ordering:
SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, created_at DESC;
This returns each customer’s most recent order — a genuinely useful PostgreSQL-specific feature not all databases support.
Aggregate Functions
PostgreSQL supports the standard SQL aggregate functions for summarizing data:
SELECT COUNT(*) FROM customers;
SELECT SUM(total_amount) FROM orders;
SELECT AVG(total_amount) FROM orders;
SELECT MIN(created_at), MAX(created_at) FROM orders;
Grouping with GROUP BY
Aggregates become much more useful when combined with GROUP BY, which lets you compute a summary per group rather than across the whole table:
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;
This returns one row per customer, showing how many orders they’ve placed and their total spend.
Filtering Groups with HAVING
WHERE filters individual rows before grouping; HAVING filters groups after aggregation:
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 500;
This only returns customers whose total spend exceeds $500 — a condition that only makes sense to apply after the aggregation has already happened.
Joining Tables
Most real queries need data from more than one table. PostgreSQL supports the standard join types.
INNER JOIN
Returns only rows that have a match in both tables:
SELECT orders.id, customers.name, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
LEFT JOIN
Returns all rows from the left table, with matching data from the right table where it exists, and NULL where it doesn’t:
SELECT customers.name, orders.id AS order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;
This is useful for finding customers with no orders at all — those rows will show NULL for order_id:
SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.id IS NULL;
RIGHT JOIN and FULL JOIN
RIGHT JOIN is the mirror image of LEFT JOIN, and FULL JOIN returns all rows from both tables, matched where possible and filled with NULL where not:
SELECT *
FROM customers
FULL JOIN orders ON customers.id = orders.customer_id;
Joining More Than Two Tables
SELECT orders.id, customers.name, order_items.product_id, order_items.quantity
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN order_items ON order_items.order_id = orders.id;
Joins chain naturally — each additional JOIN clause adds another table into the mix.
Subqueries
A query can be nested inside another, either in the WHERE clause, the FROM clause, or the column list.
Subquery in WHERE
SELECT * FROM customers
WHERE id IN (
SELECT customer_id FROM orders WHERE total_amount > 1000
);
Subquery in FROM (Derived Table)
SELECT category, avg_price
FROM (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS category_averages
WHERE avg_price > 50;
Correlated Subquery
A subquery that references a column from the outer query, re-evaluated for each row:
SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total_amount > 500
);
This returns customers who have at least one order over $500.
Common Table Expressions (CTEs)
WITH clauses let you define named, reusable subqueries that make complex queries much more readable:
WITH high_value_orders AS (
SELECT customer_id, SUM(total_amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
)
SELECT customers.name, high_value_orders.total
FROM customers
JOIN high_value_orders ON customers.id = high_value_orders.customer_id
ORDER BY high_value_orders.total DESC;
CTEs can also be recursive, which is useful for hierarchical data like organizational charts or category trees:
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
Window Functions
Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row the way GROUP BY does.
SELECT
customer_id,
total_amount,
SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS order_rank
FROM orders;
This shows each individual order alongside that customer’s running total and a rank for how recent each order is — data that would be lost with a straightforward GROUP BY.
Other common window functions include RANK(), DENSE_RANK(), LAG(), and LEAD(), useful for comparing a row to the ones before or after it.
Filtering with LIKE and ILIKE
For pattern matching on text:
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
LIKE is case-sensitive; ILIKE is PostgreSQL’s case-insensitive equivalent:
SELECT * FROM customers WHERE name ILIKE 'sarah%';
% matches any sequence of characters, and _ matches exactly one character.
Working with Dates
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
Or using date functions:
SELECT * FROM orders
WHERE DATE_TRUNC('month', created_at) = '2026-01-01';
Casting Types with ::
PostgreSQL’s shorthand cast operator is genuinely convenient:
SELECT '42'::integer;
SELECT created_at::date FROM orders;
Practical Examples
Top 5 customers by total spend
SELECT customers.name, SUM(orders.total_amount) AS total_spent
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name
ORDER BY total_spent DESC
LIMIT 5;
Orders placed in the last 7 days, with customer info
SELECT orders.id, customers.name, orders.total_amount, orders.created_at
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.created_at >= NOW() - INTERVAL '7 days'
ORDER BY orders.created_at DESC;
Products that have never been ordered
SELECT products.name
FROM products
LEFT JOIN order_items ON products.id = order_items.product_id
WHERE order_items.id IS NULL;
Common Use Cases
Application data retrieval. Fetching a user’s profile, a product listing, an order history — the backbone of nearly every application feature.
Reporting and analytics. Aggregating sales by month, finding top customers, calculating growth metrics — all built on GROUP BY, aggregates, and often window functions.
Data validation and auditing. Finding orphaned records, checking for duplicates, verifying data integrity through targeted SELECT queries before running fixes.
Troubleshooting Common Errors
ERROR: column "x" does not exist. Typo, or referencing a column that belongs to a different table without proper qualification in a multi-table query. Use table.column syntax to disambiguate.
ERROR: column must appear in the GROUP BY clause or be used in an aggregate function. Every selected column that isn’t wrapped in an aggregate function must appear in GROUP BY. This is one of the most common early mistakes with grouped queries.
Unexpected NULL results from a LEFT JOIN. This is often correct behavior, not a bug — it means no matching row existed in the joined table. Confirm this is actually the case you’re trying to detect.
Slow queries on large tables. Use EXPLAIN ANALYZE in front of your query to see the execution plan and spot missing indexes or inefficient join strategies.
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
Best Practices
- Select only the columns you need rather than always using
SELECT *, especially in application code and on wide tables. - Index columns frequently used in
WHERE,JOIN, andORDER BYclauses to keep queries fast as tables grow. - Use CTEs to break complex queries into readable, named steps rather than deeply nested subqueries.
- Reach for window functions instead of self-joins or multiple queries when you need row-level detail alongside aggregate context.
- Always test filtering conditions on a
SELECTbefore reusing them in a destructiveUPDATEorDELETE. - Use
EXPLAIN ANALYZEwhenever a query feels slower than expected, rather than guessing at the cause.
Aliasing Tables and Columns
As queries grow more complex, aliases make them dramatically more readable:
SELECT c.name AS customer_name, o.total_amount AS order_total
FROM customers AS c
JOIN orders AS o ON c.id = o.customer_id;
The AS keyword is technically optional in most cases (customers c works the same as customers AS c), but including it tends to make queries easier to scan for other readers.
Set Operations: UNION, INTERSECT, EXCEPT
PostgreSQL supports combining the results of multiple SELECT statements:
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;
UNION combines results and removes duplicates by default; UNION ALL keeps duplicates and is faster since it skips the deduplication step:
SELECT email FROM customers
UNION ALL
SELECT email FROM newsletter_subscribers;
INTERSECT returns only rows present in both result sets:
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;
EXCEPT returns rows from the first query that don’t appear in the second:
SELECT email FROM customers
EXCEPT
SELECT email FROM newsletter_subscribers;
This last one is genuinely useful for questions like “which customers are not currently subscribed to the newsletter.”
Conditional Logic with CASE in SELECT
CASE expressions work in the column list too, not just in UPDATE statements, letting you compute derived values directly in a query:
SELECT
name,
total_amount,
CASE
WHEN total_amount > 500 THEN 'high value'
WHEN total_amount > 100 THEN 'medium value'
ELSE 'low value'
END AS order_tier
FROM orders;
Full-Text Search
For searching within larger blocks of text, PostgreSQL’s LIKE/ILIKE isn’t the most efficient or capable tool — full-text search is built specifically for this:
SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'postgresql & database');
This searches for articles containing both “postgresql” and “database” (in any form — full-text search handles stemming automatically, so “databases” would also match). For frequent full-text searches, adding a dedicated index dramatically improves performance:
CREATE INDEX idx_articles_content_fts ON articles USING GIN (to_tsvector('english', content));
Querying JSON and JSONB Data
Beyond simple filtering (covered in the WHERE clause guide), SELECT can extract and reshape JSON data directly:
SELECT
id,
payload->>'event_type' AS event_type,
payload->'metadata'->>'source' AS source
FROM events;
-> returns a JSON value (still JSON type), while ->> returns the value cast to text — the distinction matters when chaining further JSON operations versus when you need a plain string for comparison or display.
Handling Time Zones in Queries
When working with TIMESTAMPTZ columns, it’s often useful to convert to a specific time zone for display or filtering:
SELECT created_at AT TIME ZONE 'Asia/Karachi' AS local_time
FROM orders;
This converts the stored UTC timestamp to the specified time zone for the query’s output, without changing how the underlying data is stored.
Using EXPLAIN to Understand Query Performance
Before optimizing a slow query, it helps to see exactly what PostgreSQL is doing to execute it:
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;
This shows the planned execution strategy without actually running the query. Adding ANALYZE actually executes the query and reports real timing alongside the plan:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
Look for Seq Scan (a full table scan, often a red flag on large tables if it’s unexpected) versus Index Scan (using an index efficiently), and pay attention to the estimated versus actual row counts — a large mismatch often points to outdated table statistics, fixable with ANALYZE table_name.
Practical Examples (Continued)
Monthly revenue trend using a window function
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total_amount) AS monthly_revenue,
SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS running_total
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
Customers who appear in both a marketing list and a purchase list
SELECT email FROM marketing_subscribers
INTERSECT
SELECT email FROM customers WHERE total_orders > 0;
Full-text search across article titles and content
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'postgresql');
Frequently Asked Questions
What’s the difference between WHERE and HAVING? WHERE filters individual rows before any grouping happens; HAVING filters groups after aggregation. You can’t use aggregate functions like SUM() or COUNT() in a WHERE clause directly — that’s exactly what HAVING is for.
Is SELECT * bad practice? It’s fine for quick, exploratory queries in psql, but in application code and production queries, naming specific columns is generally better — it’s more efficient (especially with wide tables or unnecessary large columns like TEXT blobs), and it protects your code from silently breaking or behaving unexpectedly if the table’s structure changes later.
When should I use a CTE instead of a subquery? CTEs are generally preferred when a subquery is reused more than once in a larger query, or when breaking a complex query into named, readable steps improves clarity. For a single simple filter, an inline subquery is often just as clear and sometimes marginally more efficient, though modern PostgreSQL versions (12+) can inline non-recursive CTEs during optimization in many cases, narrowing the historical performance gap.
Why is my JOIN returning more rows than I expected? This usually means the join condition is matching multiple rows on one side to a single row on the other (a one-to-many relationship), which duplicates the “one” side’s data across each matched row. Double-check whether you actually need to aggregate afterward, or whether your join condition is more permissive than intended.
Wrapping Up
SELECT is deceptively deep — the basic form takes minutes to learn, but joins, subqueries, CTEs, window functions, and full-text search unlock genuinely powerful ways to shape and analyze your data directly in the database, rather than pulling everything into application code first. Getting comfortable with these tools pays off constantly, whether you’re building application features or digging into a one-off analytics question.
