Comparison, logical and arithmetic operators

The operators you use in conditions and calculations, and how PostgreSQL evaluates them.

Syntax

=  <>  !=  <  <=  >  >=          comparison (<> and != are the same)
AND  OR  NOT                     logic (precedence: NOT, AND, OR)
+  -  *  /  %  ^                 arithmetic (% is modulo, ^ is power)
||                               text concatenation

Integer division truncates: 7 / 2 is 3, -7 / 2 is -3. Make one side numeric (7 / 2.0, x::numeric / y) to get decimals.
% is the remainder (7 % 3 = 1); ^ is exponentiation and evaluates left-to-right (2 ^ 3 ^ 2 = 64).
Division by zero raises an error (division by zero); guard with NULLIF(denominator, 0).
Comparisons work on numbers, text (by collation), dates, timestamps and booleans. Comparing values of incompatible types (integer = text) is an error, not a silent conversion.
Precedence: arithmetic before comparison, comparison before NOT, NOT before AND, AND before OR.
NULL: every operator returns NULL when an operand is NULL, except IS [NOT] NULL, IS [NOT] DISTINCT FROM, TRUE OR NULL (TRUE) and FALSE AND NULL (FALSE).
Row comparison: (a, b) > (1, 2) compares lexicographically — handy for keyset pagination.

Examples

Arithmetic results starter

SELECT 7 / 2 AS int_div, 7 / 2.0 AS num_div, 7 % 2 AS remainder,
       2 ^ 10 AS power, round(10::numeric / 3, 2) AS rounded;

int_div is 3; num_div 3.5; power 1024; rounded 3.33.

Line value with operators shop

SELECT order_id, line_no, quantity, unit_price, discount_amount,
       quantity * unit_price - discount_amount AS line_value
FROM order_items
WHERE quantity >= 20
ORDER BY line_value DESC
LIMIT 5;

runs before -, so the discount is subtracted from the extended price.

Row comparison for keyset paging shop

SELECT order_id, line_no, product_id
FROM order_items
WHERE (order_id, line_no) > (100, 2)
ORDER BY order_id, line_no
LIMIT 5;

Rows strictly after order 100 line 2 in (order_id, line_no) order.

Pitfalls

Integer division: SUM(a) / COUNT(*) on integers drops the fraction.
Comparing a number to a quoted string from another column of type text (operator does not exist: integer = text).
Using == for equality (PostgreSQL uses =).
Using ^ for XOR (in PostgreSQL ^ is power; # is bitwise XOR).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: / always returns a decimal (7/2 = 3.5); integer division is DIV. || is logical OR unless PIPES_AS_CONCAT is set. ^ is bitwise XOR; use POW().
SQL Server: Integer division truncates like PostgreSQL. + concatenates strings; ^ is bitwise XOR, use POWER().
BigQuery: / always returns FLOAT64; use DIV(x, y) for integer division and SAFE_DIVIDE to avoid errors.
Snowflake: / on integers returns a decimal (7/2 = 3.5).
Oracle: / returns a NUMBER with the fraction (7/2 = 3.5); modulo is MOD(a, b).