11. SQL reference

• Category: Tutorial

What you will learn

• The exact form of every KDB statement.
• Every type, function and operator, with a short example.
• The time rules, reserved words and limits.

This is a short reference. Chapters 3 to 5 explain everything with longer examples. In the syntax, [x] means "x is optional", a | b means "a or b", and ... means "more of the same".

The example results in this chapter are real. They come from examples/11-sql-reference/.

General rules

Statements

Tables

CREATE TABLE [IF NOT EXISTS] name (
  column TYPE [PRIMARY KEY [AUTO]] [NOT NULL] [[CONSTRAINT name] UNIQUE] [DEFAULT expr]
              [[CONSTRAINT name] REFERENCES table[(column)] [ON DELETE action] [ON UPDATE action]]
              [ROUND interval] [AS (expr)] [[CONSTRAINT name] CHECK (condition)],
  ...
  [, [CONSTRAINT name] CHECK (condition) ...]
  [, [CONSTRAINT name] PRIMARY KEY (column, ...)]
  [, [CONSTRAINT name] UNIQUE (column, ...)]
  [, [CONSTRAINT name] FOREIGN KEY (column, ...) REFERENCES table (column, ...) [ON DELETE action] [ON UPDATE action]]
)
[TIMESERIES(time_column)]
[PARTITION BY DAY | MONTH | YEAR | NONE]
[SORT BY (column, ...)]
[DEDUP KEY (column, ...)]
[TTL n HOURS | DAYS | WEEKS | MONTHS | YEARS]
[RECORDS];

DROP TABLE [IF EXISTS] name;
TRUNCATE [TABLE] name [, name ...];      -- removes every row at once; not inside BEGIN ... COMMIT
RENAME TABLE old TO new;
ALTER TABLE name ADD [COLUMN] column TYPE [rules];
ALTER TABLE name DROP [COLUMN] column;
ALTER TABLE name RENAME [COLUMN] old TO new;
ALTER TABLE name DROP PARTITION 'key' [, 'key' ...];
ALTER TABLE name DROP PARTITION OLDER THAN n DAYS | MONTHS | YEARS | ...;
ALTER TABLE name SET TTL n DAYS | ... | NONE;
ALTER TABLE name DEDUP ENABLE UPSERT KEYS (column, ...);   -- or DEDUP DISABLE; rewrites the rows
ALTER TABLE name ADD [CONSTRAINT cname] CHECK (condition);
ALTER TABLE name DROP CONSTRAINT cname;
-- PostgreSQL forms (chapter 22):
CREATE TABLE name AS SELECT ...;
ALTER TABLE [IF EXISTS] name action [, action ...];
  -- ADD [COLUMN] [IF NOT EXISTS] ..., DROP [COLUMN] [IF EXISTS] c [CASCADE],
  -- ALTER [COLUMN] c {SET DEFAULT expr | DROP DEFAULT | SET NOT NULL | DROP NOT NULL | TYPE type [USING expr]},
  -- ADD [CONSTRAINT n] {UNIQUE (...) | PRIMARY KEY (...) | FOREIGN KEY (...) REFERENCES ... | CHECK (...)} [NOT VALID],
  -- DROP CONSTRAINT [IF EXISTS] n, RENAME [COLUMN] a TO b, RENAME TO new

SHOW TABLES;
SHOW COLUMNS FROM name;
SHOW CREATE TABLE name;
SHOW VIEWS;

Writing

[WITH name AS (query), ...]
INSERT [OR REPLACE | OR IGNORE] INTO table [AS alias] [(column, ...)]
VALUES (value, ...) [, (value, ...) ...] | SELECT ... | DEFAULT VALUES
[ON CONFLICT [(column, ...) [WHERE ...] | ON CONSTRAINT name]
   DO NOTHING | DO UPDATE SET column = expr, ... [WHERE condition]]      -- chapter 23
[RETURNING expr [[AS] alias], ... | *];

