Expressions

Arithmetic operations, CASE expressions, CAST, and boolean logic in SQL.

Arithmetic

SELECT id, val * 2.0 AS doubled FROM t;
SELECT id, val + 10.0 AS shifted FROM t;
SELECT a, a % b AS remainder FROM modtest;

Note: Integer-float arithmetic automatically promotes to float:

SELECT i, i + r FROM types ORDER BY i;
-- (1, 2.5), (2, 4.5), (3, 6.5)

SELECT i, i * 1.5 FROM types ORDER BY i;
-- (1, 1.5), (2, 3.0), (3, 4.5)

String Concatenation

SELECT s || '!' FROM strs;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

CASE Expressions

Searched CASE:

SELECT i, CASE
  WHEN i = 1 THEN 'one'
  WHEN i = 2 THEN 'two'
  ELSE 'other'
END FROM t ORDER BY i;
-- (1, 'one'), (2, 'two'), (3, 'other')

Simple CASE:

SELECT i, CASE i
  WHEN 1 THEN 'one'
  WHEN 2 THEN 'two'
  ELSE 'other'
END FROM t ORDER BY i;

Comparison Operators

SELECT * FROM t WHERE val > 100;
SELECT * FROM t WHERE name = 'Alice';
SELECT * FROM t WHERE id != 3;
SELECT * FROM t WHERE val >= 10.0 AND val <= 50.0;

Supported operators: =, != / <>, <, >, <=, >=.

BETWEEN

SELECT * FROM t WHERE val BETWEEN 10.0 AND 50.0;
SELECT * FROM t WHERE val NOT BETWEEN 10.0 AND 50.0;

x BETWEEN a AND b is equivalent to x >= a AND x <= b.

IN

SELECT * FROM t WHERE id IN (1, 2, 3);
SELECT * FROM t WHERE name NOT IN ('Alice', 'Bob');

LIKE / ILIKE

SELECT * FROM t WHERE name LIKE 'A%';       -- starts with 'A'
SELECT * FROM t WHERE name LIKE '%son';      -- ends with 'son'
SELECT * FROM t WHERE name LIKE '%li%';      -- contains 'li'
SELECT * FROM t WHERE name ILIKE 'alice';    -- case-insensitive match
SELECT * FROM t WHERE name NOT LIKE '%x%';

ILIKE performs case-insensitive pattern matching using a native kernel (no LOWER() overhead).

IS NULL / IS NOT NULL

SELECT * FROM t WHERE val IS NULL;
SELECT * FROM t WHERE val IS NOT NULL;

NULL is represented internally as NaN for float columns, 0 for integers, and empty string for VARCHAR.

Boolean Logic

SELECT * FROM t WHERE a > 0 AND b > 0;
SELECT * FROM t WHERE a > 0 OR b > 0;
SELECT * FROM t WHERE NOT (a > 0);

CAST

-- Numeric CAST
SELECT CAST(i AS REAL) FROM types WHERE i = 1;   -- 1.0
SELECT i::REAL FROM types WHERE i = 1;            -- 1.0
SELECT CAST(r AS INTEGER) FROM types;             -- truncates to int

-- Temporal CAST (string literals parsed at plan time)
SELECT CAST('2025-01-15' AS DATE);
SELECT CAST('12:30:00' AS TIME);
SELECT CAST('2025-01-15 09:30:00' AS TIMESTAMP);

The :: shorthand is equivalent to CAST(... AS ...). TRY_CAST is not supported.

COALESCE and NULLIF

SELECT COALESCE(val, 0) FROM t;
SELECT NULLIF(val, 50.0) FROM t;

LEAST and GREATEST

SELECT LEAST(a, b, c) FROM t;
SELECT GREATEST(val, 0.0) FROM t;

Return the minimum or maximum value from a list of expressions (requires at least 2 arguments).

Constant Expressions (SELECT without FROM)

SELECT 1 + 2;             -- 3
SELECT 'foo' || 'bar';    -- foobar
SELECT 3.14;              -- 3.14
SELECT true;              -- true

Constant expressions can be evaluated without a FROM clause. A single-row dummy table is synthesized internally.