PostgreSQL Operators and Expressions

Q: Which is better, :: or CAST() ?

:: is more concise and is PostgreSQL-specific syntax; CAST() is the SQL-standard form. For pure PostgreSQL projects, :: is recommended; use CAST() when you need cross-database compatibility.

Q: What are the pitfalls of NULL in logical operations?

TRUE AND NULL = NULL (not TRUE), FALSE OR NULL = NULL (not FALSE), NOT NULL = NULL . In WHERE, NULL is treated as not TRUE, so the relevant rows are excluded.

Q: Do range operators only work on built-in Range types?

Yes, operators like @> <@ && act on PostgreSQL's built-in Range types (int4range, numrange, daterange, etc.). Regular columns must first be turned into a Range value before comparison.

Q: Is the ^ power operator higher precedence than multiplication/division?

Yes, ^ binds tighter than * / % . 2^3^2 = 2^(3^2) = 512 (right-associative), not (2^3)^2 = 64 .

Q: What's the difference between IS DISTINCT FROM and <> ?

With <> , NULL <> NULL returns NULL (neither TRUE nor FALSE). IS DISTINCT FROM treats NULL as a comparable value: NULL IS DISTINCT FROM NULL returns FALSE, and 1 IS DISTINCT FROM NULL returns TRUE.

1. What You'll Learn

Arithmetic operators + - * / % and their overflow and precision behavior
Comparison and logical operators
String concatenation || and pattern-matching operators
Type casts :: and CAST()
Row constructors ROW() and range operators @> <@ &< &> -
Subquery expressions IN / EXISTS / ANY / ALL

2. The Story

Charlie is a financial analyst at a SaaS platform, responsible for:

Computing the after-tax amount of each order (total * 1.08)
Checking whether an order falls within a given date range
Converting string-formatted dates to the DATE type for comparison
Checking whether price ranges overlap

These tasks require him to be fluent with PostgreSQL's various operators and expressions.

3. Concept: Arithmetic Operators

(1) Basic Arithmetic Operators

Operator	Meaning	Example	Result
`+`	Add	`100 + 8`	`108`
`-`	Subtract	`500 - 50`	`450`
`*`	Multiply	`29.99 * 3`	`89.97`
`/`	Integer divide (truncate)	`7 / 2`	`3`
`/`	Numeric divide (with decimal)	`7.0 / 2`	`3.5`
`%`	Modulo	`10 % 3`	`1`
`^`	Power	`2 ^ 10`	`1024`
`	/`	Square root	`
`		/`	Cube root
`!`	Factorial	`5 !`	`120`
`@`	Absolute value	`@ -15`	`15`

▶ Example: Computing After-Tax Amount

SQL

SELECT
  order_id,
  total_amount,
  total_amount * 0.08 AS tax,
  total_amount * 1.08 AS total_with_tax
FROM orders
WHERE order_status = 'completed';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Integer Division vs Numeric Division

SQL

SELECT
  7 / 2 AS int_div,
  7.0 / 2 AS numeric_div,
  7 / 2.0 AS numeric_div2,
  CAST(7 AS NUMERIC) / 2 AS cast_div;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Operator Precedence

Precedence	Operator	Description
1 (highest)	`.`	member access
2	`::`	type cast
3	`[`	array subscript
4	`-` (unary), `@`, `!`	unary operations
5	`^`	power
6	`*`, `/`, `%`	multiply / divide / modulo
7	`+`, `-`	add / subtract
8	`
9	comparison operators	`= <> < > <= >=`
10	`IS`, `IN`, `BETWEEN`	predicates
11	`NOT`	logical not
12	`AND`	logical and
13 (lowest)	`OR`	logical or

▶ Example: Precedence and Parentheses

SQL

SELECT
  2 + 3 * 4 AS no_parens,
  (2 + 3) * 4 AS with_parens;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Overflow and Precision

PostgreSQL's INTEGER ranges from about -2.1 billion to 2.1 billion; exceeding it raises an error. For money calculations, use NUMERIC(precision, scale) or DECIMAL.

Type	Range	Precision	Best for
`SMALLINT`	-32768 ~ 32767	integer	small-range counts
`INTEGER`	±2.1 billion	integer	general integers
`BIGINT`	±9.2 quintillion	integer	large-range IDs
`NUMERIC(p,s)`	unlimited	arbitrary precision	money calculations

▶ Example: Safe Precision for Money Calculations

SQL

SELECT
  order_id,
  total_amount::NUMERIC(12,2) AS amount,
  (total_amount::NUMERIC(12,2) * 0.08)::NUMERIC(12,2) AS tax,
  (total_amount::NUMERIC(12,2) * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

4. Concept: Comparison Operators

(1) Basic Comparison

Operator	Meaning	`1 <> 2`	`NULL = NULL`
`=`	Equal	FALSE	NULL
`<>` / `!=`	Not equal	TRUE	NULL
`<`	Less than	TRUE	NULL
`>`	Greater than	FALSE	NULL
`<=`	Less than or equal	TRUE	NULL
`>=`	Greater than or equal	FALSE	NULL

Any comparison with NULL yields NULL, not TRUE or FALSE.

▶ Example: Comparison Operations

SQL

SELECT product_name, unit_price
FROM products
WHERE unit_price >= 100
  AND unit_price < 500
  AND category = 'Electronics';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) BETWEEN and Compound Comparisons

Expression	Equivalent
`x BETWEEN a AND b`	`x >= a AND x <= b`
`x NOT BETWEEN a AND b`	`x < a OR x > b`

▶ Example: BETWEEN Range Comparison

SQL

SELECT order_id, total_amount, order_date
FROM orders
WHERE total_amount BETWEEN 100 AND 1000
  AND order_date BETWEEN '2025-01-01' AND '2025-12-31';

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) IS DISTINCT FROM

