SELECT, Filtering, and Sorting

The SELECT statement is the foundation of data retrieval in PostgreSQL. Beyond simple column selection, you can compute expressions, call functions, use CASE expressions for conditional logic, and apply aliases with AS. The WHERE clause filters rows using comparison operators, logical operators (AND, OR, NOT), pattern matching (LIKE, ILIKE for case-insensitive, SIMILAR TO for SQL patterns, and ~ for POSIX regex), and range checks (BETWEEN). The ORDER BY clause sorts results by one or more columns, with NULLS FIRST or NULLS LAST to control where null values appear. DISTINCT eliminates duplicate rows, and DISTINCT ON (PostgreSQL extension) keeps only the first row per distinct value of specified columns.

Example

-- Basic SELECT with expressions and aliases
SELECT
  id,
  name,
  price,
  price * 1.08  AS price_with_tax,
  UPPER(name)   AS name_upper,
  CASE
    WHEN price < 50  THEN 'budget'
    WHEN price < 200 THEN 'mid-range'
    ELSE 'premium'
  END AS price_tier,
  created_at::DATE AS created_date
FROM products
WHERE is_active = TRUE
  AND price BETWEEN 10 AND 500
  AND name ILIKE '%keyboard%'     -- case-insensitive LIKE
  AND category_id IN (1, 2, 3)
ORDER BY price_tier ASC, price DESC NULLS LAST
LIMIT 25 OFFSET 0;

-- DISTINCT ON — one row per category, the cheapest product
SELECT DISTINCT ON (category_id)
  category_id, name, price
FROM products
ORDER BY category_id, price ASC;

-- POSIX regex — names starting with A or B
SELECT name FROM users WHERE name ~ '^[AB]';

-- NULL checks
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned