PostgreSQL SELECT

by

In this article, I will guide you through the full spectrum of the PostgreSQL SELECT statement—from core syntax and logical execution order to advanced pattern matching, relational joins, Common Table Expressions (CTEs), window functions, and query optimization strategies.

PostgreSQL SELECT

The Anatomy of a PostgreSQL SELECT Statement

The SELECT statement retrieves rows from one or more tables, views, or materialized tables in a PostgreSQL database.

The complete structural syntax of a complex PostgreSQL SELECT query encompasses several declarative clauses:

SQL

SELECT [ DISTINCT [ ON ( distinct_expressions ) ] ]
    select_list
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ];

Logical Query Processing Order

To write efficient SQL, you must understand that PostgreSQL does not execute the clauses of a SELECT statement in the order they are written (lexical order). Instead, the query planner executes them in a strict logical processing order.

Why Logical Order Matters

Because the WHERE clause is evaluated before the SELECT clause, you cannot reference a column alias defined in the SELECT list inside your WHERE filter:

SQL

-- INVALID: Throws "column 'annual_compensation' does not exist"
SELECT 
    employee_id, 
    base_salary + COALESCE(annual_bonus, 0) AS annual_compensation
FROM hr.employees
WHERE annual_compensation > 100000;

-- VALID: Evaluate using the raw expression
SELECT 
    employee_id, 
    base_salary + COALESCE(annual_bonus, 0) AS annual_compensation
FROM hr.employees
WHERE (base_salary + COALESCE(annual_bonus, 0)) > 100000;

Basic Projections, Expressions, and Column Aliasing

The simplest query retrieves literal values, system metadata, or calculated expressions directly without accessing a physical table:

SQL

-- Selecting scalar values, mathematical expressions, and system functions
SELECT 
    CURRENT_TIMESTAMP AS execution_time,
    VERSION() AS postgresql_version,
    (1500.00 * 1.0825)::NUMERIC(10,2) AS total_with_tax;

1. Selecting Specific Columns vs. SELECT *

In production applications, avoid using SELECT *. Explicitly naming required columns reduces network serialization overhead, optimizes memory buffer usage, and allows the PostgreSQL query planner to execute high-speed Index-Only Scans.

SQL

-- RECOMMENDED: Explicit column projection
SELECT 
    account_id, 
    customer_id, 
    account_tier, 
    current_balance
FROM finance.accounts;

2. String Concatenation and Column Aliasing

PostgreSQL supports standard SQL concatenation (||) alongside built-in formatting functions like CONCAT() and CONCAT_WS():

SQL

SELECT 
    first_name || ' ' || last_name AS full_name_standard,
    CONCAT_WS(', ', last_name, first_name, state_code) AS formatted_directory_entry
FROM corporate.staff_members;

Filtering Rows with the WHERE Clause

The WHERE clause filters individual records before any grouping or projection takes place.

PostgreSQL Comparison and Logical Operators

Operator / KeywordDescriptionExample Syntax
=, <>, !=Equality and InequalityWHERE account_status = 'Active'
<, <=, >, >=Relational magnitude comparisonWHERE order_total >= 500.00
BETWEEN ... ANDInclusive boundary range filterWHERE created_at BETWEEN '2026-01-01' AND '2026-06-30'
IN (...)Value matching within an explicit setWHERE state_code IN ('CA', 'NY', 'TX', 'WA')
IS NULL / IS NOT NULLEvaluating three-valued nullabilityWHERE termination_date IS NULL
IS DISTINCT FROMNULL-safe inequality comparisonWHERE new_tier IS DISTINCT FROM old_tier

PostgreSQL-Specific Pattern Matching

In addition to standard ANSI SQL LIKE, PostgreSQL provides powerful case-insensitive matching and POSIX regular expressions:

SQL

-- Case-Insensitive Pattern Matching (ILIKE)
SELECT account_id, company_name 
FROM sales.corporate_clients
WHERE company_name ILIKE 'apex%'; -- Matches 'Apex', 'APEX', 'apex solutions'

-- POSIX Regular Expression Matching (~ and ~*)
SELECT user_id, email_address
FROM security.user_credentials
WHERE email_address ~* '^[a-z0-9._%+-]+@enterprise\.(com|org)$';

Sorting and Pagination: ORDER BY, LIMIT, and OFFSET

1. Advanced Sorting with NULL Positioning

PostgreSQL allows you to specify whether NULL values appear at the beginning or end of your sorted result set using NULLS FIRST or NULLS LAST:

SQL

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    commission_rate
FROM sales.representatives
ORDER BY 
    commission_rate DESC NULLS LAST, 
    last_name ASC;