IS DISTINCT FROM treats NULL as a comparable value, avoiding the NULL pitfall of standard comparisons.

Expression	`NULL = NULL`	`NULL IS DISTINCT FROM NULL`
Result	NULL	FALSE
`1 IS DISTINCT FROM NULL`	—	TRUE

▶ Example: IS DISTINCT FROM

SQL

SELECT order_id, old_status, new_status
FROM order_status_log
WHERE old_status IS DISTINCT FROM new_status;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Concept: Logical Operators

(1) Three-Valued Logic Truth Table

PostgreSQL uses three-valued logic (TRUE / FALSE / NULL); when NULL takes part in a logical operation, the result can be counterintuitive.

a	b	a AND b	a OR b
T	T	T	T
T	F	F	T
T	N	N	T
F	T	F	T
F	F	F	F
F	N	F	N
N	T	N	T
N	F	F	N
N	N	N	N

▶ Example: NULL in Logical Operations

SQL

SELECT
  TRUE AND NULL AS and_result,
  TRUE OR NULL AS or_result,
  FALSE AND NULL AS and_false,
  FALSE OR NULL AS or_null,
  NOT NULL AS not_result;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: NULL Behavior in WHERE

SQL

-- This excludes rows where discount_rate is NULL
SELECT product_name, discount_rate
FROM products
WHERE discount_rate > 0 OR discount_rate = 0;
-- NULL rows are NOT included because NULL > 0 is NULL, NULL = 0 is NULL

-- Fix: explicitly handle NULL
SELECT product_name, discount_rate
FROM products
WHERE discount_rate > 0 OR discount_rate = 0 OR discount_rate IS NULL;

TEXT

 count 
-------
     5
(1 row)

6. Concept: String Operators

(1) String Concatenation `||`

|| is PostgreSQL's string concatenation operator, unlike SQL Server's +.

Operator	Meaning	Example
`		`

▶ Example: String Concatenation

SQL

SELECT
  customer_id,
  first_name || ' ' || last_name AS full_name,
  'Order #' || order_id || ': $' || total_amount::TEXT AS order_summary
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

TEXT

  result  
----------
   42.50
(1 row)

(2) Concatenating Strings with NULL

Concatenating any string with NULL yields NULL; use COALESCE to handle it.

