---
title: "Tables with primary keys: create, insert all-or-none, read and delete by key"
slug: semurg-as-a-relational-database
date: 2026-11-01
author: Semurg
section: how-to
tags: [relational, sql, database, how-to]
summary: A relational door on the same store as your graph: create a table over the PostgreSQL wire, insert rows all or none, read and delete them by primary key from psql or over REST, with every answer saying present, absent or could not judge.
status: published
covers: [route:POST /v1/query, op:read_rows, op:insert_rows, op:delete_rows]
constants: [2, 3, 5432, 4000, 409]
evidence:
  - release:r11.142.20261030.e037138
---
> Last checked on r11.142.20261030.e037138 (30 Oct 2026): every command below ran on a fresh node of that release. The current release is r11.143.20261102.b5a9758 (2 Nov 2026), and the commands have not been re-checked on it yet.

A table with a primary key answers one question very often: what is the row under this key? Semurg answers it from the same store as your graph, your vectors and your memory, and
you reach it two ways: the PostgreSQL wire protocol, so `psql` and any Postgres driver connect, and the `/v1/query` door over REST. This post creates a table, puts rows in, reads them back by key and deletes them.

> Diagram rel-flow: A client creates a table, inserts rows all or none, reads rows by primary key and deletes rows by primary key; each read answers with the row, with absent, or with could not judge.

## Connect

The commands on this page ran on the release published on 30 October 2026.[^1]

The node's SQL door listens on the machine's loopback, port 5432, and the password is your API token. Install a client once (put `sudo` in front when you are not root):

```bash runnable kind=shell-node needs=none
apt-get update -qq && apt-get install -y -qq postgresql-client && psql --version
```

```expect
expect: the client installs and reports its version
assert: rc=0;out~^psql
```

Every command below reads the token from the environment, as in the other guides ([the token file steps](/blog/claude-code-persistent-memory) show where to keep it), and never writes it into a command.

## Create a table and insert rows

A table needs a primary key. This creates one and inserts two rows in one statement:

```bash runnable kind=shell-node needs=token
export SEMURG_API_TOKEN=<your token>
export PGPASSWORD="$SEMURG_API_TOKEN"
psql -h 127.0.0.1 -p 5432 -U semurg -d semurg \
  -c "CREATE TABLE IF NOT EXISTS people (id BIGINT PRIMARY KEY, name TEXT, city TEXT)" \
  -c "INSERT INTO people VALUES (1, 'Ada', 'Paris'), (2, 'Bo', 'Oslo')"
```

```expect
expect: the table is created and both rows are inserted
assert: out~^CREATE TABLE$;out~^INSERT 0 2$
```

## Read by key

Read one row, or several keys at once. A key that holds nothing is not in the answer, and the count says how many were absent:

```bash runnable kind=shell-node needs=token
export SEMURG_API_TOKEN=<your token>
export PGPASSWORD="$SEMURG_API_TOKEN"
psql -h 127.0.0.1 -p 5432 -U semurg -d semurg \
  -c "SELECT * FROM people WHERE id IN (1, 2, 3)"
```

```expect
expect: the two stored rows come back, and key 3 is simply not there
assert: out~Ada;out~Bo;out~\(2 rows\)
```

The same read over REST names each key's outcome in order: a row, or `null` for an absent key, with the count of absent keys beside it.

```bash runnable kind=shell-node needs=token
export SEMURG_API_TOKEN=<your token>
curl -sS http://localhost:4000/v1/query \
  -H "Authorization: Bearer $SEMURG_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"op": "read_rows", "db": "semurg", "table": "people", "pks": [1, 2, 3]}'
```

```expect
expect: two rows and a null for the absent key, with absent counted as 1
assert: json:.ok==true and .absent==1 and (.rows|length)==3 and .rows[2]==null
```

## An insert is all or none

A statement that would put a second row under a key that exists is refused by name, and none of its rows is written. Here the first row is new and the second repeats a key that exists, so neither is stored:

```bash runnable kind=shell-node needs=token
export SEMURG_API_TOKEN=<your token>
export PGPASSWORD="$SEMURG_API_TOKEN"
psql -h 127.0.0.1 -p 5432 -U semurg -d semurg \
  -c "INSERT INTO people VALUES (3, 'Cy', 'Rome'), (1, 'Dup', 'Nowhere')" 2>&1 || true
psql -h 127.0.0.1 -p 5432 -U semurg -d semurg \
  -c "SELECT * FROM people WHERE id = 3"
```

```expect
expect: the duplicate key is refused by name, and key 3 holds nothing afterwards
assert: out~duplicate key value violates unique constraint;out~\(0 rows\)
```

Over REST the same refusal is HTTP 409, with the reason in the answer:

```bash runnable kind=shell-node needs=token
export SEMURG_API_TOKEN=<your token>
curl -sS -w '\nHTTP %{http_code}\n' http://localhost:4000/v1/query \
  -H "Authorization: Bearer $SEMURG_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"op": "insert_rows", "db": "semurg", "table": "people",
       "rows": [[4, "Di", "Lima"], [1, "Dup", "Nowhere"]]}'
```

```expect
expect: HTTP 409 and a duplicate-key reason; row 4 is not written either
assert: out~duplicate key;out~HTTP 409
```

## Delete by key

A delete says what it did to each key: deleted, absent, or could not judge:

```bash runnable kind=shell-node needs=token
export SEMURG_API_TOKEN=<your token>
curl -sS http://localhost:4000/v1/query \
  -H "Authorization: Bearer $SEMURG_API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"op": "delete_rows", "db": "semurg", "table": "people", "pks": [2, 99]}'
```

```expect
expect: key 2 is deleted, key 99 was absent, and nothing could not be judged
assert: json:.ok==true and .deleted==1 and .absent==1 and (.could_not_judge|length)==0
```

## How it behaves

- A row is read, inserted or deleted by its primary key; a key can be a single column or several.
- An insert writes every row of a statement or none of them, and a repeated key is refused by name.
- Every read and delete answers per key with the row, absent, or could not judge; an answer never guesses.
- A statement this door does not accept is refused with a name that says which statement it was.
- The table, its rows and your graph are in one store: the same node serves the memory, vector and graph guides.

## Next

- [Semurg as a graph database](/blog/semurg-as-a-graph-database): walk edges from the same node.
- [Give your agent persistent memory with MCP in five minutes](/blog/agent-memory-mcp-five-minutes): the memory door beside this one.

*This post was written with AI assistance. Every result in it is checked against a published release or record before the post goes out.*