3. Tables

• Category: Tutorial

What you will learn

• The two kinds of tables, and when to use each.
• Column types and column rules (keys, NOT NULL, DEFAULT, REFERENCES, computed columns).
• How to change and remove tables.

The examples start with an empty database market. Run them in order.

Two kinds of tables

Simple rule: if the row has a time and you get many new rows every day, use a time-series table.

Relational tables

CREATE TABLE securities (
  symbol SYMBOL PRIMARY KEY,
  name   STRING NOT NULL,
  sector STRING
);

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

INSERT INTO securities VALUES
  ('CITY', 'City Bank Limited', 'Banking'),
  ('COAST',  'Coastal Bank Limited', 'Banking'),
  ('VOLT', 'Volt River Hydropower Limited', 'Energy');

INSERT INTO brokers VALUES (1, 'Keystone Securities'), (45, 'Imperial Securities'), (58, 'Northgate Securities');

table 'securities' created

table 'brokers' created

3 row(s) inserted

3 row(s) inserted

(To run a file like this: Get-Content tables.sql -Raw | kdb sql market -.)

Time-series tables

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

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

table 'ochl_day' created

table 'ochl_minutes' created

The table options

A picture of a time-series table

ochl_minutes with PARTITION BY MONTH and the default SORT BY (symbol, time):

Why this is good:

• A query with WHERE time IN '2026-09' reads only the 2026-09 file.
• A query for one symbol finds its rows close together.
• You can delete a whole month fast: ALTER TABLE ochl_minutes DROP PARTITION '2026-08', or everything older than a year: ALTER TABLE ochl_minutes DROP PARTITION OLDER THAN 1 YEAR.

Which PARTITION BY to use

Partitions are based on the UTC date of the time column.

DEDUP KEY: how duplicates are found

DEDUP KEY (symbol, time) means: one row per symbol per time. A second row with the same symbol and time is a duplicate.

• INSERT stops with an error on a duplicate.
• INSERT OR REPLACE overwrites the old row.
• INSERT OR IGNORE keeps the old row and skips the new one.

The DEDUP KEY must include the time column:

CREATE TABLE bad (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) DEDUP KEY (symbol);

error: DEDUP KEY must include the time column 'time', e.g. DEDUP KEY (symbol, time)

Without a DEDUP KEY, KDB keeps every row, also exact copies:

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;

table 'ticks' created

1 row(s) inserted

1 row(s) inserted

time                     | symbol | price
-------------------------+--------+------
2026-09-10T05:15:00.000Z | CITY   |   510
2026-09-10T05:15:00.000Z | CITY   |   510
(2 rows, 0.1 ms)

Tip: give every market-data time-series table a DEDUP KEY. Then you can load the same file twice without making double rows.

ROUND: clean times

ROUND 1d (or 1m, 5m, 1h, ...) on a TIMESTAMP column cuts the time down when a row is written. Units: ms, s, m (minute), h, d, w.

INSERT INTO ochl_day VALUES ('2026-09-10 14:37:12', 'CITY', 504.6, 507, 500.7, 504.9, 183040);
INSERT INTO ochl_minutes VALUES ('2026-09-10T11:05:42+05:45', 'CITY', 504.6, 504.9, 504.5, 504.8, 1200);
SELECT time, symbol, close FROM ochl_day;
SELECT time, symbol, close FROM ochl_minutes;

1 row(s) inserted

1 row(s) inserted

time                     | symbol | close
-------------------------+--------+------
2026-09-10T00:00:00.000Z | CITY   | 504.9
(1 row, 0.1 ms)

time                     | symbol | close
-------------------------+--------+------
2026-09-10T05:20:00.000Z | CITY   | 504.8
(1 row, 0.0 ms)

• 14:37:12 became 00:00:00 (the day, in UTC).
• 11:05:42 Nepal time is 05:20:42 UTC. It became 05:20:00.

Because of ROUND, a second row for the same day is a duplicate, even with another time of day:

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

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

