Blog / How-to guides

Tables with primary keys: create, insert all-or-none, read and delete by key

1 November 2026 · Semurg · 4 min read · Markdown

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 (how)

relationalsqldatabasehow-to

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.

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.Create a table with aprimary keyInsert rows: all ornoneRead by key: the row,absent, or could notjudgeDelete by key:deleted, absent, orcould 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
apt-get update -qq && apt-get install -y -qq postgresql-client && psql --version

Expected: the client installs and reports its version

Every command below reads the token from the environment, as in the other guides (the token file steps 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
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')"

Expected: the table is created and both rows are inserted

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
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)"

Expected: the two stored rows come back, and key 3 is simply not there

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
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]}'

Expected: two rows and a null for the absent key, with absent counted as 1

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
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"

Expected: the duplicate key is refused by name, and key 3 holds nothing afterwards

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

bash
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"]]}'

Expected: HTTP 409 and a duplicate-key reason; row 4 is not written either

Delete by key

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

bash
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]}'

Expected: key 2 is deleted, key 99 was absent, and nothing could not be judged

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

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

How these commands were checked

Our test farm ran each of the 7 command blocks marked runnable, exactly as written, with the answer checked against the expectation printed under it, against a fresh node of the published release r11.142.20261030.e037138. A post is published only when every one of them passed there.

Evidence

  1. Release r11.142.20261030.e037138, published on one.semurg.io (release:r11.142.20261030.e037138; the record as JSON)

Related