CREATE TABLE

Make a relational or a time-series table, with partitions, sort order, a dedup key, rounded times and computed columns, or make one from a query with CREATE TABLE ... AS.

• Category: Tables
• Keywords: create table, new table, IF NOT EXISTS, TEMP TABLE, TEMPORARY, UNLOGGED, table options, relational table, time-series table, timeseries table, RECORDS, TIMESERIES, PARTITION BY, DAY, MONTH, YEAR, NONE, SYMBOL(col), SORT BY, DEDUP KEY, DEDUP UPSERT KEYS, TIMESTAMP(col), WAL, CREATE TABLE AS, CREATE TABLE ... AS SELECT, AS SELECT, WITH NO DATA, copy a table, deduplication, ROUND, round time, computed column, generated column, GENERATED ALWAYS, STORED, stored generated column, virtual column, AS, schema, design, ttl_column, row TTL, expire, EXPIRE, expires_at, per-row TTL, key expiry, redis, session table, rate limit, max_bytes, evict, durability, cache table, LRU, eviction, maxmemory, kv_memory, volatile_ttl
• See also: Column types, Constraints, ALTER, RENAME and DROP TABLE, SHOW TABLES, COLUMNS, CREATE TABLE, INSERT, Partitions, SORT BY and DEDUP KEY, Indexes

CREATE TABLE makes a new table. KDB has two kinds of tables:

Simple rule: if every row has a time and many new rows come every day, use a time-series table. You cannot change the kind, the partitions or the sort order later, so choose carefully before you load big data. The dedup key can be changed later, but that rewrites every row (DEDUP ENABLE).

Example: Daily stock candles: a time-series table

CREATE TABLE ochl_day (
  time   TIMESTAMP ROUND 1d,
  symbol SYMBOL,
  open   DOUBLE,
  high   DOUBLE,
  low    DOUBLE,
  close  DOUBLE,
  volume DOUBLE
) TIMESERIES(time) PARTITION BY YEAR DEDUP KEY (symbol, time);

Result:

table 'ochl_day' created

CREATE TABLE

Syntax:

CREATE TABLE [IF NOT EXISTS] name (
  column TYPE [PRIMARY KEY [AUTO]] [NOT NULL] [UNIQUE] [DEFAULT expr]
              [REFERENCES table(column)] [ROUND interval] [AS (expr) | GENERATED ALWAYS AS (expr) STORED]
              [[CONSTRAINT name] CHECK (condition)],
  ...
  [, [CONSTRAINT name] CHECK (condition) ...]
)
[TIMESERIES(time_column)]
[PARTITION BY DAY | MONTH | YEAR | NONE [, SYMBOL(column)]]
[SORT BY (column, ...)]
[DEDUP KEY (column, ...)]
[TTL n HOURS | DAYS | WEEKS | MONTHS | YEARS]
[RECORDS]
[WITH (ttl_column = column)]

Makes a table with at least one column. The table options after ) can come in any order, each at most once.

• Returns: table 'name' created, or table 'name' already exists with IF NOT EXISTS.
• Notes: a view and a table cannot have the same name. CREATE TABLE cannot be used inside BEGIN ... COMMIT. To make a table from a query, see CREATE TABLE ... AS.
• PostgreSQL: CREATE TEMP TABLE makes a temporary table. CREATE UNLOGGED TABLE is refused: every KDB table is written to the log.
• QuestDB: TIMESTAMP(col) means TIMESERIES(col), DEDUP UPSERT KEYS (...) means DEDUP KEY (...) with dedup_insert = 'upsert' (a plain INSERT replaces the row, as in QuestDB), and WAL / BYPASS WAL are read and ignored. col SYMBOL INDEX [CAPACITY n], and INDEX(col [CAPACITY n]) after the column list, make an index on the column. A SYMBOL's CAPACITY n, CACHE and NOCACHE are read and ignored: KDB sizes its dictionaries itself.

Example: App users: a relational table with keys and defaults

CREATE TABLE users (
  id            LONG PRIMARY KEY AUTO,
  email         STRING NOT NULL UNIQUE,
  password_hash STRING NOT NULL,
  name          STRING,
  created       TIMESTAMP NOT NULL DEFAULT now()
);
SHOW COLUMNS FROM users;