UPDATE table [[AS] alias] SET column = expr [, column = expr ...] [FROM source, ...] [WHERE condition] [RETURNING ...];

DELETE FROM table [[AS] alias] [USING source, ...] [WHERE condition] [RETURNING ...];

• RETURNING gives rows like a SELECT: the inserted rows (with AUTO ids and defaults), the rows after an UPDATE, the deleted rows.

• Values are constants or expressions without columns (now(), 1 + 1, CAST(...)), and subqueries.

• ON CONFLICT, UPDATE ... FROM, DELETE ... USING, WITH x AS (UPDATE ... RETURNING ...) and subqueries (also correlated ones) in SET and WHERE: chapter 23.

• There is no MERGE. To empty a table, use TRUNCATE (see Tables).

Queries

[WITH [RECURSIVE] name [(column, ...)] AS (query), ...]
SELECT [DISTINCT | DISTINCT ON (expr, ...)] expr [[AS] alias], ... | * | table.*
[FROM table | view | (SELECT ...) [[AS] alias [(column, ...)]] | (VALUES (...), ...) AS alias (column, ...)
   | unnest(array) [AS alias (column)] | [LEFT JOIN] LATERAL (SELECT ...) alias
   [[INNER] JOIN | LEFT [OUTER] JOIN  source ON condition]
   [ASOF JOIN source [ON condition]]
   [CROSS JOIN source | , source] ...]
[WHERE condition]
[LATEST ON time_column PARTITION BY column, ...]
[SAMPLE BY interval [FILL(NONE | NULL | PREV | LINEAR | constant)]
                    [ALIGN TO CALENDAR | ALIGN TO FIRST OBSERVATION]
                    [TIME ZONE 'zone'] [WITH OFFSET '+HH:MM']
                    [WEEK START SUNDAY | MONDAY]]
[GROUP BY expr, ...]
[HAVING condition]
[UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]  SELECT ... ...]
[ORDER BY expr [ASC | DESC] [NULLS FIRST | NULLS LAST], ...]
[LIMIT n | ALL] [OFFSET m [ROWS]] [FETCH FIRST n ROWS ONLY]
[FOR UPDATE];                            -- locks the tables it reads until the transaction ends (chapter 15)

EXPLAIN SELECT ...;

Views

CREATE [OR REPLACE] [CACHED] VIEW [IF NOT EXISTS] name AS [(] SELECT ... [)] [REFRESH ON COMMIT];
ALTER VIEW name AS SELECT ...;
REFRESH VIEW name;
DROP VIEW [IF EXISTS] name;
CREATE MATERIALIZED VIEW [IF NOT EXISTS] name [PARTITION BY DAY | MONTH | YEAR] [TTL n DAYS]
  AS SELECT time_bucket, ..., aggregates FROM table GROUP BY time_bucket, ...;
REFRESH MATERIALIZED VIEW name [FOR 'time' [TO 'time']];
DROP MATERIALIZED VIEW [IF EXISTS] name;
SHOW VIEWS;

REFRESH ON COMMIT needs CACHED. A view and a table cannot have the same name.

Indexes and full-text search

Chapter 17 explains them with a forum and a news example.

CREATE [UNIQUE | FULLTEXT] INDEX [IF NOT EXISTS] name ON table (column, ...);
CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] [name] ON [ONLY] table [USING method]
  ({column | (expression)} [DESC] [NULLS LAST], ...) [WHERE condition];   -- PostgreSQL form, chapter 22
DROP INDEX [IF EXISTS] name;
SHOW INDEXES [FROM table];   -- columns: name, table, kind, columns, built, rows, memory_bytes, create_sql

Sequences

Chapter 22 explains them.

CREATE SEQUENCE [IF NOT EXISTS] name [INCREMENT [BY] n] [MINVALUE n] [MAXVALUE n] [START [WITH] n] [[NO] CYCLE];
ALTER SEQUENCE name [RESTART [WITH n]] [INCREMENT [BY] n] ...;
DROP SEQUENCE [IF EXISTS] name [, ...];
SELECT nextval('name'), currval('name'), setval('name', n [, is_called]), lastval();