Expression	Result
`'A'
`'A'

▶ Example: Safe String Concatenation

SQL

SELECT
  product_name || COALESCE(' - ' || description, '') AS product_info
FROM products;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

7. Concept: Type Casts

(1) `::` vs `CAST()`

Form	Example	Standard SQL	Description
`::type`	`'2025-01-01'::DATE`	No (PG-specific)	Concise, recommended in PG
`CAST(expr AS type)`	`CAST('2025-01-01' AS DATE)`	Yes	Portable, verbose

▶ Example: String to Date

SQL

SELECT
  order_id,
  order_date::DATE AS date_only,
  '2025-06-15'::DATE AS literal_date,
  CAST('2025-06-15 14:30:00' AS TIMESTAMP) AS full_timestamp
FROM orders
WHERE order_date::DATE >= '2025-01-01'::DATE;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Numeric Type Conversion

SQL

SELECT
  total_amount::NUMERIC(12,2) AS rounded,
  total_amount::INTEGER AS truncated,
  total_amount::TEXT AS as_text
FROM orders;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Common Cast Scenarios

Source → Target	Usage
TEXT → DATE	`'2025-01-01'::DATE`
TEXT → TIMESTAMP	`CAST(ts AS TIMESTAMP)`
NUMERIC → TEXT	`123.45::TEXT`
INTEGER → NUMERIC	`id::NUMERIC`
TEXT → INTEGER	`'42'::INTEGER`

▶ Example: Charlie's Date Range Check

SQL

SELECT order_id, total_amount, order_date
FROM orders
WHERE order_date >= '2025-01-01'::DATE
  AND order_date < '2025-07-01'::DATE
  AND total_amount::NUMERIC(12,2) > 500;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

8. Concept: Row Constructors and Subquery Expressions

(1) ROW Constructor

ROW(val1, val2, ...) builds an anonymous row value, useful for row-level comparisons.

▶ Example: Row Constructor Comparison

SQL

SELECT order_id, order_status, total_amount
FROM orders
WHERE ROW(order_status, total_amount) = ROW('completed', 500);

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Row Comparison and IN

A row constructor can be paired with IN for multi-column matching.

▶ Example: Multi-Column IN Matching

SQL

SELECT order_id, customer_id, order_status
FROM orders
WHERE (order_status, customer_id) IN (
  ('completed', 1001),
  ('completed', 1002),
  ('pending', 1003)
);

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Subquery Expression Comparison

Expression	Semantics	Return type
`col IN (subquery)`	Equal to any value in the subquery result	BOOLEAN
`col = ANY(subquery)`	Same as IN	BOOLEAN
`col > ALL(subquery)`	Greater than all values in the subquery	BOOLEAN
`EXISTS (subquery)`	Whether the subquery returned any rows	BOOLEAN

▶ Example: ALL Comparison Subquery

SQL

SELECT product_name, unit_price, category
FROM products
WHERE unit_price > ALL (
  SELECT AVG(unit_price) FROM products GROUP BY category
);

TEXT

  result  
----------
   42.50
(1 row)

9. Concept: Range Operators

(1) Range Types

PostgreSQL has built-in range types such as int4range, int8range, numrange, daterange, and tstzrange.

▶ Example: Creating a daterange

SQL

SELECT daterange('2025-01-01'::DATE, '2025-06-30'::DATE) AS h1_range;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Range Operator Reference

Operator	Meaning	Example
`@>`	Contains element	`'[1,5]'::int4range @> 3` → TRUE
`<@`	Is contained by	`3 <@ '[1,5]'::int4range` → TRUE
`&&`	Overlaps	`'[1,5]'::int4range && '[3,8]'::int4range` → TRUE
`&<`	Does not extend right of	`'[1,3]'::int4range &< '[2,5]'::int4range` → TRUE
`&>`	Does not extend left of	`'[3,5]'::int4range &> '[1,3]'::int4range` → TRUE
`-	-`	Is adjacent to
`+`	Union	`'[1,3]'::int4range + '[3,6]'::int4range` → `[1,6)`
`*`	Intersection	`'[1,5]'::int4range * '[3,8]'::int4range` → `[3,5)`
`-`	Difference	`'[1,5]'::int4range - '[3,8]'::int4range` → `[1,3)`

