PostgreSQL - Operators

PostgreSQL - Modern Guide to Operators

What is an Operator in PostgreSQL?

An operator is a special character or keyword that performs an operation on one or more values (operands). Operators are fundamental to SQL, used extensively in the SELECT list to compute new values and in the WHERE clause to filter data based on specific conditions.

PostgreSQL provides a rich set of operators, which can be categorized as follows:

• Arithmetic Operators
• Comparison Operators
• Logical Operators
• Bitwise & Bit String Operators
• Pattern Matching Operators
• Modern Operators for JSON and Arrays

PostgreSQL Arithmetic Operators

Arithmetic operators perform mathematical calculations. All examples below can be run directly in a SQL client like psql or DBeaver.

Operator	Description	Example
+	Addition	SELECT 10 + 5; — Returns 15
-	Subtraction	SELECT 10 - 5; — Returns 5
*	Multiplication	SELECT 10 * 5; — Returns 50
/	Division. Note: Integer division truncates the result.	SELECT 10 / 4; — Returns 2
::numeric	To get a precise division, cast one operand to a numeric type.	SELECT 10::numeric / 4; — Returns 2.50
%	Modulus (Remainder)	SELECT 10 % 4; — Returns 2
^	Exponentiation	SELECT 2 ^ 3; — Returns 8
|/	Square Root	SELECT |/ 25.0; — Returns 5
||/	Cube Root	SELECT ||/ 27.0; — Returns 3
!	Factorial (postfix)	SELECT 5!; — Returns 120

PostgreSQL Comparison Operators

Comparison operators are used in the WHERE clause to filter rows. Let’s use a sample employees table:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INT
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 80000),
('Bob', 'Engineering', 95000),
('Charlie', 'HR', 60000);

Operator	Description	Example
=	Equal to	SELECT name FROM employees WHERE department = ‘HR’; — Returns Charlie
<> or !=	Not equal to. The <> operator is standard SQL.	SELECT name FROM employees WHERE department <> ‘HR’; — Returns Alice, Bob
>	Greater than	SELECT name FROM employees WHERE salary > 80000; — Returns Bob
<	Less than	SELECT name FROM employees WHERE salary < 80000; — Returns Charlie
>=	Greater than or equal to	SELECT name FROM employees WHERE salary >= 80000; — Returns Alice, Bob
<=	Less than or equal to	SELECT name FROM employees WHERE salary <= 80000; — Returns Alice, Charlie
IS NULL	Checks if a value is NULL.	SELECT name FROM employees WHERE salary IS NULL;
IS NOT NULL	Checks if a value is not NULL.	SELECT name FROM employees WHERE salary IS NOT NULL;

PostgreSQL Logical Operators

Logical operators combine multiple boolean conditions. Operator precedence is NOT, then AND, then OR. Use parentheses () to control the order of evaluation.

Operator	Description	Example (using the employees table)
AND	Returns true if both conditions are true.	SELECT name FROM employees WHERE department = ‘Engineering’ AND salary > 90000; — Returns Bob
OR	Returns true if either condition is true.	SELECT name FROM employees WHERE department = ‘HR’ OR salary > 90000; — Returns Bob, Charlie
NOT	Reverses the result of a logical expression.	SELECT name FROM employees WHERE NOT department = ‘Engineering’; — Returns Charlie

Bitwise and Bit String Operators

These operators perform bit-level manipulations on integer types or on bit / bit varying string types.

Bitwise Operators on Integers

Let A = 60 (binary 00111100) and B = 13 (binary 00001101)

Operator	Description	Example
&	Bitwise AND	SELECT 60 & 13; — Returns 12 (binary 00001100)
|	Bitwise OR	SELECT 60 | 13; — Returns 61 (binary 00111101)
#	Bitwise XOR (Exclusive OR)	SELECT 60 # 13; — Returns 49 (binary 00110001)
~	Bitwise NOT (Inverts bits)	SELECT ~60; — Returns -61
<<	Bitwise Left Shift	SELECT 60 << 2; — Returns 240 (binary 11110000)
>>	Bitwise Right Shift	SELECT 60 >> 2; — Returns 15 (binary 00001111)

Bit String Operators

SELECT B'10101' | B'01101'; -- Returns 11101
SELECT B'10101' & B'01101'; -- Returns 00101
SELECT 'hello' || ' world'; -- String concatenation, not bitwise OR

Modern and Special-Purpose Operators

PostgreSQL’s power is extended with operators for modern data types like JSONB and arrays.

• Pattern Matching: LIKE (case-sensitive), ILIKE (case-insensitive) for simple wildcard matching. ~ for POSIX regular expression matching.
• Array Operators: @> (contains), <@ (is contained by), && (overlaps). Example: SELECT '{1, 4, 3}'::int[] @> '{3, 1}'::int[]; -- returns true
• JSONB Operators: -> (get object field), ->> (get object field as text), ? (key exists), @> (contains). These are essential for working with schemaless data.

Common Mistakes & Best Practices

• Integer Division: Remember 5 / 2 is 2. Use casting like 5::numeric / 2 for precise results.
• NULL Propagation: Comparisons with NULL (e.g., col = NULL) result in NULL, not true or false. Always use IS NULL or IS NOT NULL.
• Operator Precedence: When mixing AND and OR, always use parentheses () to ensure your intended logic is executed. Example: WHERE (department = 'HR' OR department = 'Sales') AND status = 'active';