Types

Every column has a type. The playground offers ten, chosen to cover what a learner needs without the sprawl of a production database.

The ten types

Type	Stores
text	Text of any length.
int	A whole number (64-bit signed).
real	A floating-point number.
decimal	An exact decimal number, kept precise (see note below).
bool	A truth value, shown as true / false.
date	A calendar date, YYYY-MM-DD.
datetime	A date and time, YYYY-MM-DDTHH:MM:SS.
blob	Arbitrary binary data.
serial	An auto-incrementing whole number — see below.
shortid	A short random identifier — see below.

Note

decimal, date, and datetime are compared as text, so they sort in the expected order when written in their standard form. Arithmetic on a decimal needs an explicit cast — they are stored exactly rather than as approximate floating point.

Auto-generated types: serial and shortid

Two types fill themselves in when you insert a row, so you never supply them by hand.

• serial — the next number in sequence (one more than the current maximum). It is the natural choice for a primary key. Outside a primary key, a serial column is kept unique.
• shortid — a short, random, base58 identifier (10–12 characters, avoiding easily-confused characters). Always kept unique. Useful when you want an identifier that is compact but not guessable or sequential.

create table members with pk member_id(serial)
add column to members: member_code (shortid)
insert into members (name) values ('Grace Hopper')

Both the member_id and the member_code are generated for you.

If you add a serial or shortid column to a table that already has rows — or change an existing column to one of these types — the empty cells are filled with freshly generated values in the same step.

Keys that point at these types

A foreign key stores a plain looked-up value, not a new generated one. So a column that references a serial primary key is itself an int, and one that references a shortid is a text. The playground handles this for you when you declare a relationship; it is worth knowing why the child column’s type differs from the parent key’s.

Type names in advanced mode

In advanced mode you may also use familiar standard-SQL spellings, which map onto the types above — for example integer, bigint, and smallint are all int; varchar and char are text; boolean is bool; timestamp is datetime; numeric is decimal; float and double precision are real. Simple mode uses only the ten names in the table, so it teaches one clear vocabulary.