Columns

A table’s columns are not fixed once it exists. You can add new ones, rename them, change their type, and remove them — in both simple and advanced mode. The examples below use the example library; to see a table’s current columns at any time, use show table:

show table books

books
┌───────────┬────────┬─────────────┐
│ Name      │ Type   │ Constraints │
├───────────┼────────┼─────────────┤
│ book_id   │ serial │ PK          │
│ title     │ text   │ NOT NULL    │
│ author_id │ int    │             │
│ published │ int    │             │
│ isbn      │ text   │ UNIQUE      │
└───────────┴────────┴─────────────┘

Add a column

add column to books: page_count (int)

The new column goes at the end of the table and is empty for existing rows. After the change, the updated structure is shown automatically. If you add a serial or shortid column — or change a column to one of those types — the empty cells are filled with freshly generated values in the same step (see Types). To fill the new column on existing rows with generated data, use seed books.page_count.

Rename a column

rename column in books: page_count to pages

Change a column’s type

change column in books: pages (text)

Changing a type re-interprets every existing value. When the conversion could lose information — turning text into int, say, where some rows do not look like numbers — the playground refuses by default and shows you exactly which rows are the problem, so nothing is silently corrupted. Two flags let you decide what should happen instead:

• --force-conversion — go ahead and convert, accepting the loss.
• --dont-convert — keep the column’s stored values as they are under the new type.

A primary-key column, or a column taking part in a relationship, is protected — remove the relationship first if you need to restructure it.

Drop a column

drop column from books: pages

If the column is covered by an index, the drop is refused so you do not lose the index by surprise. Add --cascade to drop the column and the index that covers it together:

drop column from books: pages --cascade

In advanced mode

The same operations are written as standard ALTER TABLE statements:

alter table books add column page_count int
alter table books rename column page_count to pages
alter table books alter column pages type text
alter table books drop column pages

Syntax

add column [to] [table] <Table>: <Name> (<Type>)
rename column [in] [table] <Table>: <Old> to <New>
change column [in] [table] <Table>: <Name> (<Type>) [--force-conversion | --dont-convert]
drop column [from] [table] <Table>: <Name> [--cascade]

See also Types, Constraints, and Tables.