2. Result Set Pagination

To paginate through large datasets, you can combine LIMIT (the page size) with OFFSET (the starting row index):

SQL

-- Retrieve Page 3 (Rows 21 to 30)
SELECT 
    order_id, 
    customer_id, 
    order_total, 
    order_date
FROM sales.orders
ORDER BY order_date DESC, order_id DESC
LIMIT 10 OFFSET 20;

Performance Warning on Large Offsets: OFFSET 1000000 forces PostgreSQL to scan and discard 1,000,000 physical rows before returning the requested records. For high-scale pagination, use Keyset Pagination (Seek Method):

SQL

-- High-Speed Keyset Pagination
SELECT order_id, customer_id, order_total, order_date
FROM sales.orders
WHERE (order_date, order_id) < ('2026-08-15 10:30:00', 849201)
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

Eliminating Duplicates: DISTINCT and PostgreSQL DISTINCT ON

Standard DISTINCT removes exact duplicate rows across all projected columns. However, PostgreSQL offers a specialized, highly efficient construct: DISTINCT ON.

The Power of DISTINCT ON

Suppose you want to retrieve each customer’s single most recent order. In other database engines, this requires complex window functions or subqueries. In PostgreSQL, it takes a single clean statement:

SQL

SELECT DISTINCT ON (customer_id)
    customer_id,
    order_id,
    order_date,
    order_total
FROM sales.orders
ORDER BY customer_id, order_date DESC;
  • The expression inside DISTINCT ON (customer_id) tells PostgreSQL where to look for uniqueness.
  • The leftmost expression in ORDER BY must match the DISTINCT ON expression (customer_id).
  • The subsequent sorting columns (order_date DESC) determine which row within that unique group is preserved.

Data Aggregation: GROUP BY, HAVING, and Aggregate Filters

The GROUP BY clause condenses rows that share identical values across specified columns into summarized summary rows.

SQL

SELECT 
    department_id,
    COUNT(employee_id) AS total_headcount,
    ROUND(AVG(base_salary), 2) AS average_salary,
    MIN(base_salary) AS minimum_salary,
    MAX(base_salary) AS maximum_salary
FROM hr.employees
GROUP BY department_id
HAVING COUNT(employee_id) >= 5
ORDER BY average_salary DESC;

PostgreSQL Aggregate FILTER Clause

PostgreSQL supports the standard SQL FILTER (WHERE ...) clause on aggregate functions. This allows you to perform selective conditional aggregations without writing verbose CASE WHEN statements:

SQL

SELECT 
    department_id,
    COUNT(*) AS total_employees,
    COUNT(*) FILTER (WHERE employment_status = 'Active') AS active_employees,
    COUNT(*) FILTER (WHERE employment_status = 'OnLeave') AS on_leave_employees,
    SUM(base_salary) FILTER (WHERE employment_status = 'Active') AS active_payroll_sum
FROM hr.employees
GROUP BY department_id;

Combining Datasets with Relational JOINs

Relational querying relies on joining independent tables via shared keys.

                    POSTGRESQL RELATIONAL JOIN TYPES
                    
INNER JOIN:  Returns rows when keys match in BOTH tables.
LEFT JOIN:   Returns ALL rows from Left table, plus matched Right rows (or NULL).
RIGHT JOIN:  Returns ALL rows from Right table, plus matched Left rows (or NULL).
FULL JOIN:   Returns ALL rows from BOTH tables, filling mismatches with NULLs.
CROSS JOIN:  Cartesian product (Every row in Table A paired with Table B).

Practical Enterprise Multi-Table JOIN Example

SQL

SELECT 
    ord.order_id,
    ord.order_date,
    cust.company_name,
    cust.state_code,
    emp.first_name || ' ' || emp.last_name AS account_manager,
    SUM(item.quantity * item.unit_price) AS calculated_order_total
FROM sales.orders AS ord
INNER JOIN sales.customers AS cust 
    ON ord.customer_id = cust.customer_id
LEFT JOIN hr.employees AS emp 
    ON cust.account_manager_id = emp.employee_id
INNER JOIN sales.order_line_items AS item 
    ON ord.order_id = item.order_id
WHERE ord.order_status = 'Completed'
  AND ord.order_date >= '2026-01-01'
GROUP BY 
    ord.order_id, 
    ord.order_date, 
    cust.company_name, 
    cust.state_code, 
    emp.first_name, 
    emp.last_name
ORDER BY calculated_order_total DESC;

Advanced Retrieval: CTEs and Window Functions

When building complex reports, Common Table Expressions (CTEs) and Window Functions allow you to write modular, performant SQL.