Result:

CREATE TABLE users (

table 'users' created

SHOW COLUMNS FROM users

(5 rows)

Example: An install script that can run many times

CREATE TABLE IF NOT EXISTS brokers (id INT PRIMARY KEY, name STRING NOT NULL);
CREATE TABLE IF NOT EXISTS brokers (id INT PRIMARY KEY, name STRING NOT NULL);

Result:

CREATE TABLE IF NOT EXISTS brokers (

table 'brokers' created

CREATE TABLE IF NOT EXISTS brokers (

table 'brokers' already exists

Example: The same name twice without IF NOT EXISTS (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);

CREATE TABLE brokers (id INT PRIMARY KEY);

Result:

Error: table 'brokers' already exists; expected a new table name, or CREATE TABLE IF NOT EXISTS

Temporary tables: CREATE TEMP TABLE

Syntax:

CREATE [GLOBAL | LOCAL] TEMP | TEMPORARY TABLE name (...) [table options]
CREATE TEMP TABLE name AS query

A temporary table belongs to one session, as in PostgreSQL:

• Role: ddl, like CREATE TABLE.
• Notes: ON COMMIT DROP and ON COMMIT DELETE ROWS are not supported; the table lives until the session ends.

Example: A scratch table for one request: made, used, gone

CREATE TEMP TABLE scratch (id INT, score DOUBLE);
INSERT INTO scratch VALUES (1, 0.5), (2, 0.9);
SELECT count(*) AS rows_now, max(score) AS best FROM scratch;

Result:

CREATE TABLE pg_temp_4611686018427387904_scratch (

table 'pg_temp_4611686018427387904_scratch' created

INSERT INTO pg_temp_4611686018427387904_scratch VALUES (1, 0.5), (2, 0.9)

2 row(s) inserted

SELECT count(*) AS rows_now, max(score) AS best FROM pg_temp_4611686018427387904_scratch

(1 row)

Example: After the request, the name is gone (shows an error)

Setup:

CREATE TEMP TABLE scratch2 (id INT);

SELECT * FROM scratch2;

Result:

Error: line 1, column 15: table or view 'scratch2' does not exist

Example: UNLOGGED is refused (shows an error)

CREATE UNLOGGED TABLE fast_scratch (id INT);

Result:

Error: line 1, column 8: CREATE UNLOGGED TABLE is not supported: every KDB table is written to the log and kept. Use CREATE TABLE

CREATE TABLE ... AS

Syntax:

CREATE TABLE [IF NOT EXISTS] name [(column, ...)] AS query
    [TIMESERIES(time_column)] [PARTITION BY DAY | MONTH | YEAR | NONE]
    [SORT BY (column, ...)] [DEDUP KEY (column, ...)] [RECORDS]
CREATE TABLE name AS query WITH NO DATA

Makes a new table from the result of a query, and copies the rows in. The columns get the names and types of the query's columns. The query may be in brackets: AS (SELECT ...).

• Returns: table 'name' created with N row(s).
• Notes:
  • A SYMBOL column of the query becomes STRING. Keys, NOT NULL, defaults, CHECK rules and indexes are not copied. Add them with ALTER TABLE afterwards, or make the table first and INSERT INTO t SELECT ... (see INSERT ... SELECT).
  • PARTITION BY ..., SYMBOL(col) is not accepted here.
  • Two query columns with the same name are refused: name them with AS.

Example: Copy one symbol's daily candles into a time-series table (sample data: sample)

CREATE TABLE city_day AS
  SELECT time, close, volume FROM ochl_day WHERE symbol = 'CITY'
  TIMESERIES(time) PARTITION BY YEAR DEDUP KEY (time);
SHOW CREATE TABLE city_day;
SELECT count(*) AS rows FROM city_day;

Result:

CREATE TABLE city_day AS SELECT time, close, volume FROM ochl_day WHERE symbol = 'CITY'

table 'city_day' created with 19 row(s)

SHOW CREATE TABLE city_day

(1 row)

SELECT count(*) AS rows FROM city_day

(1 row)

Example: A summary table with new column names

Setup:

CREATE TABLE trades (symbol SYMBOL, qty LONG, rate DOUBLE);
INSERT INTO trades VALUES ('CITY', 10, 515.0), ('CITY', 20, 516.0), ('COAST', 5, 376.0);

CREATE TABLE turnover (symbol, amount) AS SELECT symbol, sum(qty * rate) FROM trades GROUP BY symbol;
SELECT * FROM turnover ORDER BY symbol;

Result:

CREATE TABLE turnover (symbol, amount) AS SELECT symbol, sum(qty * rate) FROM trades GROUP BY symbol

table 'turnover' created with 2 row(s)

SELECT * FROM turnover ORDER BY symbol

(2 rows)

Example: The same column name twice (shows an error)

Setup:

CREATE TABLE trades (symbol SYMBOL, qty LONG);

CREATE TABLE bad AS SELECT symbol, qty, qty FROM trades;

Result:

Error: the query of CREATE TABLE bad gives two columns named 'qty'; name them with AS

Relational tables (RECORDS)

Syntax:

CREATE TABLE name (column TYPE [rules], ...) [RECORDS]

A table without TIMESERIES is a relational table. The word RECORDS says it explicitly; it changes nothing. Use relational tables for lists of things that you look up by a key and change often: users, accounts, the ledger, brokers, forum posts, news articles.

• Give it a PRIMARY KEY. Rows are kept in key order, so a lookup by key is fast.
• Without a key, every INSERT adds rows, also exact copies.
• For money, add rules like CHECK (balance >= 0); see CHECK.
• Notes: PARTITION BY, SORT BY and DEDUP KEY are only for time-series tables. RECORDS together with TIMESERIES is an error.

Example: Accounting: accounts and a ledger with exact money

CREATE TABLE accounts (
  id      INT PRIMARY KEY AUTO,
  owner   STRING NOT NULL,
  balance DECIMAL(2) NOT NULL DEFAULT 0
) RECORDS;

CREATE TABLE ledger (
  id           LONG PRIMARY KEY AUTO,
  from_account INT NOT NULL REFERENCES accounts(id),
  to_account   INT NOT NULL REFERENCES accounts(id),
  amount       DECIMAL(2) NOT NULL,
  created      TIMESTAMP DEFAULT now()
);
SHOW TABLES;

Result:

CREATE TABLE accounts (

table 'accounts' created

CREATE TABLE ledger (

table 'ledger' created

SHOW TABLES

(2 rows)

Example: Forum posts and news articles

CREATE TABLE posts (
  id         LONG PRIMARY KEY AUTO,
  thread_id  LONG NOT NULL,
  parent_id  LONG REFERENCES posts(id),
  author     STRING NOT NULL,
  body       STRING NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE news (
  id           LONG PRIMARY KEY AUTO,
  symbol       STRING,
  published_at TIMESTAMP NOT NULL,
  lang         STRING NOT NULL DEFAULT 'en',
  title        STRING NOT NULL,
  body         STRING NOT NULL,
  source_url   STRING
);
SHOW TABLES;

Result:

CREATE TABLE posts (

table 'posts' created

CREATE TABLE news (

table 'news' created

SHOW TABLES

(2 rows)

Example: PARTITION BY on a relational table (shows an error)

CREATE TABLE users (id LONG PRIMARY KEY, email STRING) PARTITION BY MONTH;

Result:

Error: line 1, column 56: PARTITION BY is only for time-series tables; expected TIMESERIES(<TIMESTAMP or DATE column>) on table 'users'

TIMESERIES

Syntax:

CREATE TABLE name (...) TIMESERIES(time_column) [PARTITION BY ...] [SORT BY (...)] [DEDUP KEY (...)]

Makes a time-series table. time_column is its time column: a stored TIMESTAMP or DATE column. Time-series tables are made for many rows with a time: they are split into files by time (PARTITION BY), and they work with SAMPLE BY, LATEST ON, ASOF JOIN and WHERE time IN '2026-09'.

• Every row needs a value in the time column.
• A time-series table cannot have a PRIMARY KEY. Use DEDUP KEY.
• Notes: partitions use the UTC date of the time column.

Example: Minute candles and live prices

CREATE TABLE ochl_minutes (
  time TIMESTAMP ROUND 1m, symbol SYMBOL,
  open DOUBLE, high DOUBLE, low DOUBLE, close DOUBLE, volume DOUBLE
) TIMESERIES(time) PARTITION BY MONTH DEDUP KEY (symbol, time);

CREATE TABLE live_data (
  timestamp TIMESTAMP, symbol SYMBOL, ltp DOUBLE, pct_change DOUBLE, volume LONG
) TIMESERIES(timestamp) PARTITION BY DAY DEDUP KEY (timestamp, symbol);
SHOW TABLES;

Result:

CREATE TABLE ochl_minutes (

table 'ochl_minutes' created

CREATE TABLE live_data (

table 'live_data' created

SHOW TABLES

(2 rows)

Example: A DATE time column: one row per company per trading day

CREATE TABLE market_cap (day DATE, symbol SYMBOL, cap DECIMAL(2)) TIMESERIES(day) PARTITION BY YEAR DEDUP KEY (symbol, day);
INSERT INTO market_cap VALUES ('2026-09-10', 'CITY', 136637213200.00);
SELECT day, symbol, cap FROM market_cap;

Result:

CREATE TABLE market_cap (

table 'market_cap' created

INSERT INTO market_cap VALUES ('2026-09-10', 'CITY', 136637213200.00)

1 row(s) inserted

SELECT day, symbol, cap FROM market_cap

(1 row)

Example: The time column must be a TIMESTAMP or DATE (shows an error)

CREATE TABLE trades (id LONG, price DOUBLE) TIMESERIES(id);

Result:

Error: line 1, column 56: TIMESERIES column 'id' is LONG; expected a TIMESTAMP or DATE column

Example: A time-series table with a PRIMARY KEY (shows an error)

CREATE TABLE trades (time TIMESTAMP, id LONG PRIMARY KEY, price DOUBLE) TIMESERIES(time);

Result:

Error: time-series tables cannot have a PRIMARY KEY ('id'); use DEDUP KEY (..., time) instead, or make it a relational table

PARTITION BY

Syntax:

PARTITION BY DAY | MONTH | YEAR | NONE

A time-series table keeps its rows in one file (a partition) per day, month or year of the time column. The default is DAY.

Add , SYMBOL(column) after YEAR, MONTH or NONE to split each partition again, one file per symbol: PARTITION BY YEAR, SYMBOL(symbol). The rules are in PARTITION BY ..., SYMBOL(col).

Why partitions help:

• WHERE time IN '2026-09' reads only the files of September 2026.

• ALTER TABLE t DROP PARTITION '2025' deletes a whole year at once (DROP PARTITION).

• Notes: the choices are DAY, MONTH, YEAR and NONE; there are no weekly partitions, and PARTITION BY WEEK is an error. The partition is the UTC date. 2026-09-10T02:00:00+05:45 is 2026-09-09 20:15 UTC, so it goes into the partition 2026-09-09. Too small partitions (DAY for a table with 50 rows a day) make many tiny files; too big ones (NONE for millions of rows) make writes slower. See partitions and dedup.

Example: Rows go to monthly partitions; drop one month

Setup:

CREATE TABLE ochl_minutes (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY MONTH;
INSERT INTO ochl_minutes VALUES
  ('2026-08-30T11:00:00+05:45', 'CITY', 508.1),
  ('2026-09-10T11:00:00+05:45', 'CITY', 504.6);

ALTER TABLE ochl_minutes DROP PARTITION '2026-08';
SELECT time, symbol, close FROM ochl_minutes;

Result:

ALTER TABLE ochl_minutes DROP PARTITION '2026-08'

1 partition(s) of 'ochl_minutes' dropped

SELECT time, symbol, close FROM ochl_minutes

(1 row)

SORT BY

Syntax:

SORT BY (column, ...)

The order of the rows inside each partition file. Rows with the same first columns are stored close together, so a query for one symbol reads less.

• Default: the first SYMBOL column, then the time column: SORT BY (symbol, time). Without a SYMBOL column: the time column only.
• Only stored columns (not computed ones), each once.
• Notes: you cannot change SORT BY later. Columns in SORT BY cannot be dropped. SHOW CREATE TABLE shows the sort order KDB uses.

Example: The default sort order is shown by SHOW CREATE TABLE

CREATE TABLE trade_log (date TIMESTAMP, transaction_id LONG, symbol SYMBOL, quantity LONG, rate DOUBLE) TIMESERIES(date);
SHOW CREATE TABLE trade_log;

Result:

CREATE TABLE trade_log (

table 'trade_log' created

SHOW CREATE TABLE trade_log

(1 row)

Example: Sort the trades of a day by transaction number

CREATE TABLE trade_log (date TIMESTAMP ROUND 1d, transaction_id LONG, symbol SYMBOL, quantity LONG, rate DOUBLE)
  TIMESERIES(date) PARTITION BY DAY SORT BY (date, transaction_id) DEDUP KEY (date, transaction_id);
SHOW CREATE TABLE trade_log;

Result:

CREATE TABLE trade_log (

table 'trade_log' created

SHOW CREATE TABLE trade_log

(1 row)

DEDUP KEY

Syntax:

DEDUP KEY (column, ..., time_column)

The columns that make two rows "the same row". It is the key of a time-series table, and it must include the time column.

• Without a DEDUP KEY, KDB keeps every row, also exact copies.
• NULL counts as a value here: two rows with the same time and symbol NULL are the same row.
• Tip: give every time-series table a DEDUP KEY. Then an import file can be loaded twice without double rows.
• Notes: QuestDB's DEDUP UPSERT KEYS(...) is read as DEDUP KEY (...) and, as in QuestDB, a plain INSERT of a key that is there then replaces the row (dedup_insert). With KDB's own DEDUP KEY, duplicates give an error unless you write INSERT OR REPLACE, the table has dedup_insert = 'upsert', or the database uses the QuestDB dialect. See INSERT.
• Notes: a load over the line protocol does less work when every SORT BY column is in the DEDUP KEY (how loads find duplicates).

Example: One candle per symbol per day

Setup:

CREATE TABLE ochl_day (time TIMESTAMP ROUND 1d, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY YEAR DEDUP KEY (symbol, time);
INSERT INTO ochl_day VALUES ('2026-09-10', 'CITY', 504.9);

INSERT OR REPLACE INTO ochl_day VALUES ('2026-09-10', 'CITY', 505.2);
SELECT time, symbol, close FROM ochl_day;

Result:

INSERT OR REPLACE INTO ochl_day VALUES ('2026-09-10', 'CITY', 505.2)

0 row(s) inserted, 1 replaced

SELECT time, symbol, close FROM ochl_day

(1 row)

Example: Without a DEDUP KEY, the same tick is stored twice

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time) PARTITION BY DAY;
INSERT INTO ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510);
INSERT INTO ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510);
SELECT time, symbol, price FROM ticks;

Result:

CREATE TABLE ticks (

table 'ticks' created

INSERT INTO ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510)

1 row(s) inserted

INSERT INTO ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510)

1 row(s) inserted

SELECT time, symbol, price FROM ticks

(2 rows)

Example: A DEDUP KEY without the time column (shows an error)

CREATE TABLE live_data (timestamp TIMESTAMP, symbol SYMBOL, ltp DOUBLE) TIMESERIES(timestamp) DEDUP KEY (symbol);

Result:

Error: DEDUP KEY must include the time column 'timestamp', e.g. DEDUP KEY (symbol, timestamp), or a column GENERATED ALWAYS AS (date_trunc('minute', timestamp)) STORED

ROUND

Syntax:

column TIMESTAMP ROUND interval
interval: number + ms | s | m | h | d | w      (1m, 5m, 15m, 1h, 1d)

Cuts every time down to the interval when a row is written. ROUND 1m turns 11:05:42 into 11:05:00. Use it for candles, so that times are clean and the DEDUP KEY finds the same minute or day.

• NULL: a NULL time stays NULL.
• Notes: only for TIMESTAMP columns. ROUND 1d rounds to the UTC day: 2026-09-10T02:00:00+05:45 becomes 2026-09-09T00:00:00Z. For weeks that start on Sunday, use ROUND 1d and SAMPLE BY 1w in queries instead of ROUND 1w.

Example: Minute candles: 11:05:42 Nepal time is stored as 05:20:00 UTC

CREATE TABLE ochl_minutes (time TIMESTAMP ROUND 1m, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY MONTH DEDUP KEY (symbol, time);
INSERT INTO ochl_minutes VALUES ('2026-09-10T11:05:42+05:45', 'CITY', 504.8);
SELECT time, symbol, close FROM ochl_minutes;

Result:

CREATE TABLE ochl_minutes (

table 'ochl_minutes' created

INSERT INTO ochl_minutes VALUES ('2026-09-10T11:05:42+05:45', 'CITY', 504.8)

1 row(s) inserted

SELECT time, symbol, close FROM ochl_minutes

(1 row)

Example: With ROUND 1d a second row of the same day is a duplicate (shows an error)

Setup:

CREATE TABLE ochl_day (time TIMESTAMP ROUND 1d, symbol SYMBOL, close DOUBLE) TIMESERIES(time) DEDUP KEY (symbol, time);
INSERT INTO ochl_day VALUES ('2026-09-10 14:37:12', 'CITY', 504.9);

INSERT INTO ochl_day VALUES ('2026-09-10 09:00', 'CITY', 1);

Result:

Error: a row with symbol='CITY', time='2026-09-10T00:00:00.000Z' already exists in 'ochl_day'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it

Example: 15-minute buckets

CREATE TABLE index_15m (time TIMESTAMP ROUND 15m, index_name SYMBOL, value DOUBLE) TIMESERIES(time) DEDUP KEY (index_name, time);
INSERT OR REPLACE INTO index_15m VALUES
  ('2026-09-10T11:14:59+05:45', 'INDEX', 2710.5),
  ('2026-09-10T11:15:00+05:45', 'INDEX', 2712.0);
SELECT time, index_name, value FROM index_15m;

Result:

CREATE TABLE index_15m (

table 'index_15m' created

INSERT OR REPLACE INTO index_15m VALUES ('2026-09-10T11:14:59+05:45', 'INDEX', 2710.5), ('2026-09-10

2 row(s) inserted

SELECT time, index_name, value FROM index_15m

(2 rows)

Example: ROUND on a column that is not a TIMESTAMP (shows an error)

CREATE TABLE t (day DATE ROUND 1d, v DOUBLE);

Result:

Error: line 1, column 26: ROUND needs a TIMESTAMP column; expected TIMESTAMP but column 'day' is DATE

Computed columns: AS (expr)

Syntax:

column TYPE AS (expression)

A computed column is not stored. KDB works it out from the other columns of the same row each time you read it.

• The expression uses stored columns of the same row, operators and built-in functions.
• You cannot give it a value in INSERT, and UPDATE cannot set it. Change the columns it is made from.
• INSERT INTO t VALUES (...) without a column list skips computed columns.
• To keep the value on disk (for an index, UNIQUE or NOT NULL), use a stored generated column.
• Notes: a computed column cannot also have PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, REFERENCES or ROUND, and cannot be in SORT BY, DEDUP KEY, a CHECK or an index. No subqueries, window functions or other computed columns inside. Do not use random functions (gen_random_uuid()) or now() in it: the value changes on every read.

Example: Trade log amount and broker commission

CREATE TABLE trade_log (
  date TIMESTAMP ROUND 1d, transaction_id LONG, symbol SYMBOL,
  quantity LONG, rate DOUBLE,
  amount DOUBLE AS (quantity * rate),
  commission DOUBLE AS (round(quantity * rate * 0.004, 2))
) TIMESERIES(date) PARTITION BY DAY DEDUP KEY (date, transaction_id);
INSERT INTO trade_log VALUES ('2026-09-10', 100010, 'CITY', 50, 517.0);
SELECT transaction_id, quantity, rate, amount, commission FROM trade_log;

Result:

CREATE TABLE trade_log (

table 'trade_log' created

INSERT INTO trade_log VALUES ('2026-09-10', 100010, 'CITY', 50, 517.0)

1 row(s) inserted

SELECT transaction_id, quantity, rate, amount, commission FROM trade_log

(1 row)

Example: A computed column cannot be given a value (shows an error)

Setup:

CREATE TABLE trade_log (quantity LONG, rate DOUBLE, amount DOUBLE AS (quantity * rate));

INSERT INTO trade_log (quantity, rate, amount) VALUES (100, 515.5, 51550);

Result:

Error: column 'amount' is computed (AS ...) and cannot be given a value

Example: A computed column that uses a missing column (shows an error)

CREATE TABLE trade_log (quantity LONG, amount DOUBLE AS (quantity * price));

Result:

Error: column 'amount' of table 'trade_log' uses column 'price' in AS (...), which does not exist in table 'trade_log'

Stored generated columns: GENERATED ALWAYS AS (expr) STORED

Syntax:

column TYPE GENERATED ALWAYS AS (expression) STORED
column TYPE GENERATED ALWAYS AS (expression) [VIRTUAL]

The PostgreSQL form. With STORED, KDB works the value out when the row is written and keeps it like any other column. Without STORED (or with VIRTUAL) it is a computed column, worked out when read.

• Every write works it out again: INSERT, UPDATE of a column it reads, ON CONFLICT DO UPDATE, a foreign key ON UPDATE CASCADE, and API and line-protocol inserts.
• You cannot give it a value in INSERT or UPDATE, as in PostgreSQL. INSERT INTO t VALUES (...) without a column list skips it; COPY without a column list too.
• ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (...) STORED fills it in for the rows already in the table (the rows are written again).
• Notes: it cannot be the PRIMARY KEY, a time-series table's time column, or in SORT BY, and cannot have DEFAULT, AUTO or ROUND. It can be in a DEDUP KEY: date_trunc('minute', time) there makes seconds of one minute the same row (a key on the minute). The expression uses stored columns that are not generated themselves, and built-in functions only. A column it uses cannot be dropped first.
• Tables made before 2026-09-24 with STORED keep a computed column (worked out when read). To store it, drop the column and add it again.

Example: An order total you can index and search

CREATE TABLE order_lines (
  id INT PRIMARY KEY, qty INT, price DECIMAL(2),
  total DECIMAL(2) GENERATED ALWAYS AS (qty * price) STORED
);
CREATE INDEX order_lines_total ON order_lines (total);
INSERT INTO order_lines (id, qty, price) VALUES (1, 2, 1.50), (2, 4, 2.00);
UPDATE order_lines SET qty = 3 WHERE id = 1;
SELECT id, qty, price, total FROM order_lines WHERE total > 4 ORDER BY id;

Result:

CREATE TABLE order_lines (

table 'order_lines' created

CREATE INDEX order_lines_total ON order_lines (total)

index 'order_lines_total' created on table 'order_lines' (0 rows)

INSERT INTO order_lines (id, qty, price) VALUES (1, 2, 1.50), (2, 4, 2.00)

2 row(s) inserted

UPDATE order_lines SET qty = 3 WHERE id = 1

1 row(s) updated

SELECT id, qty, price, total FROM order_lines WHERE total > 4 ORDER BY id

(2 rows)

Example: A stored generated column cannot be given a value (shows an error)

Setup:

CREATE TABLE order_lines (id INT PRIMARY KEY, qty INT, price DECIMAL(2), total DECIMAL(2) GENERATED ALWAYS AS (qty * price) STORED);

UPDATE order_lines SET total = 10;

Result:

Error: column 'total' is GENERATED ALWAYS AS (...) STORED and cannot be set; KDB works it out from the row

Rows that expire: WITH (ttl_column = ...)

Syntax:

CREATE TABLE name (..., expires_at TIMESTAMP, ...) WITH (ttl_column = expires_at)

Each row can have its own end of life, like a Redis key with EXPIRE. When the time in expires_at has come, the row is gone at once for every statement, and a background thread deletes it within about a second. Use it for login sessions, rate-limit counters, caches and short-lived tokens.

• An INSERT of an expired row's key is a new row, not an "already exists" error. INSERT ... ON CONFLICT DO UPDATE starts a fresh row, so a counter that expires counts from 1 again.
• To keep a row alive longer, UPDATE its expires_at.
• The deletes are ordinary deletes: they are in the log, and DELETE triggers see the rows.
• Notes: the column must be a stored TIMESTAMP. It cannot be dropped while the table has the option, and the option is set by CREATE TABLE only (ALTER TABLE ... SET (ttl_column = ...) is refused for now). A database with such a table needs KDB 2026-09-25 or later (versions).
• Speed: a read of such a table checks the time on each row and never comes from the result cache. The table's TTL n DAYS (whole partitions) is a different, older option: SET TTL.

Example: Sessions that expire, and a counter that starts again

CREATE TABLE sessions (
  token      STRING PRIMARY KEY,
  user_id    LONG,
  hits       LONG,
  expires_at TIMESTAMP
) WITH (ttl_column = expires_at);
INSERT INTO sessions VALUES ('old', 1, 5, '2020-01-01T00:00:00Z'), ('new', 2, 1, '2099-01-01T00:00:00Z'), ('api', 3, 0, NULL);
SELECT token, user_id FROM sessions ORDER BY token;
INSERT INTO sessions VALUES ('old', 1, 1, '2099-01-01T00:00:00Z')
  ON CONFLICT (token) DO UPDATE SET hits = sessions.hits + 1 RETURNING hits;

Result:

CREATE TABLE sessions (

table 'sessions' created

INSERT INTO sessions VALUES ('old', 1, 5, '2020-01-01T00:00:00Z'), ('new', 2, 1, '2099-01-01T00:00:0

3 row(s) inserted

SELECT token, user_id FROM sessions ORDER BY token

(2 rows)

INSERT INTO sessions VALUES ('old', 1, 1, '2099-01-01T00:00:00Z') ON CONFLICT (token) DO UPDATE SET

(1 row)

1 row(s) inserted

Cache tables: WITH (max_bytes, evict, durability)

Syntax:

CREATE TABLE name (key ... PRIMARY KEY, ...)
  WITH (max_bytes = '64MB' [, evict = 'lru'] [, durability = 'none'])
ALTER TABLE name SET (max_bytes = '128MB')
ALTER TABLE name RESET (max_bytes)

A cache table has a memory cap, like Redis with maxmemory. When its rows take more than max_bytes, a background thread deletes rows until it fits again (within about a second), in the order evict says:

• SELECT kv_memory('name') answers the bytes the table holds, as last measured. A row counts the bytes of its values (text by its length, numbers and times 8 bytes) plus 48 bytes.
• A row is "used" when a statement reads it row by row (a look-up by key, SELECT ... WHERE) or writes it. Aggregates over many rows (count(*), sum) do not count as a use.
• Needs: a PRIMARY KEY for any evict; a ttl_column for the volatile_ ones. The evicting deletes are ordinary deletes: they are in the log and DELETE triggers see them.
• The key-value store has the same cap: CONFIG SET maxmemory 64mb (kv).
• A database with a cache table needs KDB 2026-10-02 or later (versions).

Example: A cache of 1 MB that keeps the rows in use

CREATE TABLE page_cache (url STRING PRIMARY KEY, html STRING)
  WITH (max_bytes = '1MB', evict = 'lru');
INSERT INTO page_cache VALUES ('/', '<h1>home</h1>'), ('/about', '<h1>about</h1>');
SELECT html FROM page_cache WHERE url = '/';
ALTER TABLE page_cache SET (max_bytes = '4MB', durability = 'none');

Result:

CREATE TABLE page_cache (

table 'page_cache' created

INSERT INTO page_cache VALUES ('/', '<h1>home</h1>'), ('/about', '<h1>about</h1>')

2 row(s) inserted

SELECT html FROM page_cache WHERE url = '/'

(1 row)

ALTER TABLE page_cache SET (max_bytes = 4194304, durability = 'none')

table 'page_cache': max_bytes = 4194304, evict = 'lru', durability = 'none' (emptied every time the database opens)