Chapter 17: PostgreSQL Operators
PostgreSQL: Operators ☕🔧
When people say “PostgreSQL operators”, they usually mean all the special symbols and short keywords that you put between values to compare them, combine them, do math with them, check membership, work with JSON, do pattern matching, do bitwise operations, etc.
PostgreSQL has way more operators than most other databases — and many of them are very expressive and unique.
1. Quick classification – the families of operators you’ll meet every day
| Category | Most important operators | Typical feeling / use case | PostgreSQL strength level |
|---|---|---|---|
| Arithmetic | + – * / % ^ |/ @ | Money calculations, age computation | ★★★☆☆ |
| Comparison | = <> != < > <= >= IS [NOT] NULL | Filtering rows | ★★★★★ |
| Logical | AND OR NOT | Combining conditions | ★★★★☆ |
| String / Pattern matching | | LIKE ILIKE ~ ~* SIMILAR TO | Search, validation | ★★★★★ |
| JSON/JSONB | -> ->> #> #>> @> <@ ? ?& ?| @? @@ | Modern app data, API responses | ★★★★★★ (best in class) |
| Array | && <@ @> = && | & | Tags, permissions, multivalue columns | ★★★★★ |
| Fuzzy / Similarity | % <-> <@> <% (with pg_trgm extension) | Search-as-you-type, typo-tolerant search | ★★★★☆ |
| Bitwise | & | # ~ << >> | Flags, permissions, low-level bit manipulation | ★★★☆☆ |
| Geometric / Geometric ops | && <-> @> <@ << >> &< &> | GIS, bounding boxes (with PostGIS even more) | ★★★★☆ |
| Text search | @@ @@@ <-> (with tsvector / tsquery) | Full-text search | ★★★★★ |
| Containment / Overlap | @> <@ && (used in many types: jsonb, array, range, gist indexes…) | “contains”, “overlaps” checks | ★★★★★ |
2. Most frequently used operators – with real examples (our bookstore_demo style)
Let’s assume we have the books and customers tables from our previous demo database.
Arithmetic & Math operators
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Price with 18% GST SELECT title, price, price * 1.18 AS price_with_gst FROM books; -- Integer division & remainder SELECT stock_quantity / 10 AS full_boxes, stock_quantity % 10 AS loose_books FROM books; -- Square root (useful in statistics sometimes) SELECT title, |/ stock_quantity AS approx_sqrt_stock FROM books; |
Comparison & IS / IS NOT
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT title, price FROM books WHERE price BETWEEN 200 AND 400 -- inclusive AND publication_year >= 2000 AND stock_quantity IS NOT NULL AND stock_quantity > 0; |
Logical operators (AND / OR / NOT)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT title, price, stock_quantity FROM books WHERE (price < 300 OR publication_year > 2015) AND NOT (stock_quantity = 0) AND is_available = TRUE; |
String concatenation & LIKE / ILIKE
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Concatenation (|| is standard, + does NOT work in PostgreSQL) SELECT first_name || ' ' || last_name || ' <' || email || '>' AS contact_info FROM customers; -- Case-insensitive search (ILIKE = very common in India) SELECT title FROM books WHERE title ILIKE '%harry potter%' OR title ILIKE '%small things%'; |
Regular expressions (~ ~* !~ !~*)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Find titles that start with "The " and have space after SELECT title FROM books WHERE title ~ '^The\s'; -- ^ = start, \s = whitespace -- Case-insensitive regex SELECT email FROM customers WHERE email ~* 'gmail\.com$'; -- ends with gmail.com |
JSONB operators – PostgreSQL’s superpower in 2026
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Assume we add a details JSONB column to books later SELECT title, details->>'publisher' AS publisher FROM books WHERE details @> '{"publisher": "Penguin"}'; -- contains this key-value -- Get value as text SELECT title, details->>'language' AS lang FROM books; -- Check if key exists SELECT title FROM books WHERE details ? 'awards'; -- has "awards" key -- Check multiple keys exist SELECT title FROM books WHERE details ?& ARRAY['isbn13', 'edition']; |
Array operators (very useful for tags, roles, etc.)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Assume we add column tags TEXT[] to books SELECT title FROM books WHERE tags && ARRAY['fiction', 'indian']; -- overlaps / shares at least one SELECT title FROM books WHERE tags @> ARRAY['mythology']; -- contains all these tags SELECT title FROM books WHERE 'classics' = ANY(tags); -- contains this single value |
Containment & Overlap (works on jsonb, arrays, ranges, geometry…)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
-- JSONB containment WHERE details @> '{"category": "fantasy", "age_group": "teen"}'::jsonb -- Range containment (if we had price_range column) WHERE price_range @> 350::numeric -- 350 is inside the range |
Full-text search operators (basic example)
SQL
|
0 1 2 3 4 5 6 7 8 |
SELECT title FROM books WHERE to_tsvector('english', title || ' ' || description) @@ to_tsquery('english', 'potter & stone'); |
3. Quick reference table – top 30 operators beginners should know by heart
| Operator | Meaning | Example | Returns |
|---|---|---|---|
| = | Equals | price = 399 | boolean |
| <> / != | Not equals | status <> ‘cancelled’ | boolean |
| LIKE / ILIKE | Pattern match (% _) | title ILIKE ‘%harry%’ | boolean |
| ~ / ~* | Regex match / case-insensitive | email ~* ‘gmail.com$’ | boolean |
| && | Array overlap / jsonb key overlap | tags && ARRAY[‘sci-fi’] | boolean |
| @> | Contains | details @> ‘{“in_stock”: true}’ | boolean |
| <@ | Is contained by | ARRAY[‘a’,’b’] <@ tags | boolean |
| -> | Get JSON object field as jsonb | data -> ‘address’ | jsonb |
| ->> | Get JSON object field as text | data ->> ‘city’ | text |
| || | String / array concatenation | name || ‘ ‘ || surname | text / array |
| + – * / | Arithmetic | price * 1.18 | numeric |
| BETWEEN | Inclusive range | year BETWEEN 2000 AND 2010 | boolean |
| IN | Membership | status IN (‘shipped’,’delivered’) | boolean |
| IS NULL | Null check | phone IS NULL | boolean |
| AND / OR | Logical | price < 500 AND stock > 0 | boolean |
Your mini homework right now
Try these in your bookstore_demo (or college_db):
- Find books cheaper than ₹300 using <
- Find customers whose email contains “gmail” using ILIKE
- Use ->> if you added any JSONB column
- Use && if you add a tags array column
Next class?
Tell me:
- Want deep dive into JSONB operators (most powerful in 2026)?
- Full-text search operators & tsvector / tsquery?
- Array operators with real use-cases?
- Or custom operators / operator classes (advanced)?
Your guru is ready — what’s the next topic? 🚀