1. Common Table Expressions (The WITH Clause)

A Common Table Expression defines a temporary, named result set that exists only during the execution scope of a single query.

SQL

WITH RegionalSalesSummary AS (
    SELECT 
        cust.state_code,
        COUNT(ord.order_id) AS total_orders,
        SUM(ord.order_total) AS gross_revenue
    FROM sales.orders AS ord
    JOIN sales.customers AS cust ON ord.customer_id = cust.customer_id
    WHERE ord.order_date >= '2026-01-01'
    GROUP BY cust.state_code
),
BenchmarkMetrics AS (
    SELECT AVG(gross_revenue) AS national_average_revenue
    FROM RegionalSalesSummary
)
SELECT 
    r.state_code,
    r.total_orders,
    r.gross_revenue,
    ROUND(r.gross_revenue - b.national_average_revenue, 2) AS variance_from_average
FROM RegionalSalesSummary AS r
CROSS JOIN BenchmarkMetrics AS b
ORDER BY r.gross_revenue DESC;

2. Analytical Window Functions

Window functions perform calculations across a set of table rows that are related to the current row, without collapsing the individual rows like GROUP BY does.

SQL

SELECT 
    employee_id,
    department_id,
    last_name,
    base_salary,
    -- Calculate average salary within the employee's specific department
    AVG(base_salary) OVER(PARTITION BY department_id) AS department_average,
    -- Rank employees by salary within their department
    DENSE_RANK() OVER(
        PARTITION BY department_id 
        ORDER BY base_salary DESC
    ) AS salary_rank_in_dept,
    -- Compute running total of payroll across the company
    SUM(base_salary) OVER(
        ORDER BY hire_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_company_payroll
FROM hr.employees;

Performance Tuning: Indexing and EXPLAIN ANALYZE

Even a well-written SELECT statement can cause bottlenecks if the database engine is forced to scan entire tables from disk.

Understanding SARGability (Search Argument Able)

To leverage B-Tree indexes, your WHERE clauses must be SARGable. Wrapping indexed columns inside functions prevents index lookups:

SQL

-- ANTI-PATTERN: Non-SARGable (Forces Sequential Scan on indexed column)
SELECT account_id FROM finance.accounts 
WHERE EXTRACT(YEAR FROM created_at) = 2026;

-- OPTIMAL: SARGable Range Filter (Leverages Index Seek)
SELECT account_id FROM finance.accounts 
WHERE created_at >= '2026-01-01 00:00:00' 
  AND created_at < '2027-01-01 00:00:00';

Frequently Asked Questions (FAQs)

What is the difference between WHERE and HAVING in PostgreSQL?

WHERE filters individual rows before any grouping or aggregation takes place. HAVING filters aggregated groups after the GROUP BY clause has evaluated summary metrics.

Why is SELECT COUNT(*) slow on large PostgreSQL tables?

Because of PostgreSQL’s Multi-Version Concurrency Control (MVCC) architecture, different concurrent transactions may see different active versions of rows. To ensure complete snapshot isolation, PostgreSQL must scan the table (or visibility map) to confirm which rows are visible to the current transaction.

How does UNION differ from UNION ALL?

UNION combines the results of two queries and performs an explicit sorting and deduplication step to remove identical rows. UNION ALL concatenates the two datasets directly without removing duplicates, making it substantially faster and less memory-intensive.

Can I write to a table using a SELECT statement?

Yes. You can create a new persistent table populated with query results using CREATE TABLE ... AS SELECT (CTAS) or insert rows into an existing table using INSERT INTO ... SELECT.

Summary and Key Takeaways

The PostgreSQL SELECT statement provides a comprehensive, declarative toolkit for extracting and analyzing data across relational architectures:

  • Respect Logical Processing Order: Remember that tables are assembled in FROM, filtered in WHERE, grouped in GROUP BY, projected in SELECT, and ordered in ORDER BY.
  • Use Explicit Projections: Eliminate SELECT * in production to minimize memory pressure and enable high-speed index-only scans.
  • Leverage PostgreSQL-Specific Capabilities: Take advantage of DISTINCT ON for deduplication and FILTER (WHERE ...) for conditional aggregation.
  • Write SARGable Queries: Keep indexed columns free of function wrappers inside WHERE and JOIN conditions to ensure the query planner uses optimal index scans.
  • Inspect Execution Plans: Use EXPLAIN (ANALYZE, BUFFERS) to verify query performance and optimize execution paths on production workloads.

You may also like the following articles:

Top 200 SQL Server Interview Questions and Answers

Free PDF On Top 200 SQL Server Interview Questions And Answers

Download A 40 pages PDF And Learn Now.