A number is never given twice, also after a crash. currval and lastval are per session.

PostgreSQL schema statements

CREATE SCHEMA, CREATE EXTENSION (pgcrypto, uuid-ossp, citext, plpgsql, pg_trgm, btree_gin, btree_gist, unaccent) and COMMENT ON are accepted and change nothing. DO $$ BEGIN ... END $$ runs IF, RAISE and SQL statements. The views information_schema.tables, .columns, .table_constraints, .key_column_usage, ... and pg_class, pg_attribute, pg_indexes, pg_constraint, ... describe the database. Chapter 22.

Functions, procedures and triggers

Chapter 13 explains them with examples.

CREATE [OR REPLACE] FUNCTION name(param TYPE, ...) [RETURNS TYPE] AS (expression);
DROP FUNCTION [IF EXISTS] name;
SHOW FUNCTIONS;                         -- columns: name, arguments, returns, definition

CREATE [OR REPLACE] PROCEDURE name(param TYPE, ...) AS BEGIN
  statement; ...
END;
CALL name(argument, ...) [WITH DEBUG];
DROP PROCEDURE [IF EXISTS] name;
SHOW PROCEDURES;                        -- columns: name, arguments, definition

CREATE [OR REPLACE] TRIGGER name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table
  EXECUTE {CALL procedure(argument, ...) | INSERT ... | UPDATE ... | DELETE ... | BEGIN statement; ... END};
ALTER TABLE table {ENABLE | DISABLE} TRIGGER name;
DROP TRIGGER [IF EXISTS] name;
CREATE [OR REPLACE] TRIGGER name AFTER INSERT ON table DEFERRED [TIMEOUT 10min] EXECUTE ...;
RETRY TRIGGER QUEUE name;               -- a DEFERRED trigger: run the failed job again
SKIP TRIGGER JOB name job_id;           -- a DEFERRED trigger: leave one job out
SHOW TRIGGERS;                          -- columns: name, table, timing, event, enabled, definition

Statements inside a procedure or trigger body (each ends with ;):

SELECT ... | INSERT ... | UPDATE ... | DELETE ... | CALL name(...) | SHOW ... | EXPLAIN ...
DECLARE variable TYPE [DEFAULT expr]
SET variable = expr                     -- also: SET variable = (SELECT ...)
IF condition THEN ... [ELSIF condition THEN ...] [ELSE ...] END IF
WHILE condition DO ... END WHILE
FOR row IN (SELECT ...) DO ... END FOR  -- row.column is a value of the current row
DECLARE cursor CURSOR FOR SELECT ...
OPEN cursor
FETCH cursor INTO variable, ...        -- sets the variable FOUND
CLOSE cursor
RETURN
RAISE expr                              -- error: the whole CALL (or statement) is undone
PRINT expr                              -- a line in the "print" (or "debug") result

Transactions and cursors

BEGIN [TRANSACTION | WORK];
COMMIT [TRANSACTION | WORK];
ROLLBACK [TRANSACTION | WORK];

DECLARE name CURSOR FOR SELECT ...;     -- runs the query and keeps the rows
FETCH [NEXT [n] | ALL] FROM name;       -- NEXT without n = 1 row
CLOSE name;

Database

BACKUP DATABASE;          -- needs --backups <folder> on kdb serve / shell / sql
CHECK TABLE any_name;     -- reads every file of the whole database

CHECK TABLE checks all files; the table name is not used.

Databases

A database is a folder in the data folder (chapter 1). Each database has its own tables, views, triggers and procedures.

CREATE DATABASE [IF NOT EXISTS] name;
DROP DATABASE [IF EXISTS] name;     -- closes it and moves its folder to <data folder>/_trash
SHOW DATABASES;                     -- columns: name, tables, bytes, current
USE name;                           -- the statements after it use this database
SELECT ... FROM other_db.table ...; -- read a table of another database on the same server

