# Column types

Source: https://modulify.ai/docs/data/column-types

Every Postgres type the CMS understands and the editor it gets.

The CMS picks a field's editor from the column's Postgres type, from metadata registered in `_collection_fields`, and in a few cases from the values already in the column. This page lists what you get for each.

## How the editor is chosen

The checks run in order, and the first match wins:

1. A multi reference field registered in `_collection_fields`.
2. A real foreign key column.
3. A Postgres enum type.
4. An `hstore` column.
5. An explicit `ui_type` of `color`, `richtext`, `file` or `image`.
6. The column's type group, from the table below.
7. For plain text columns with no metadata, the shape of the values already stored.

## By type group

| Group | Postgres types | Editor | Table cell |
|---|---|---|---|
| String | `text`, `varchar`, `character varying`, `char`, `character`, `bpchar`, `name`, `citext` | Single line input, or a six row textarea for long text | Text, truncated |
| UUID | `uuid` | Text input, hinted `00000000-0000-0000-0000-000000000000` | Text |
| Number | `integer`, `bigint`, `smallint`, `numeric`, `real`, `double precision`, `decimal`, `serial`, `bigserial`, `smallserial`, `int`, `int2`, `int4`, `int8`, `float4`, `float8`, `oid` | Number input | Tabular figures |
| Money | `money` | Text input with a `$` prefix and the hint "Enter a dollar amount, e.g. 1000.00" | Text |
| Boolean | `boolean`, `bool` | Dropdown of `true` and `false` | `true` or `false` |
| JSON | `json`, `jsonb` | JSON editor that validates as you type | The JSON, stringified |
| Date | `date`, `timestamp`, `timestamptz`, `timestamp with time zone`, `timestamp without time zone` | Date and time picker | Formatted date, tabular figures |
| Time | `time`, `timetz`, `time with time zone`, `time without time zone` | Hour, minute and second selects | Formatted time |
| Interval | `interval` | Text input, hinted "e.g. 2 hours 30 minutes" | Text |
| Array | `array` (any `<type>[]`) | Chip input, one value at a time, each validated against the base type | Up to six pills, then `+n` |
| Binary | `bytea` | Two mode editor, **text** which encodes as you type, or **hex** for a raw `\x` literal | Text |
| Enum | any Postgres enum (`user-defined`) | Dropdown of the enum's values, with `None` when the column is nullable | Text |
| Network | `cidr`, `inet`, `macaddr`, `macaddr8` | Text input with a format hint and validation | Text |
| Geometric | `point`, `line`, `lseg`, `box`, `path`, `polygon`, `circle` | Shape aware numeric fields, x and y per point | Rendered back to its literal |
| Bit | `bit`, `bit varying`, `varbit` | Text input accepting only `0` and `1` | Text |
| Range | `int4range`, `int8range`, `numrange`, `tsrange`, `tstzrange`, `daterange` | A **from** and a **to** field, numeric, date or date and time depending on the range | Text |
| Key value | `hstore` | Rows of key and value inputs with an **Add pair** button | Text |
| Text search | `tsvector`, `tsquery` | Text input with a syntax hint | Text |
| XML | `xml` | Text input validated as well formed XML | Text |

Anything the CMS does not recognise falls back to a plain text input.

## Metadata driven fields

These four are declared by the AI in `_collection_fields` at the same time the column is created. The Postgres type stays `text` (or `text[]` for a gallery), and only the metadata decides the widget.

| `ui_type` | Column type | Editor |
|---|---|---|
| `color` | `text` | Color swatch with a native color picker, validated as a hex color |
| `richtext` | `text` | WYSIWYG editor: bold, italic, underline, strikethrough, paragraph and headings 1 to 6, quote, bullet and numbered lists, links and inline images |
| `file` | `text` | File upload with drag and drop, showing the file name with download and replace actions. Stores the uploaded file's URL |
| `image` | `text` | Image upload with a preview, replace, delete and an alt text popover |
| `image` | `text[]` | Gallery upload, an ordered list of images each with its own alt text |

An image column always comes with a companion column named `<column>_alt` of the same type. The CMS hides that companion and edits it from inside the image widget.

Without the metadata, a text column holding a file URL is indistinguishable from any other string, so it renders as a plain text box. That is why the AI registers it up front.

