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;

CAST

SELECT CAST(i AS REAL) FROM types WHERE i = 1;   -- 1.0
SELECT i::REAL FROM types WHERE i = 1;            -- 1.0

COALESCE and NULLIF

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