▶ Example: Checking Date Range Overlap

SQL

SELECT o1.order_id, o2.order_id
FROM orders o1, orders o2
WHERE o1.customer_id = o2.customer_id
  AND o1.order_id < o2.order_id
  AND daterange(o1.order_date, o1.delivery_date) &&
      daterange(o2.order_date, o2.delivery_date);

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Price Range Containment Check

SQL

SELECT product_name, unit_price
FROM products
WHERE numrange(100, 500) @> unit_price::NUMERIC;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

10. Flowchart: Type Cast Decision

flowchart TD
    A[Need a type cast?] --> B{Target type?}
    B -->|Date/Time| C["::DATE / ::TIMESTAMP"]
    B -->|Numeric| D["::NUMERIC / ::INTEGER"]
    B -->|String| E["::TEXT"]
    B -->|Portability needed| F["CAST(expr AS type)"]
    C --> G{Does source have format?}
    G -->|Standard format| H[Direct :: cast]
    G -->|Custom format| I["TO_DATE() / TO_TIMESTAMP()"]
    D --> J[Watch for precision loss]
    E --> K[Watch for NULL concatenation]
    H --> L[Done]
    I --> L
    J --> L
    K --> L
    F --> L

    style A fill:#e1f5fe
    style L fill:#c8e6c9

11. Comprehensive Example

Charlie completed all the calculations his financial analysis required.

SQL

