PostgreSQL SELECT Statement Basics

Clause order

SELECT   [DISTINCT] columns
FROM     table
[JOIN    other ON condition]
[WHERE   row_filter]
[GROUP BY columns]
[HAVING  group_filter]
[ORDER BY columns]
[LIMIT n] [OFFSET m];

Logical execution order

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

This is why a SELECT alias cannot be used in WHERE but can be used in ORDER BY.

Basics

SELECT * FROM students;
SELECT name_en, cgpa FROM students;
SELECT DISTINCT department FROM students;

SELECT
  name_en AS student_name,
  cgpa * 25 AS percentage,
  department dept
FROM students s;

Identifiers are folded to lower case unless double-quoted. SELECT NAME_EN and SELECT name_en are the same column, but SELECT "Name_En" is a different one — which is why quoted mixed-case names are best avoided entirely.

String concatenation and expressions

SELECT name_en || ' (' || department || ')' AS label FROM students;
SELECT concat_ws(', ', name_en, email, department) FROM students;   -- skips NULLs
SELECT 5 + 3, now(), current_date, version();

|| returns NULL if any operand is NULL; concat() and concat_ws() do not.

DISTINCT ON

A Postgres extension that keeps the first row of each group — perfect for "latest per key" queries.

SELECT DISTINCT ON (department) department, name_en, cgpa
FROM students
ORDER BY department, cgpa DESC;

The leading ORDER BY columns must match the DISTINCT ON columns; whatever comes next decides which row wins.

LIMIT, OFFSET and FETCH

SELECT * FROM students ORDER BY id LIMIT 10;
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 20;
SELECT * FROM students ORDER BY id OFFSET 20 FETCH FIRST 10 ROWS ONLY;   -- SQL standard

Generating rows

SELECT generate_series(1, 10) AS n;
SELECT generate_series('2026-01-01'::date, '2026-01-31', '1 day') AS d;
SELECT * FROM (VALUES (1,'a'), (2,'b')) AS t(id, label);

generate_series is the standard way to build gap-free date ranges for reports.

Good habits

Select only the columns you need; SELECT * breaks when someone reorders columns and prevents index-only scans.
Use \timing in psql while developing to feel how expensive a query is.
Prefer text comparisons with the correct collation over lower() on both sides, which prevents index use.

SELECT Basics