Careful: ROUND 1d rounds to the UTC day. For daily candles stored at 00:00 UTC of the trading date, this is right. A time like 2026-09-10T02:00:00+05:45 (still 9 September in UTC) would go to 9 September.

SHOW: look at tables

SHOW TABLES;

name         | kind       | partition | columns | rows
-------------+------------+-----------+---------+-----
brokers      | relational | NONE      |       2 |    3
trade_log    | timeseries | DAY       |       8 |    0
live_data    | timeseries | DAY       |       5 |    0
ochl_day     | timeseries | YEAR      |       7 |    0
ochl_minutes | timeseries | MONTH     |       7 |    0
securities   | relational | NONE      |       3 |    3
(6 rows, 0.0 ms)

(trade_log and live_data are created in the next section. The output above was taken after that.)

SHOW COLUMNS FROM trade_log;

name           | type      | primary_key | not_null | computed | references 
---------------+-----------+-------------+----------+----------+------------
date           | TIMESTAMP | false       | false    | false    | NULL       
transaction_id | LONG      | false       | false    | false    | NULL       
symbol         | SYMBOL    | false       | false    | false    | NULL       
buyer          | INT       | false       | false    | false    | brokers(id)
seller         | INT       | false       | false    | false    | brokers(id)
quantity       | LONG      | false       | false    | false    | NULL       
rate           | DOUBLE    | false       | false    | false    | NULL       
amount         | DOUBLE    | false       | false    | true     | NULL       
(8 rows, 0.0 ms)

SHOW CREATE TABLE gives the full statement. Use CSV format, so the long text is not cut:

kdb sql market "SHOW CREATE TABLE ochl_day" --format csv

create_sql
"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 SORT BY (symbol, time) DEDUP KEY (symbol, time)"

You can see the default SORT BY (symbol, time) here.

SHOW VIEWS lists views (chapter 5).

Column types

Other names that also work: BOOLEAN = BOOL, INTEGER = INT, BIGINT = LONG, VARCHAR and TEXT = STRING.

There are more types for other kinds of data: JSON and BYTES (chapter 18), and the PostgreSQL types like SERIAL, VARCHAR(n), NUMERIC(p,s), UUID and arrays (chapter 22).

Every type can be NULL (no value). For the number types, the smallest value is used to store NULL. That is why BYTE goes to -127, not -128.

All types in one table:

CREATE TABLE type_demo (
  b   BOOL,
  i8  BYTE,
  i16 SHORT,
  i32 INT,
  i64 LONG,
  f   FLOAT,
  d   DOUBLE,
  m   DECIMAL(2),
  day DATE,
  ts  TIMESTAMP,
  sym SYMBOL,
  txt STRING
);

