Here's a comparison of how operators in the WHERE clause are used in Microsoft SQL Server (MSSQL) vs PostgreSQL:
| Operator | Purpose | Used in MSSQL | Used in PostgreSQL | Notes |
|---|---|---|---|---|
= | Equal to | ✅ Yes | ✅ Yes | Common in both |
< | Less than | ✅ Yes | ✅ Yes | Common in both |
> | Greater than | ✅ Yes | ✅ Yes | Common in both |
<= | Less than or equal to | ✅ Yes | ✅ Yes | Common in both |
>= | Greater than or equal to | ✅ Yes | ✅ Yes | Common in both |
<> | Not equal to | ✅ Yes | ✅ Yes | Standard SQL operator |
!= | Not equal to (alternate form) | ✅ Yes | ✅ Yes | Supported in both, though <> is standard |
LIKE | Pattern matching (case-sensitive in both by default) | ✅ Yes | ✅ Yes | PostgreSQL is case-sensitive by default; use ILIKE for insensitive |
ILIKE | Pattern matching (case-insensitive) | ❌ No | ✅ Yes | PostgreSQL-only |
AND | Logical AND | ✅ Yes | ✅ Yes | Common in both |
OR | Logical OR | ✅ Yes | ✅ Yes | Common in both |
IN | Check if a value is in a list | ✅ Yes | ✅ Yes | Common in both |
BETWEEN | Range check (inclusive) | ✅ Yes | ✅ Yes | Common in both |
IS NULL | Checks if a value is NULL | ✅ Yes | ✅ Yes | Common in both |
NOT | Logical negation (NOT LIKE, NOT IN, etc.) | ✅ Yes | ✅ Yes | Common in both |
⚠️ Key Differences:
ILIKEis only available in PostgreSQL for case-insensitive pattern matching. In MSSQL, useLOWER(column) LIKE 'pattern'orCOLLATEfor case-insensitive searches.- Pattern matching behavior (
LIKE) is case-insensitive by default in MSSQL depending on collation, but case-sensitive in PostgreSQL unlessILIKEis used.
✅ Example in MSSQL:
-- Case-sensitive search: Only matches names starting with lowercase 'e' (e.g., 'emily')
-- Even if the database or column is case-insensitive by default, we force case sensitivity using COLLATE
SELECT * FROM Employees
WHERE Department = 'HR'
AND Name COLLATE Latin1_General_CS_AS LIKE 'e%';
-- Default behavior (usually case-insensitive depending on collation)
-- This will match names starting with both 'E' and 'e' if the column uses a case-insensitive collation
SELECT * FROM Employees
WHERE Department = 'HR'
AND Name LIKE 'E%';
✅ Example in PostgreSQL:
-- Case-sensitive search: Matches names starting only with lowercase 'e'
-- PostgreSQL's LIKE is case-sensitive by default
SELECT * FROM Employees
WHERE Department = 'HR'
AND Name LIKE 'e%';
-- Case-insensitive search: Matches names starting with 'E' or 'e'
-- PostgreSQL's ILIKE operator performs case-insensitive pattern matching
SELECT * FROM Employees
WHERE Department = 'HR'
AND Name ILIKE 'E%';