Inserting & editing data

Once your tables exist, you fill them with rows and keep them up to date. The examples use the example library. After every write, the playground shows you the rows that changed, so you can confirm the effect at a glance.

Inserting rows

List the columns you are providing, then the matching values:

insert into members (name, joined) values ('Katherine Johnson', '2024-06-01')

  1 row(s) inserted
┌───────────┬───────────────────┬────────────┐
│ member_id │ name              │ joined     │
├───────────┼───────────────────┼────────────┤
│         3 │ Katherine Johnson │ 2024-06-01 │
└───────────┴───────────────────┴────────────┘

Notice that member_id was filled in automatically — it is a serial column, so you leave it out of the insert and the database assigns the next value. The same applies to shortid columns and to any column with a default (see Types).

Updating rows

update changes columns on the rows that match a filter:

update books set published = 1970 where book_id = 2

  1 row(s) updated
┌─────────┬───────────────────────────┬───────────┬───────────┬────────────────┐
│ book_id │ title                     │ author_id │ published │ isbn           │
├─────────┼───────────────────────────┼───────────┼───────────┼────────────────┤
│       2 │ The Left Hand of Darkness │         1 │      1970 │ 978-0441478125 │
└─────────┴───────────────────────────┴───────────┴───────────┴────────────────┘

Deleting rows

delete from authors where author_id = 3

When the deleted row has children linked by an on delete cascade relationship, those children are removed too, and the result reports each affected relationship:

  1 row(s) deleted
  related: 1 row(s) deleted in `books` for relationship `books_author` (on delete cascade)

The required filter

update and delete must have a where clause. This is a safety rail: it is far too easy to wipe or rewrite a whole table by forgetting one. When you really do mean every row, say so explicitly with --all-rows:

update books set published = 2000 --all-rows
delete from loans --all-rows

update <Table> set <col>=<value>[, ...] (where <expr> | --all-rows)
delete from <Table> (where <expr> | --all-rows)

Richer filters

A where clause is more than simple equality. It accepts and / or / not, the comparison operators, and like, is null, in, and between:

delete from loans where returned_on is null and loaned_on < '2024-01-01'

The same expression grammar drives show data — see Querying & inspecting.

In advanced mode

The simple-mode commands above already use SQL-like syntax; advanced mode is full SQL, including multi-row inserts and returning:

insert into members (name, joined) values
  ('Katherine Johnson', '2024-06-01'),
  ('Dorothy Vaughan', '2024-06-02')
update books set published = 1970 where book_id = 2
delete from authors where author_id = 3

Syntax

insert into <Table> [(<col>[, ...])] [values] (<value>[, ...])
update <Table> set <col>=<value>[, ...] (where <expr> | --all-rows)
delete from <Table> (where <expr> | --all-rows)

To fill a table with many rows at once instead of typing each one, see Generating sample data.

See also Querying & inspecting and Constraints.