INSERT INTO type_demo VALUES
  (TRUE, 127, 32767, 2147483647, 9223372036854775807, 1.5, 512.45, 1500.25,
   '2026-09-10', '2026-09-10T11:00:00+05:45', 'CITY', 'City Bank Limited'),
  (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

SELECT * FROM type_demo;

table 'type_demo' created

2 row(s) inserted

b    | i8   | i16   | i32        | i64                 | f    | d      | m       | day        | ts                       | sym   | txt               
-----+------+-------+------------+---------------------+------+--------+---------+------------+--------------------------+-------+-------------------
true |  127 | 32767 | 2147483647 | 9223372036854775807 |  1.5 | 512.45 | 1500.25 | 2026-09-10 | 2026-09-10T05:15:00.000Z | CITY  | City Bank Limited
NULL | NULL |  NULL |       NULL |                NULL | NULL |   NULL |    NULL | NULL       | NULL                     | NULL  | NULL              
(2 rows, 0.1 ms)

DOUBLE or DECIMAL? DOUBLE is fast and good for prices and indicators. It is not exact: 0.1 + 0.2 is not exactly 0.3. For money that must add up exactly (accounts, fees), use DECIMAL(2).

Column rules

Use PRIMARY KEY on relational tables. On time-series tables use DEDUP KEY (a PRIMARY KEY there is refused).

Accounts: AUTO key, NOT NULL, UNIQUE, DEFAULT, DECIMAL

CREATE TABLE accounts (
  id      LONG PRIMARY KEY AUTO,
  owner   STRING NOT NULL,
  email   STRING UNIQUE,
  balance DECIMAL(2) DEFAULT 0,
  status  STRING DEFAULT 'active',
  opened  TIMESTAMP DEFAULT now()
);

INSERT INTO accounts (owner, email, balance) VALUES
  ('Ram Sharma', 'ram@example.com', 1500.25),
  ('Sita Rai', 'sita@example.com', '99.5');
INSERT INTO accounts (owner) VALUES ('Hari Thapa');

SELECT id, owner, email, balance, status FROM accounts;

table 'accounts' created

2 row(s) inserted

1 row(s) inserted

id | owner      | email            | balance | status
---+------------+------------------+---------+-------
 1 | Ram Sharma | ram@example.com  | 1500.25 | active
 2 | Sita Rai   | sita@example.com |   99.50 | active
 3 | Hari Thapa | NULL             |    0.00 | active
(3 rows, 0.1 ms)

• id was filled with 1, 2, 3.
• '99.5' (text) was turned into the decimal 99.50.
• Hari got the DEFAULT values 0.00 and active.

The rules stop bad rows:

INSERT INTO accounts (owner, balance) VALUES ('Gita', 10.555);

error: column 'balance' (DECIMAL(2)): 10.555 has more than 2 decimal places

INSERT INTO accounts (email) VALUES ('nobody@example.com');

error: column 'owner' (STRING): cannot be NULL

INSERT INTO accounts (owner, email) VALUES ('Ram Again', 'ram@example.com');

error: column 'email' must be unique: 'ram@example.com' is already used

TradeLog: REFERENCES and a computed column

CREATE TABLE trade_log (
  date           TIMESTAMP ROUND 1d,
  transaction_id LONG,
  symbol         SYMBOL,
  buyer          INT REFERENCES brokers(id),
  seller         INT REFERENCES brokers(id),
  quantity       LONG,
  rate           DOUBLE,
  amount         DOUBLE AS (quantity * rate)
) TIMESERIES(date) PARTITION BY DAY DEDUP KEY (date, transaction_id);

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

table 'trade_log' created

table 'live_data' created

Insert a trade. Do not give amount; KDB computes it:

INSERT INTO trade_log (date, transaction_id, symbol, buyer, seller, quantity, rate)
VALUES ('2026-09-10', 100001, 'CITY', 58, 45, 100, 515.5);

SELECT transaction_id, symbol, buyer, seller, quantity, rate, amount FROM trade_log;

1 row(s) inserted

transaction_id | symbol | buyer | seller | quantity | rate  | amount
---------------+--------+-------+--------+----------+-------+-------
        100001 | CITY   |    58 |     45 |      100 | 515.5 |  51550
(1 row, 0.2 ms)

A broker that does not exist is refused:

INSERT INTO trade_log (date, transaction_id, symbol, buyer, seller, quantity, rate)
VALUES ('2026-09-10', 100002, 'COAST', 99, 45, 10, 399);

error: column 'buyer' = '99' has no matching row in brokers(id)

A broker that trades are pointing at cannot be deleted:

DELETE FROM brokers WHERE id = 58;

error: cannot remove brokers(id) = '58': table 'trade_log' column 'buyer' still refers to it (foreign key 'trade_log_buyer_fkey')

Speed note: REFERENCES checks every new row. For very big imports (millions of trade_log rows) you may want to leave REFERENCES out, and check the data with a query instead.

Change a table

The word COLUMN is optional: ALTER TABLE t ADD c LONG also works.

ALTER TABLE ticks ADD COLUMN volume LONG;
ALTER TABLE ticks RENAME COLUMN price TO ltp;
INSERT INTO ticks VALUES ('2026-09-10T11:01:00+05:45', 'CITY', 511, 300);
SELECT time, symbol, ltp, volume FROM ticks;

table 'ticks' altered

table 'ticks' altered

1 row(s) inserted

time                     | symbol | ltp | volume
-------------------------+--------+-----+-------
2026-09-10T05:15:00.000Z | CITY   | 510 |   NULL
2026-09-10T05:15:00.000Z | CITY   | 510 |   NULL
2026-09-10T05:16:00.000Z | CITY   | 511 |    300
(3 rows, 0.2 ms)

Some columns cannot be dropped: the TIMESERIES column, SORT BY and DEDUP KEY columns, the PRIMARY KEY, columns that other tables REFERENCE, and columns used by a computed column.

ALTER TABLE ticks DROP COLUMN symbol;

error: cannot drop column 'symbol' from table 'ticks': it is part of SORT BY

ALTER TABLE ticks DROP COLUMN volume;
RENAME TABLE ticks TO test_ticks;
DROP TABLE test_ticks;
DROP TABLE IF EXISTS test_ticks;
SHOW TABLES;

table 'ticks' altered

table 'ticks' renamed to 'test_ticks'

table 'test_ticks' dropped; UNDROP TABLE test_ticks brings it back within 7 days

table 'test_ticks' does not exist

name         | kind       | partition | columns | rows
-------------+------------+-----------+---------+-----
accounts     | relational | NONE      |       6 |    3
brokers      | relational | NONE      |       2 |    3
trade_log    | timeseries | DAY       |       8 |    1
live_data    | timeseries | DAY       |       5 |    0
ochl_day     | timeseries | YEAR      |       7 |    1
ochl_minutes | timeseries | MONTH     |       7 |    1
securities   | relational | NONE      |       3 |    3
type_demo    | relational | NONE      |      12 |    2
(8 rows, 0.0 ms)

A table that another table REFERENCES cannot be dropped:

DROP TABLE brokers;

error: cannot drop table 'brokers': column 'buyer' of table 'trade_log' REFERENCES it; drop that column or table first

Keep only recent data

Live prices and ticks grow every day. Often you need only the last months. A TTL (time to live) drops old partitions by itself:

ALTER TABLE live_data SET TTL 90 DAYS;

table 'live_data' drops partitions older than 90 DAYS

• KDB checks once a minute. A partition goes when its last moment is older than the TTL. It never deletes single rows.
• SHOW CREATE TABLE shows the TTL. ALTER TABLE live_data SET TTL NONE turns it off.
• You can also give it when you create the table: ... TIMESERIES(timestamp) PARTITION BY DAY TTL 90 DAYS.
• To drop old data once, by hand or from a timer, use ALTER TABLE live_data DROP PARTITION OLDER THAN 90 DAYS.

What you cannot change

You cannot change the PARTITION BY or the SORT BY of a table, and you cannot change the type of a column that is the time column or part of SORT BY or DEDUP KEY. To change them, create a new table, copy the rows with INSERT ... SELECT, then drop the old table:

CREATE TABLE ochl_day_by_symbol (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY YEAR SORT BY (symbol, time);
INSERT INTO ochl_day_by_symbol SELECT time, symbol, close FROM ochl_day;

Copying a big table this way takes time and space. Choose the table design carefully before you load big data.

Names

• Table and column names: letters, digits and _. They do not care about upper or lower case.
• A name that is a reserved word (like order, from, select) must be written in double quotes: "order". Chapter 11 lists the reserved words.
• time, date, timestamp, symbol and partition are not reserved. You can use them as column names.

Try it yourself

1. Create a time-series table index_minutes for stock index values: time, index name, value. One row per index per minute. Partition by month.
2. Create a relational table sectors with name as the primary key. Add a new column sector_name to securities that must point to sectors(name).
3. Insert a stock index value at 2026-09-10T11:00:00+05:45 and again at 11:00:30 into your table from task 1, with plain INSERT. What happens?

Next: 4. Writing data