Comparison and logic
SELECT * FROM students WHERE department = 'CSE';
SELECT * FROM students WHERE cgpa >= 3.50;
SELECT * FROM students WHERE age <> 20; -- or !=
SELECT * FROM students WHERE department = 'CSE' AND cgpa > 3.0;
SELECT * FROM students WHERE (department = 'CSE' OR department = 'EEE') AND cgpa > 3.0;
SELECT * FROM students WHERE NOT department = 'CSE';
AND binds tighter than OR, so parenthesise whenever you mix them.
Ranges and sets
SELECT * FROM students WHERE age BETWEEN 18 AND 22; -- inclusive
SELECT * FROM students WHERE department IN ('CSE','EEE');
SELECT * FROM students WHERE department NOT IN ('CSE');
SELECT * FROM students WHERE id = ANY(ARRAY[1,2,3]); -- array form
SELECT * FROM students WHERE admitted_on >= current_date - interval '30 days';
Pattern matching
| Operator | Meaning |
|---|---|
LIKE | Case-sensitive wildcard match |
ILIKE | Case-insensitive — a Postgres extension |
~ / ~* | Regex match / case-insensitive regex |
!~ / !~* | Negated regex |
SIMILAR TO | SQL-standard regex-ish syntax |
SELECT * FROM students WHERE name_en LIKE 'R%';
SELECT * FROM students WHERE name_en ILIKE '%uddin%';
SELECT * FROM students WHERE email ~ '^[a-z0-9._]+@example\.com$';
SELECT * FROM students WHERE roll_no LIKE 'CSE-1_1';
SELECT * FROM students WHERE code LIKE '50\%' ; -- backslash escapes the wildcard
For fast ILIKE '%term%' searching, install pg_trgm and add a GIN index:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_name_trgm ON students USING gin (name_en gin_trgm_ops);
NULL handling
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;
SELECT * FROM students WHERE email IS DISTINCT FROM 'x@y.com'; -- NULL-safe <>
SELECT coalesce(email, 'no email') FROM students;
SELECT nullif(cgpa, 0) FROM students;
= NULL is never true; IS DISTINCT FROM is the null-safe comparison.
Row and array comparisons
SELECT * FROM enrollments WHERE (student_id, course_id) = (1, 7);
SELECT * FROM enrollments WHERE (student_id, course_id) IN ((1,7),(2,9));
SELECT * FROM posts WHERE tags @> ARRAY['sql']; -- array contains
SELECT * FROM posts WHERE tags && ARRAY['sql','db']; -- arrays overlap
Keeping the WHERE clause index-friendly
-- slow: the function hides the column from the index
WHERE extract(year from admitted_on) = 2024
WHERE lower(email) = 'a@b.com'
-- fast
WHERE admitted_on >= '2024-01-01' AND admitted_on < '2025-01-01'
WHERE email = 'a@b.com' -- with a citext column or a functional index
When you genuinely need the expression, index the expression itself:
CREATE INDEX idx_lower_email ON students (lower(email));
Postgres supports expression indexes on any immutable expression, which is often a cleaner fix than rewriting every query.