Select and Filtering
Core SELECT shapes for reading rows. Results appear in the same fence: same-line -- comments when short, multiline -- blocks below the sample when not.
//
Search across all documentation pages
Core SELECT shapes for reading rows. Results appear in the same fence: same-line -- comments when short, multiline -- blocks below the sample when not.
Project columns or expressions; alias with AS.
SELECT id, upper(name) AS name_up
FROM users
WHERE id = 1;
-- name_up: ADA (example)Filter with AND/OR/NOT and comparisons.
SELECT *
FROM orders
WHERE status = 'open' AND total >= 100;
-- only open high-value ordersSort; control NULL placement.
SELECT name, score
FROM players
ORDER BY score DESC NULLS LAST
LIMIT 10;
-- highest scores first; nulls at endPage results; prefer keyset for large offsets.
SELECT id, title
FROM posts
ORDER BY id
LIMIT 20 OFFSET 40;
-- rows 41-60 in id orderKeep first row per expression (Postgres-specific).
SELECT DISTINCT ON (user_id) user_id, created_at
FROM events
ORDER BY user_id, created_at DESC;
-- latest event per userMembership and range filters.
SELECT id FROM products
WHERE category_id IN (1, 2, 3)
AND price BETWEEN 10 AND 50;
-- categories 1-3, mid pricePattern match; ILIKE is case-insensitive.
SELECT email FROM users
WHERE email ILIKE '%@example.com';
-- case-insensitive domain filterThree-valued logic - use IS NULL not = NULL.
SELECT id FROM users WHERE deleted_at IS NULL;
-- active rows onlyReplace NULL with a fallback.
SELECT coalesce(nickname, name, 'anon') AS label
FROM users;
-- first non-null labelConditional values in the select list.
SELECT id,
CASE WHEN total > 100 THEN 'high' ELSE 'low' END AS band
FROM orders;
-- band: high|lowPostgres has a real boolean type.
SELECT id FROM features WHERE enabled IS TRUE;
-- enabled = true also worksStandard SQL pagination synonym for LIMIT.
SELECT * FROM items
ORDER BY id
FETCH FIRST 10 ROWS ONLY;
-- 10 rowsShort names for readability.
SELECT u.id, u.email
FROM users AS u
WHERE u.active;
-- u is usersExistence check as a boolean result.
SELECT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = 42
) AS has_orders;
-- has_orders: t/fInline row sets without a table.
SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(id, label);
--
-- id | label
-- 1 | a
-- 2 | bStack versions: PostgreSQL 18.4 (stable 18, maintenance 17) · pgvector 0.8+
Reviewed by Chris St. John·Last updated Jul 19, 2026