Generated IDs and Serial Counters

KalamDB accepts familiar PostgreSQL SERIAL and BIGSERIAL declarations. Choose SERIAL for a 32-bit counter or BIGSERIAL for a generated 64-bit identifier; their allocation semantics differ.

Generate a KalamDB ID

1CREATE SCHEMA IF NOT EXISTS app;2CREATE TABLE app.tasks (3  id BIGSERIAL PRIMARY KEY,4  title TEXT NOT NULL5) WITH (TYPE = 'USER');6INSERT INTO app.tasks (title) VALUES ('First task') RETURNING id;

Use the returned ID. A BIGSERIAL ID is not a consecutive PostgreSQL sequence counter. Use a separate timestamp when the application needs creation time.

Use a serial counter

1CREATE TABLE app.counters (2  id SERIAL PRIMARY KEY,3  title TEXT4) WITH (TYPE = 'USER');5INSERT INTO app.counters (title) VALUES ('First') RETURNING id;6SELECT pg_get_serial_sequence('app.counters', 'id');7SELECT setval(pg_get_serial_sequence('app.counters', 'id'), 100, true);8INSERT INTO app.counters (title) VALUES ('After reset') RETURNING id;

For a new table, this returns IDs 1 and 101. Resetting a supported counter is useful after importing explicit IDs; choose a value at least as high as the existing maximum. BIGSERIAL does not use this 32-bit serial-counter registry.

Sequence compatibility limits

The supported serial-counter helpers do not imply a general PostgreSQL sequence implementation. Standalone CREATE SEQUENCE and nextval are unsupported in the tested 0.8 server. pg_catalog.pg_sequence exists for introspection but is not evidence of a complete writable sequence catalog. Do not use PostgreSQL sequence scripts unchanged.

Tables without a primary key

A primary key is optional. KalamDB assigns a hidden _row_id to tables without one; SELECT * omits it. Prefer an explicit application ID for URLs, updates and SDK row reconciliation.

Next: Schema migrations. Reference: Table DDL.