# Tables and SQL

[Wiki](../README.md)

**On this page**

- [A table is a property](#a-table-is-a-property)
- [Column types](#column-types)
- [Reading rows from Cypher](#reading-rows-from-cypher)
- [What clients receive](#what-clients-receive)
- [Storage and footprint](#storage-and-footprint)
- [Topic guides](#topic-guides)

A node property can hold a `TABLE`: typed rows with a primary key, indexes
and constraints, read and changed with SQL. Orders, readings, line items and
ledgers stay with the node they belong to, and a query moves between the
graph and the rows in one statement. An order of five columns with a date
index takes 63 bytes on disk as a table row, against 85 as a node of its own
(100,000 orders across 1,000 customers, Galactus DB 2.1.0, Paged). From version 2.1.0.
See it work in the [Tables and SQL demo](/demos/tables-sql).

```cypher
MATCH (c:Customer {id: 1})
SET c.orders = TABLE (
  id        INTEGER PRIMARY KEY,
  placed_at LOCAL DATETIME NOT NULL,
  sku       STRING NOT NULL,
  qty       INTEGER NOT NULL DEFAULT 1 CHECK (qty > 0),
  amount    DECIMAL(12, 2),
  INDEX by_sku (sku, placed_at)
);

MATCH (c:Customer {id: 1})
CALL (c) SQL {
  INSERT INTO c.orders (id, placed_at, sku, amount)
  VALUES (1, localdatetime(), 'SKU-00042', 129.50)
};

MATCH (c:Customer)
WHERE SQL { SELECT count(*) FROM c.orders WHERE amount > 100 } > 5
RETURN c.id;
```

## A table is a property

- `SET n.t = TABLE (…)` gives one node an empty table. Every node can have any
  number of tables, each with its own columns; nothing is declared per label.
- Tables with the same definition share one stored schema, so 20,000
  customers' `orders` tables cost one schema entry, and each row stores only
  its values.
- Properties are values: `SET d.orders = c.orders` copies the table, rows and
  indexes. `REMOVE n.t` or deleting the node drops it; its space is reclaimed
  once no reader's snapshot can see it.
- A table cannot be put in a list or map, compared with `=` or indexed as a
  property value.
- Reading a table needs `MATCH` (`READ`); changing one needs `WRITE`, as for
  any property.

## Column types

| Type | Also written | Key and index |
|---|---|---|
| `INTEGER` | `INT`, `BIGINT`, `SMALLINT`, `INT64` | Yes |
| `FLOAT` | `DOUBLE`, `REAL`, `DOUBLE PRECISION`, `FLOAT64` | Yes |
| `DECIMAL(p, s)` | `NUMERIC`; up to 38 digits, default `(38, 0)` | Yes |
| `BOOLEAN` | `BOOL` | Yes |
| `STRING`, `VARCHAR(n)` | `TEXT`, `CHAR(n)`, `CHARACTER VARYING(n)` | Yes |
| `BYTES` | `BLOB`, `BYTEA` | Yes |
| `DATE` | | Yes |
| `LOCAL TIME` | `TIME`, `TIME WITHOUT TIME ZONE` | Yes |
| `ZONED TIME` | `TIME WITH TIME ZONE` | Yes |
| `LOCAL DATETIME` | `TIMESTAMP`, `TIMESTAMP WITHOUT TIME ZONE` | Yes |
| `DATETIME` | `ZONED DATETIME`, `TIMESTAMPTZ` | Yes |
| `DURATION` | `INTERVAL` | No |
| `POINT` | | No |
| `LIST`, `MAP` | `ARRAY`, `JSON` | No |

Values convert where SQL does: an integer into a `FLOAT` or `DECIMAL`
column, a decimal rounded to the column's scale. `VARCHAR(n)` counts
characters. A table without a `PRIMARY KEY` is keyed by a hidden row number.

## Reading rows from Cypher

| Expression | Gives |
|---|---|
| `size(n.t)` | The row count, from the table's own record |
| `UNWIND n.t AS row` | Each row as a map |
| `[r IN n.t WHERE … \| …]`, `any(…)`, `reduce(…)` | List forms, up to `GDB_TABLE_MAX_LIST_ROWS` rows (default 10,000) |
| `sql.columns(n.t)`, `sql.indexes(n.t)`, `sql.type(n.t)` | The definition |
| `sql.describe(n.t)` | The definition as `TABLE (…)` text |
| `sql.bytes(n.t)` | Bytes the rows take |

SQL itself runs in `SQL { }` blocks: see [SQL](sql.md).

## What clients receive

Returning a node, or the property itself, returns a small summary in place
of the rows, whatever the table's size:

```json
{"$gdbType": "table", "version": 1, "type": "Order", "rows": 1823,
 "columns": ["id", "placed_at", "sku", "qty", "amount"]}
```

Rows come back only when a query asks for them: `CALL (n) SQL { SELECT … }`,
`UNWIND`, `sql.page` or a list form.

## Storage and footprint

Rows live in the table tree of the database, on every storage engine: in
pages on Paged, in memory and the commit log on Resident and Hybrid. A row
stores its schema id, a null bitmap and its non-null values without type
tags; an index entry stores the indexed values and the key.

- `ADD COLUMN` changes no row: rows written before read the column's
  default.
- Deleted rows and dropped tables give their space back. On Paged, a page
  left under a quarter full is joined with its neighbour as the change is
  flushed, and `CALL gdb.compact()` rewrites every page that holds dead
  space ([Compacting a database](../operations/checkpoints.md#compacting-a-database)).
- A database that never creates a table is unchanged on disk and stays
  readable by 2.0. The first table raises the oldest release that may read
  it to 2.1.0.

## Topic guides

- [SQL](sql.md): `SELECT`, `INSERT`, `UPDATE`, `DELETE`, the `SQL { }`
  forms, parameters and the `sql.` procedures.
- [Definitions](definitions.md): `ALTER TABLE`, `CHECK` constraints, indexes,
  table types and global indexes.