@"
CREATE DATABASE IF NOT EXISTS market;
CREATE DATABASE demo;
USE demo;
CREATE TABLE notes (id INT PRIMARY KEY, text STRING);
SHOW DATABASES;
USE market;
DROP DATABASE demo;
DROP DATABASE IF EXISTS demo;
SHOW DATABASES;
"@ | kdb shell --data-dir kdb-data

KDB 0.2.0-dev shell on default (data folder kdb-data)
End statements with ';'. Type .help for commands, .exit to quit.
kdb> database 'market' created
kdb> database 'demo' created
kdb> using database 'demo'
kdb> table 'notes' created
kdb> name    | tables | bytes | current
--------+--------+-------+--------
default |      0 |     0 | false
demo    |      1 |   229 | true
market  |      0 |     0 | false
(3 rows, 0.1 ms)
kdb> using database 'market'
kdb> database 'demo' dropped; its folder was moved to kdb-data/_trash/1789135960212-demo
kdb> database 'demo' does not exist
kdb> name    | tables | bytes | current
--------+--------+-------+--------
default |      0 |     0 | false
market  |      0 |     0 | true
(2 rows, 0.0 ms)
kdb>

Users and roles

Users of the KDB server (chapter 16). They belong to the data folder, not to one database.

CREATE USER [IF NOT EXISTS] name [WITH] PASSWORD 'secret' [ROLE admin|write|read|none];
ALTER USER name [WITH] PASSWORD 'new secret';
ALTER USER name ROLE read;
DROP USER [IF EXISTS] name;
SHOW USERS;                                              -- columns: name, role, grants, created, changed
GRANT read|write|admin|ALL ON DATABASE db TO name;       -- ALL = admin
REVOKE [read|write|admin|ALL] ON DATABASE db FROM name;

Types

Chapter 18 explains JSON and BYTES with examples.

PostgreSQL type names are accepted too, with their rules: SMALLINT, SERIAL/BIGSERIAL (the PRIMARY KEY with AUTO), REAL, DOUBLE PRECISION, NUMERIC(p,s) (DECIMAL(s), p digits checked), NUMERIC (DOUBLE), MONEY, VARCHAR(n)/CHAR(n) (STRING, at most n characters), TIMESTAMPTZ (TIMESTAMP), TIME (STRING HH:MM:SS), INTERVAL (LONG milliseconds), UUID (STRING, checked), INET, CITEXT, type[] (JSON array). The full table is in chapter 22.

Conversions (when a value goes into a column, or with CAST)

CAST(x AS TYPE) and x::TYPE are the same. CAST(x AS DECIMAL(2)) needs the scale.

Operators

From loose (evaluated last) to tight (evaluated first):

SELECT 2 + 3 * 4 AS a, (2 + 3) * 4 AS b, -2 * 3 AS c, 1 = 1 OR 1 = 2 AND 1 = 3 AS d, NOT 1 = 2 AND 2 = 2 AS e;

a,b,c,d,e
14,20,-6,true,true

NULL and logic

SELECT coalesce(NULL, 'n/a') AS coalesce, nullif(5, 5) AS nullif, NULL + 1 AS null_plus, NULL = NULL AS null_eq,
       NULL IS NULL AS is_null, TRUE AND NULL AS and_null, TRUE OR NULL AS or_null, NOT FALSE AS not_false,
       3 BETWEEN 1 AND 5 AS between_1_5, 'COAST' IN ('CITY', 'COAST') AS in_list, 1 <> 2 AS not_equal;

coalesce,nullif,null_plus,null_eq,is_null,and_null,or_null,not_false,between_1_5,in_list,not_equal
n/a,,,,true,,true,true,true,true,true

(Empty CSV fields are NULL.) NULL in arithmetic or comparison gives NULL. WHERE and HAVING treat NULL as false. Use IS NULL.

Functions

Math

