---
title: Data types
description: How PizzaSQL stores and coerces values — SQLite-style type affinity over JSON-backed rows, not strict typed columns.
---

PizzaSQL follows SQLite's **type affinity** model rather than PostgreSQL's strict typing. A column's declared type is a *hint* about how values are converted and compared; it does not constrain what you can store. There is no fixed-size enforcement, no strict typing error on insert, and no `VARCHAR(n)` length limit.

Under the hood, every row is serialized as JSON in the key-value store, which shapes a lot of the behaviour below.

## Affinity rules

A column's affinity is derived from its declared type name using the SQLite rules:

| Declared type contains | Affinity |
| --- | --- |
| `INT` | INTEGER |
| `CHAR`, `CLOB`, or `TEXT` | TEXT |
| `BLOB`, or empty type | BLOB |
| `REAL`, `FLOA`, or `DOUB` | REAL |
| `BOOLEAN` / `BOOL` | BOOLEAN |
| anything else (e.g. `NUMERIC`, `DECIMAL`, `DATE`, `DATETIME`, `JSON`) | NUMERIC |

This is substring-based and case-insensitive. `VARCHAR(255)`, `TEXT`, `NCHAR`, and `CLOB` are all TEXT affinity; `INT`, `BIGINT`, `TINYINT`, and `SMALLINT` are all INTEGER affinity. `DATE`, `TIME`, `TIMESTAMP`, `DATETIME`, `JSON`, and `JSONB` are recognised as type names but all fall through to NUMERIC affinity.

## The types you can declare

| Type name | Affinity | Notes |
| --- | --- | --- |
| `INTEGER`, `INT`, `BIGINT`, `SMALLINT`, `TINYINT`, `MEDIUMINT` | INTEGER | `INTEGER PRIMARY KEY` becomes a rowid alias |
| `REAL`, `FLOAT`, `DOUBLE` | REAL | |
| `NUMERIC`, `DECIMAL(p,s)` | NUMERIC | precision/scale parsed but ignored |
| `TEXT`, `VARCHAR(n)`, `CHAR(n)`, `CHARACTER`, `CLOB`, `NCHAR`, `NVARCHAR` | TEXT | length ignored |
| `BLOB` | BLOB | stored as a string in practice |
| `BOOLEAN`, `BOOL` | BOOLEAN | stored as 1/0 |

There is no `ARRAY`, `JSONB`-specific, `UUID`, `SERIAL`, or `ENUM` type. There are no schemas or user-defined types.

## How values are actually stored

Each row is a JSON document. That means:

- **All numbers deserialize as `float64`** on the way out of storage. PizzaSQL normalizes INTEGER-affinity columns back to `int64` after reading, so arithmetic on integer columns behaves as you'd expect.
- **`BOOLEAN` is an integer** (`1`/`0`), matching SQLite. `TRUE` and `FALSE` are literal spellings for those integers.
- **BLOBs are strings** internally. There is no real binary type on the wire; over PostgreSQL the column is advertised as `BYTEA` (OID 17) but the value travels as text.
- `NULL` is represented by a Go `nil` and serializes as JSON `null`.

## Coercion

Values are coerced on use, not on insert. The key rules:

- **Arithmetic** (`+`, `-`, `*`, `/`, `%`): if both operands are integers the result is an integer (integer division truncates toward zero); otherwise operands are coerced to floats. Strings that look like numbers are parsed. Division or modulo by zero returns `NULL`.
- **Comparison** (`=`, `<`, `>` etc.): operands are compared numerically if both can be parsed as numbers, otherwise as strings.
- **`||`** concatenation: both sides are rendered as text; `NULL || 'x'` yields `'x'` (unlike PostgreSQL's `NULL` propagation).
- **Truthiness** (`toBool`): `0` and `""` and `"0"` and `"false"` (case-insensitive) are false; any other non-empty value is true; `NULL` is false when coerced to a boolean.

## ROWID

Every row carries an implicit **`_rowid_`**, even when no `INTEGER PRIMARY KEY` is declared:

- `rowid`, `oid`, and `_rowid_` all refer to the same value.
- `INTEGER PRIMARY KEY` (exactly an integer type, single column) **aliases the rowid** — the primary-key value *is* the rowid, and inserting without a value auto-assigns the next one.
- Any other primary key (e.g. `TEXT PRIMARY KEY`, or a table-level PK) is a normal column; the rowid is a separate, invisible counter maintained in parallel.
- `_rowid_` is not included in `SELECT *`; reference it explicitly (`SELECT rowid, * FROM t`).

## Type introspection

`typeof(x)` returns one of `"null"`, `"integer"`, `"real"`, `"text"`, or `"blob"` based on the value's Go representation, mirroring SQLite.

## Caveats to keep in mind

- Column types are **not enforced**. Inserting a string into an `INTEGER` column succeeds; it's stored as text and coerced back when used.
- Because rows are JSON, very large or binary payloads are less efficient than in a native database. Treat `BLOB` as best-effort.
- `CAST(x AS type)` is a value conversion at query time, not a storage change. See [Expressions](/sql-reference/expressions/).