-- Step 1: Tax calculation with precision control
SELECT
  order_id,
  total_amount::NUMERIC(12,2) AS amount,
  (total_amount::NUMERIC(12,2) * 0.08)::NUMERIC(12,2) AS tax,
  (total_amount::NUMERIC(12,2) * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders
WHERE order_status = 'completed'
  AND order_date >= '2025-01-01'::DATE;

-- Step 2: Date range check with daterange
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE daterange('2025-01-01'::DATE, '2025-07-01'::DATE) @> order_date::DATE
  AND order_status <> 'cancelled';

-- Step 3: String-to-date conversion and formatting
SELECT
  order_id,
  'Order #' || order_id || ' - ' ||
  COALESCE(customer_name, 'Unknown') ||
  ' ($' || total_amount::TEXT || ')' AS order_label
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date::DATE >= '2025-01-01'::DATE
ORDER BY o.total_amount DESC;

-- Step 4: Row constructor and range overlap
SELECT
  p1.product_name AS product_a,
  p2.product_name AS product_b,
  numrange(p1.unit_price, p1.unit_price * 1.2) AS price_range_a,
  numrange(p2.unit_price, p2.unit_price * 1.2) AS price_range_b
FROM products p1
JOIN products p2 ON p1.product_id < p2.product_id
WHERE p1.category = p2.category
  AND numrange(p1.unit_price, p1.unit_price * 1.2) &&
      numrange(p2.unit_price, p2.unit_price * 1.2)
  AND p1.is_active = true AND p2.is_active = true;

❓ FAQ

Q Does integer division 7/2 return 3 or 3.5?

A It returns 3. In PostgreSQL, dividing two integers truncates to an integer. To get 3.5, at least one operand must be a decimal type: 7.0/2 or 7::NUMERIC/2.

Q Which is better, :: or CAST()?

A :: is more concise and is PostgreSQL-specific syntax; CAST() is the SQL-standard form. For pure PostgreSQL projects, :: is recommended; use CAST() when you need cross-database compatibility.

Q What are the pitfalls of NULL in logical operations?

A TRUE AND NULL = NULL (not TRUE), FALSE OR NULL = NULL (not FALSE), NOT NULL = NULL. In WHERE, NULL is treated as not TRUE, so the relevant rows are excluded.

Q Why is concatenating a string with NULL NULL?

A The SQL standard says any string concatenation with NULL yields NULL. Replace NULL with COALESCE(col, '') before concatenating.

Q When is row constructor comparison useful?

A When you need to compare a combination of several columns at once—e.g., WHERE (status, amount) = ROW('completed', 500)—it's cleaner than comparing two columns separately, and it also works with multi-column IN.

Q Do range operators only work on built-in Range types?

A Yes, operators like @> <@ && act on PostgreSQL's built-in Range types (int4range, numrange, daterange, etc.). Regular columns must first be turned into a Range value before comparison.

Q Is the ^ power operator higher precedence than multiplication/division?

A Yes, ^ binds tighter than * / %. 2^3^2 = 2^(3^2) = 512 (right-associative), not (2^3)^2 = 64.

Q What's the difference between IS DISTINCT FROM and <>?

A With <>, NULL <> NULL returns NULL (neither TRUE nor FALSE). IS DISTINCT FROM treats NULL as a comparable value: NULL IS DISTINCT FROM NULL returns FALSE, and 1 IS DISTINCT FROM NULL returns TRUE.

📖 Summary

Arithmetic operators: watch integer-division truncation and precision; use NUMERIC(p,s) for money
Comparison operators yield NULL when compared with NULL; use IS DISTINCT FROM for safe comparison
In three-valued logic, NULL AND TRUE = NULL and NULL OR FALSE = NULL
String concatenation || returns NULL on NULL; handle it with COALESCE
:: is PG-specific cast syntax; CAST() is the SQL standard
Row constructors ROW() support multi-column comparison and multi-column IN
Range operators @> <@ && provide efficient Range containment and overlap checks

📝 Exercises

⭐ Write a query that computes unit_price * quantity for each row in order_items, casts the result to NUMERIC(10,2), and orders by line amount descending.
⭐⭐ Write a query that uses daterange and the @> operator to find orders whose order_date falls in Q1 2025 (Jan 1 – Mar 31), and use || to build an order summary string (format: "Order #id - customer_name - $amount"), handling NULL customer names.
⭐⭐⭐ Write a query that finds pairs of products in the same category whose price ranges overlap (build the range with numrange(unit_price, unit_price * 1.2), and check overlap with &&), and show whether the row-constructor comparison ROW(a.price, a.category) = ROW(b.price, b.category) is equal for each pair.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Operators and Expressions

1. What You'll Learn

2. The Story

3. Concept: Arithmetic Operators

(1) Basic Arithmetic Operators

▶ Example: Computing After-Tax Amount

▶ Example: Integer Division vs Numeric Division

(2) Operator Precedence

▶ Example: Precedence and Parentheses

(3) Overflow and Precision

▶ Example: Safe Precision for Money Calculations

4. Concept: Comparison Operators

(1) Basic Comparison

▶ Example: Comparison Operations

(2) BETWEEN and Compound Comparisons

▶ Example: BETWEEN Range Comparison

(3) IS DISTINCT FROM

▶ Example: IS DISTINCT FROM

5. Concept: Logical Operators

(1) Three-Valued Logic Truth Table

▶ Example: NULL in Logical Operations

▶ Example: NULL Behavior in WHERE

6. Concept: String Operators

(1) String Concatenation ||

▶ Example: String Concatenation

(2) Concatenating Strings with NULL

▶ Example: Safe String Concatenation

7. Concept: Type Casts

(1) :: vs CAST()

▶ Example: String to Date

▶ Example: Numeric Type Conversion

(2) Common Cast Scenarios

▶ Example: Charlie's Date Range Check

8. Concept: Row Constructors and Subquery Expressions

(1) ROW Constructor

▶ Example: Row Constructor Comparison

(2) Row Comparison and IN

▶ Example: Multi-Column IN Matching

(3) Subquery Expression Comparison

▶ Example: ALL Comparison Subquery

9. Concept: Range Operators

(1) Range Types

▶ Example: Creating a daterange

(2) Range Operator Reference

▶ Example: Checking Date Range Overlap

▶ Example: Price Range Containment Check

10. Flowchart: Type Cast Decision

11. Comprehensive Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好

(1) String Concatenation `||`

(1) `::` vs `CAST()`