More math, finance and price functions (trunc, cbrt, log2, gcd, lcm, factorial, pi, trigonometry, width_bucket, clamp, safe_divide, div0, percent, random, setseed, normal_cdf, normal_inv, round_tick, pivot_point, pivot_level, cagr, fv, pv, pmt, nper, npv, irr) are in chapter 19.

Text

Text functions count characters, so text in any language works (length('नेपाल') is 5). A NULL argument gives NULL (except concat, concat_ws, coalesce).

Regular expressions: . [abc] [^abc] [a-z] \d \w \s \b ^ $ (...) (?:...) a|b * + ? {n,m} (and lazy *?). \w also matches letters of other languages. Back references and look-ahead are not supported. KDB never backtracks, so no pattern can hang the server.

JSON and BYTES

Chapter 18 has examples. A path looks like '$.a.b[0]', '$.items[-1]' (last) or '$."key with spaces"'.

Full-text search

Chapter 17 has examples. They work with or without a FULLTEXT index (without one, every row is read).

Words: letters and digits of any language (English, Nepali). English letters are made lower case, Devanagari digits become 0-9, punctuation (also । and ॥) separates words, and a number like 12.5 stays one word.

Time

All of these work in UTC. Arguments can be TIMESTAMP, DATE or time text.

Nepal clock: hour(time + 20700000) (20700000 ms = 5 h 45 min).

Also in chapter 19: age, quarter, day_of_year, week_of_year, last_day, make_date, make_timestamp, fiscal_year, fiscal_quarter, nepal_fiscal_year.

Aggregates

Used in SELECT with GROUP BY, SAMPLE BY or alone; also with OVER (...).

SELECT count(*) AS count_all, count(close) AS count_close, count(DISTINCT symbol) AS count_distinct,
       count_distinct(symbol) AS count_distinct2, sum(volume) AS sum, round(avg(close), 3) AS avg,
       min(close) AS min, max(close) AS max, first(close) AS first, last(close) AS last
FROM ochl_day WHERE time IN '2026-09-10';
SELECT round(stddev(close), 3) AS stddev, round(stddev_pop(close), 3) AS stddev_pop, round(variance(close), 3) AS variance,
       round(var_pop(close), 3) AS var_pop, median(close) AS median, percentile(close, 0.9) AS p90,
       round(vwap(close, volume), 3) AS vwap
FROM ochl_day WHERE time IN '2026-09-10';

count_all,count_close,count_distinct,count_distinct2,sum,avg,min,max,first,last
3,3,3,3,410710,357.4,191.2,504.9,504.9,191.2
stddev,stddev_pop,variance,var_pop,median,p90,vwap
157.684,128.748,24864.19,16576.127,376.1,479.14,414.832

Aggregates ignore NULL values. count() without * is the same as count(*).

More aggregates are in chapter 19 (row engine only):

Series and window functions

Used in SELECT only. Without OVER, series functions run over all result rows in time order.

fn(...) OVER ([PARTITION BY expr, ...] [ORDER BY expr [ASC|DESC], ...] [frame])

frame:  ROWS n PRECEDING | ROWS UNBOUNDED PRECEDING
      | {ROWS | RANGE} BETWEEN start AND end
start, end:  UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING
             (RANGE: n is a number, or text like '5 days' for a time ORDER BY)

The period n must be a whole number, 1 or more.

More window and series functions are in chapter 19: percent_rank, cume_dist, ntile, first_value, last_value, nth_value, cumprod, cummax, cummin, returns and risk (pct_return, log_return, cum_return, drawdown, volatility, zscore, minmax_scale) and technical indicators (wma, dema, tema, trima, kama, hma, vwma, macd, macd_signal, macd_hist, stoch_k, stoch_d, stochf_k, stochrsi_k, stochrsi_d, willr, cci, roc, rocp, rocr, mom, trix, natr, trange, bb_upper, bb_middle, bb_lower, bb_percent_b, bb_width, kc_upper, kc_middle, kc_lower, dc_upper, dc_middle, dc_lower, plus_di, minus_di, dx, adx, adxr, aroon_up, aroon_down, aroon_osc, sar, ichimoku_tenkan, ichimoku_kijun, ichimoku_senkou_a, ichimoku_senkou_b, ichimoku_chikou, obv, ad, cmf, adosc, mfi, efi, eom, supertrend, supertrend_direction, ha_open, ha_high, ha_low, ha_close, linearreg, linearreg_slope, linearreg_intercept, linearreg_angle, tsf).

