# Expressions

> Combine arithmetic, strings, function calls, and type casts in SQL expressions.

---

LLMS index: [llms.txt](/en/llms.txt)

---

An *expression* is anything PostgreSQL can evaluate to a single value: a literal, a column reference, an arithmetic operation, a function call, a `CASE`, even another `SELECT` that returns one value. Expressions can appear almost anywhere — in the select list, in `WHERE`, in `ORDER BY`, in `GROUP BY`, in index definitions. Mastering expressions is mastering SQL.

## What you'll learn {#learn}

- Arithmetic, string, and boolean expressions
- Calling built-in functions in expressions
- Using expressions in `SELECT`, `WHERE`, and `ORDER BY`
- Casting between types with `::` and `CAST(... AS ...)`
- Naming expression results with `AS`

## Example {#example}

```sql {num="10.1" caption="Expressions"}
-- Arithmetic and concatenation
SELECT 10 + 2 AS sum, 10 * 2 AS product, 10 / 4 AS int_div, 10 / 4.0 AS real_div;
SELECT 'Hello ' || 'world' AS greeting;
SELECT 'fruit_' || 1 AS labeled;  -- numbers are coerced to text for ||

-- Function calls in expressions
SELECT upper(name), length(name), name || ' (#' || id || ')' AS label
FROM fruits ORDER BY id LIMIT 3;

-- Expression in WHERE
SELECT * FROM fruits WHERE id * 2 > 4 ORDER BY id LIMIT 3;

-- Expression in ORDER BY
SELECT id, name FROM fruits ORDER BY length(name) DESC LIMIT 3;

-- Casting
SELECT '42'::integer AS to_int, 42::text AS to_text, '2024-01-15'::date AS to_date;
SELECT CAST(3.7 AS integer) AS rounded_value;  -- SQL-standard cast syntax
```

The `||` operator concatenates strings. In PostgreSQL, if either operand is not text, it is coerced to text (so `'id_' || 5` works). `+`, `-`, `*`, `/`, `%` are the usual arithmetic operators; remember that integer division truncates toward zero.

PostgreSQL casts come in two flavors. `value::type` is the PostgreSQL-specific shortcut and is the common style. `CAST(value AS type)` is SQL-standard and reads better when the expression is complex. Both compile to the same thing.

Expressions in `WHERE` and `ORDER BY` are evaluated for every candidate row, which means a function call there can prevent index use. `WHERE lower(email) = 'a@b.com'` cannot use a plain index on `email`; it can use an *expression index* defined as `CREATE INDEX ON users (lower(email))`.

## Run the example {#run}

To run:

```text
$ psql -f source/expressions.sql postgres
 sum | product | int_div | real_div
-----+---------+---------+----------
  12 |      20 |       2 |      2.5
(1 row)

   greeting
-------------
 Hello world
(1 row)
 ...
```

## Common pitfalls {#pitfalls}

- Integer division returning `0`: `1 / 2` is `0`. Cast: `1.0 / 2`, `1::numeric / 2`, or use `numeric` columns.
- Casting strings that don't parse — e.g. `'abc'::integer` raises an error. PostgreSQL is strict about types compared to MySQL.
- Expressions returning different types in different branches of `CASE` cause type-mismatch errors; cast to a common type.
- Operators have precedence; `1 + 2 * 3` is `7`. When in doubt, parenthesize.

> [!TIP] Tip
> When an expression is reused several times in one query, put it in a CTE or derived table once, instead of repeating it. PostgreSQL is smart enough to fold many duplicates, but the code stays cleaner.

> [!NOTE] Try it
> Run `SELECT id, name, id || ': ' || name AS label FROM fruits ORDER BY id LIMIT 3;`. Then try `SELECT name FROM fruits WHERE length(name) > 5;`. Then cast: `SELECT '3.14'::numeric * 2 AS doubled;`.

## Downloads {#downloads}

SQL file: [expressions.sql](/examples/source/expressions.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)