## Detected without metadata

Two things are inferred from the values already in a column, so they can appear on a column nobody registered:

- **Rich text.** A value containing HTML markup opens in the rich text editor rather than a text box.
- **Images.** A text column whose values look like image URLs is treated as an image field, as is an array column whose elements do. This needs at least one non empty value to go on, which is why a freshly created image column still needs its `image` metadata.

A hex color value in a plain text column also switches that field to the color picker.

Long versus short text is inferred too. Column names like `description`, `content`, `body`, `bio`, `summary` and `notes` get a textarea. Names like `name`, `title`, `slug`, `email`, `url`, `status` and `code` get a single line input. Failing both, a value with a line break or longer than 150 characters gets the textarea.

## Relationship fields

| Kind | How it is stored | Editor |
|---|---|---|
| Single reference | An ordinary foreign key column, for example `author_id uuid REFERENCES authors(id)` | Searchable single select of rows in the target collection, loading twenty at a time |
| Multi reference | A join table plus a `multi_reference` row in `_collection_fields` | Multi select with checkboxes and removable chips, searchable, paged twenty at a time |

Single references are read straight from Postgres's foreign key catalog, so they need no metadata. The field label drops a trailing `_id`, so `author_id` reads "Author".

Multi reference join tables are hidden from the Collections sidebar. Turn on **Show system collections** in the CMS Settings sub-tab to see them.

## System columns

| Column | Behaviour |
|---|---|
| The primary key, usually `id` | Never editable. Reads "Auto-generated" on a new row |
| `created_at`, `created_on`, `inserted_at`, `inserted_on` | Hidden while creating, read only when editing |
| `updated_at`, `updated_on`, `modified_at`, `modified_on` | Read only. Re-stamped by a database trigger on every edit |
| `slug` | Slug input with a live preview of the row's public URL, click to copy. Auto filled from `title` or `name` while creating, until you edit it |

## Format hints

Columns with a fiddly literal syntax show an example as their placeholder.

| Type | Hint |
|---|---|
| `uuid` | `00000000-0000-0000-0000-000000000000` |
| `money` | `$1,000.00` |
| `inet` | `192.168.0.1` |
| `cidr` | `192.168.0.0/24` |
| `macaddr` | `08:00:2b:01:02:03` |
| `point` | `(x,y)` |
| `circle` | `<(x,y),r>` |
| `box` | `(x1,y1),(x2,y2)` |
| `polygon` | `((x1,y1),(x2,y2),...)` |
| `bit` | `10101010` |
| `int4range` | `[1,10)` |
| `daterange` | `[2026-01-01, 2026-12-31)` |
| `tsquery` | `quick & brown  (& = and, \| = or, ! = not)` |
| `hstore` | `"key" => "value", "k2" => "v2"` |
| `bytea` | `\x48656c6c6f` |
| `interval` | `1 day 02:30:00` |
| `xml` | `<root>…</root>` |

## Validation

The editor blocks a save and shows the reason under the field.

| Case | Message |
|---|---|
| A `NOT NULL` column left empty | Required |
| Malformed JSON | Invalid JSON |
| A non numeric value in a number or money column | Must be a number |
| A decimal in an integer column | Must be a whole number (no decimals) |
| An integer outside its type's range | Out of range for integer (-2147483648 to 2147483647) |
| A malformed `uuid` | Must be a UUID (00000000-0000-0000-0000-000000000000) |
| A malformed `inet` | Must be an IP address, e.g. 192.168.1.5 or 192.168.1.5/24 |
| A `cidr` with no mask | Must include a network mask, e.g. 192.168.1.0/24 |
| A malformed MAC address | Must be a MAC address (08:00:2b:01:02:03) |
| A bit string with anything but 0 and 1 | Only 0 and 1 are allowed |
| Malformed XML | Must be well-formed XML |

Integer bounds are enforced per type: `smallint` -32768 to 32767, `integer` -2147483648 to 2147483647, `bigint` -9223372036854775808 to 9223372036854775807, and the `serial` variants from 1 up to the same ceiling.

## Next

- [The CMS](/docs/data/cms) shows these fields in context.
- [Site databases](/docs/data/databases) explains how columns get created.