Table functions and PIVOT

SELECT ... FROM generate_series(start, stop [, step]) [[AS] alias [(column)]]   -- numbers or times
SELECT ... FROM long_sequence(n)                                              -- column x = 1 .. n
SELECT ... FROM source PIVOT (agg(x) [AS name], ... FOR column IN (value [AS name], ...)) [[AS] alias]

Chapter 19 explains them (Part 6).

Passwords, hashes and random values

A NULL argument gives NULL. The random functions give a new value for every row and every call. password_hash and password_verify take about 0.05 to 0.4 seconds each: call password_verify once per login. password_hash('...') with a literal password in INSERT ... VALUES and UPDATE ... SET is hashed before the statement waits for the database, and the statement shown back contains the hash, not the password. Chapter 16 shows the register and login pattern.

Other expressions

Time rules

Time zones for TIME ZONE (fixed offsets only, no daylight saving): UTC, GMT, Z, Etc/UTC, Asia/Kathmandu, Asia/Katmandu, NPT, Asia/Kolkata, Asia/Calcutta, IST, Asia/Colombo, Asia/Dhaka, Asia/Thimphu, Asia/Karachi, Asia/Dubai, Asia/Bangkok, Asia/Jakarta, Asia/Shanghai, Asia/Hong_Kong, Asia/Singapore, Asia/Taipei, Asia/Tokyo, Asia/Seoul, or an offset like '+05:45'. Others are refused:

SELECT time, count(*) FROM ochl_minutes SAMPLE BY 1h TIME ZONE 'Europe/London';

error: time zone 'Europe/London' is not supported (it may change for daylight saving); use a fixed offset like '+05:45'

Reserved words

These words cannot be table, column or alias names without double quotes:

ALTER AND AS BETWEEN CASE CREATE DEFAULT DELETE DISTINCT DROP ELSE END FALSE FROM GROUP IN INSERT INTO IS JOIN LIKE LIMIT NOT NULL ON OR ORDER PRIMARY REFERENCES SELECT SET TABLE THEN TO TRUE UNIQUE UPDATE VALUES WHEN WHERE

CREATE TABLE orders (id INT, order STRING);

error: line 1, column 30: expected a column name but found the reserved word 'order' (write it in double quotes, "order", to use it as a name)

CREATE TABLE orders (id INT, "order" STRING);
INSERT INTO orders VALUES (1, 'buy');
SELECT id, "order" FROM orders;

table 'orders' created

1 row(s) inserted

id | order
---+------
 1 | buy  
(1 row, 0.1 ms)

These words can be names, but not a short alias without AS (write AS left then): ASOF CROSS FULL HAVING INNER LATEST LEFT LT OVER REFRESH RIGHT SAMPLE SPLICE UNION USING.

All other words (time, date, timestamp, symbol, partition, open, close, volume, value, key, ...) are normal names.

Limits

Correlated subqueries

SELECT s.symbol, (SELECT max(close) FROM ochl_day d WHERE d.symbol = s.symbol) AS best FROM securities s;

The subquery uses s.symbol of the outer row. KDB does not run it once per row: it reads ochl_day once, groups it by symbol, and looks every security up in that result. Chapter 23 has the rules.

Try it yourself

1. What is the result of SELECT 10 / 4, 10 % 4, round(10 / 4)?
2. Write the number of whole days between 2026-01-01 and 2026-09-10.
3. Which of these names need double quotes: time, order, left, select, symbol?

Next: 12. Troubleshooting