SQL basics

How KDB reads SQL text: statements, names, text, numbers, TRUE/FALSE/NULL, comments, errors and reserved words.

• Category: Getting started
• Keywords: syntax, statement, semicolon, ;, case, upper case, lower case, identifier, name, quoted name, double quotes, string, text, single quote, escape quote, number, integer, float, scientific notation, boolean, constant, comment, --, /*, error message, line and column, syntax error, reserved word, keyword, three-valued logic, IS NULL, IS NOT NULL, unknown, alias
• See also: Quick start, Operators, Conditional and NULL functions, Column types, SELECT, WHERE

SQL is the language you use to talk to KDB. A statement is one command, like SELECT or INSERT. This page shows the small rules that every statement follows.

Example: Names, text, numbers, NULL and a comment in one query

-- the price of one CITY share, and a note
SELECT 'CITY' AS symbol,
       512.45 AS "Last Price",   /* a name with a space needs double quotes */
       100 AS quantity,
       512.45 * 100 AS amount,
       'City''s bank' AS note,
       NULL AS broker;

Result:

(1 row)

Statements and ;

Syntax:

statement ; statement ; ...

A text can hold many statements. Put ; between them. The ; after the last statement is optional. Extra ; are ignored.

• When you send a text (with kdb sql, a file, or HTTP /exec), KDB first checks the grammar of the whole text. If one statement has a syntax error, no statement runs. (kdb shell runs what you typed each time a line ends with ;.)
• Then the statements run one by one. Each one is saved on its own.
• At the first error while running, KDB stops. The statements before it stay saved. The statements after it do not run.
• One statement is all or nothing: an INSERT of 5000 rows with one bad row saves no row.
• Notes: to make many statements succeed or fail together, use BEGIN ... COMMIT.

Example: Three statements, one result each

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);
INSERT INTO brokers VALUES (58, 'Northgate Securities');;
SELECT id, name FROM brokers

Result:

CREATE TABLE brokers (

table 'brokers' created

INSERT INTO brokers VALUES (58, 'Northgate Securities')

1 row(s) inserted

SELECT id, name FROM brokers

(1 row)

Example: A script stops at the first error; row 21 stays saved (shows an error)

Setup:

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

INSERT INTO brokers VALUES (21, 'Test Broker A');
INSERT INTO brokers VALUES (21, 'Test Broker B');
INSERT INTO brokers VALUES (22, 'Test Broker C');

Result:

1 row(s) inserted

Error: a row with id='21' already exists in 'brokers'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it

Example: A syntax error in the second statement: the first does not run either (shows an error)

CREATE TABLE notes (id INT, body STRING);
SELEC * FROM notes;

Result:

Error: line 2, column 1: expected a statement (CREATE, DROP, RENAME, ALTER, SHOW, INSERT, UPDATE, DELETE, BACKUP, CHECK, SELECT, EXPLAIN, CALL, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, DECLARE, FETCH or CLOSE) but found 'SELEC'

Upper and lower case

Syntax:

SELECT = select = Select
ochl_day = OCHL_DAY = Ochl_Day
'CITY' <> 'city'

Keywords, table names, column names and function names do not care about case. Text values do: 'CITY' is not 'city'.

• A table or column keeps the spelling you used in CREATE TABLE. SHOW TABLES shows that spelling.
• A column name in the result keeps the spelling of the alias you wrote.
• Notes: in PostgreSQL, a name in double quotes is case sensitive. In KDB it is not: "Total" and "total" are the same name.

Example: Any case works for names; text compares exactly

CREATE TABLE Brokers (Id INT PRIMARY KEY, Name STRING);
INSERT INTO BROKERS (ID, NAME) VALUES (58, 'Northgate Securities');
select id, name, name = 'northgate securities' AS same_text, upper(name) = 'NORTHGATE SECURITIES' AS same_upper from brokers;

Result:

CREATE TABLE Brokers (

table 'Brokers' created

INSERT INTO BROKERS (ID, NAME) VALUES (58, 'Northgate Securities')

1 row(s) inserted

SELECT id, name, name = 'northgate securities' AS same_text, upper(name) = 'NORTHGATE SECURITIES' AS

(1 row)

Names and "quoted names"

Syntax:

name
"any name"
"a name with a "" inside"

A name (also called identifier) names a table, column, alias, view or index. A plain name has letters, digits and _, and does not start with a digit. Letters of any language (Nepali, Chinese, ...) also work.

Write a name in double quotes when it:

• is a reserved word, like "order" or "select";
• has a space or another special character, like "Total Amount";
• starts with a digit, like "1st_level".

• Notes: time, date, timestamp, symbol, partition, open, close, volume, value and key are not reserved. Use them freely as column names.

Example: A forum table with a column called order

CREATE TABLE posts (id LONG PRIMARY KEY, "order" INT, "Posted By" STRING, body STRING);
INSERT INTO posts VALUES (1, 1, 'ram', 'First post');
SELECT id, "order", "Posted By" FROM posts ORDER BY "order";

Result:

CREATE TABLE posts (

table 'posts' created

INSERT INTO posts VALUES (1, 1, 'ram', 'First post')

1 row(s) inserted

SELECT id, "order", "Posted By" FROM posts ORDER BY "order"

(1 row)

Example: Names in any language work without quotes (here Nepali)

CREATE TABLE कम्पनी (चिन्ह STRING PRIMARY KEY, नाम STRING);
INSERT INTO कम्पनी VALUES ('CITY', 'नमस्ते');
SELECT चिन्ह, नाम FROM कम्पनी;

Result:

CREATE TABLE कम्पनी (

table 'कम्पनी' created

INSERT INTO कम्पनी VALUES ('CITY', 'नमस्ते')

1 row(s) inserted

SELECT चिन्ह, नाम FROM कम्पनी

(1 row)

Example: A reserved word without quotes (shows an error)

CREATE TABLE orders (id INT, order STRING);

Result:

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)

Text: 'single quotes'

Syntax:

'text'
'City''s bank'
''

Text (also called a string) goes in single quotes. A ' inside the text is written twice: ''. Text may have many lines and any language (UTF-8).

• There are no backslash escapes: 'a\nb' is the four characters a, \, n, b.
• '' is an empty text. It is not NULL.
• Double quotes "..." are for names, never for text. WHERE symbol = "CITY" looks for a column called CITY.
• Notes: bytes are written X'0A0B'; see BYTES. Times are text too: '2026-09-10T11:00:00+05:45'; see TIMESTAMP.

Example: Quotes inside text, empty text, text in another language

SELECT 'City''s bank' AS name,
       '' AS empty, '' IS NULL AS empty_is_null,
       'नेपाल' AS nepali,
       'a\nb' AS no_escapes;

Result:

(1 row)

Example: Double quotes mean a column name, not text (shows an error)

CREATE TABLE securities (symbol SYMBOL PRIMARY KEY, name STRING);
SELECT name FROM securities WHERE symbol = "CITY";

Result:

table 'securities' created

Error: line 2, column 44: unknown column 'CITY' (table 'securities' has: symbol name)

Example: Text without its closing quote (shows an error)

SELECT 'Northgate;

Result:

Error: line 1, column 8: expected a closing ' to end the string that starts here but found the end of the input

Numbers

Syntax:

42    -7    512.45    .5    1.5e3    2E-4

A number without a point or e is a whole number (type LONG). A number with a point or e is a DOUBLE.

• There is no , or _ inside a number: write 1000000, not 1,000,000.
• A number put into a column is converted to the column type; see column types.
• / always gives a DOUBLE: 10 / 4 is 2.5. See arithmetic.
• Notes: for exact money, the column type matters, not the literal: use DECIMAL(2) columns. For amounts with more than about 15 digits, write the value as text: '123456789012345.67'.

Example: Kinds of numbers

SELECT 42 AS whole, -7 AS negative, 512.45 AS price, .5 AS half, 1.5e3 AS thousand_five, 2E-4 AS small, 10 / 4 AS division;

Result:

(1 row)

Example: A whole number that is too big (shows an error)

SELECT 9223372036854775808;

Result:

Error: line 1, column 8: expected an integer between -9223372036854775808 and 9223372036854775807 but found '9223372036854775808'

TRUE, FALSE and NULL

Syntax:

TRUE    FALSE    NULL

TRUE and FALSE are the two BOOL values. NULL means "no value" or "unknown". Every column type can hold NULL, unless the column is NOT NULL.

• NULL is not 0, not '' and not FALSE.
• Use IS NULL and IS NOT NULL to test for NULL. See NULL logic.
• Notes: a BOOL is not a number: TRUE = 1 is an error. A BOOL column accepts 1/0 and 'true'/'false' when a row is written.

Example: The three constants

SELECT TRUE AS yes, FALSE AS no, NULL AS nothing, NULL IS NULL AS is_null, NOT FALSE AS not_false;

Result:

(1 row)

Example: A user who has not verified the email yet

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL, verified BOOL NOT NULL DEFAULT FALSE, phone STRING);
INSERT INTO users (email) VALUES ('ram@example.com');
INSERT INTO users (email, verified, phone) VALUES ('sita@example.com', TRUE, '9800000000');
SELECT id, email, verified, phone, phone IS NULL AS no_phone FROM users;

Result:

CREATE TABLE users (

table 'users' created

INSERT INTO users (email) VALUES ('ram@example.com')

1 row(s) inserted

INSERT INTO users (email, verified, phone) VALUES ('sita@example.com', TRUE, '9800000000')

1 row(s) inserted

SELECT id, email, verified, phone, phone IS NULL AS no_phone FROM users

(2 rows)

Comments

Syntax:

-- to the end of the line
/* between these marks, also over many lines */

KDB skips comments. Use them to explain a script.

• /* ... */ comments do not nest: the first */ ends the comment.
• A /* without */ is an error that shows where the comment starts.

Example: Both kinds of comments in a report query

Setup:

CREATE TABLE ledger (id LONG PRIMARY KEY AUTO, account INT, amount DECIMAL(2));
INSERT INTO ledger (account, amount) VALUES (1, 1000.00), (1, -250.50), (2, 250.50);

/* Balance per account.
   Used by the daily report. */
SELECT account,            -- the account number
       sum(amount) AS balance
FROM ledger
GROUP BY account
ORDER BY account;

Result:

(2 rows)

Example: A comment that is not closed (shows an error)

SELECT 1 /* forgot to close

Result:

Error: line 1, column 10: expected '*/' to close the comment that starts here but found the end of the input

Error messages

Syntax:

error: line L, column C: expected ... but found ...
error: <what is wrong>

When KDB cannot run a statement, it prints error: and a message. Messages about the SQL text say where: line 3, column 8 is the place in the text you sent (in a file: the line of the file). They also say what KDB expected there.

• Returns: exit code 1 from kdb sql; HTTP status 400 from the HTTP API.
• Notes: a missing comma is not an error: SELECT symbol close means "column symbol, named close". Chapter 12 lists many messages and their fixes.

Example: The message points to line 3, column 8 (shows an error)

Setup:

CREATE TABLE ochl_day (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time);

SELECT time,
       symbol,
       clsoe
FROM ochl_day;

Result:

Error: line 3, column 8: unknown column 'clsoe' (table 'ochl_day' has: time symbol close)

Example: What the grammar wanted (shows an error)

Setup:

CREATE TABLE ochl_day (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time);

SELECT symbol close FROM ochl_day WHERE;

Result:

Error: line 1, column 40: expected an expression (a value, column name, function call or '(') but found ';'

Reserved words

Syntax:

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

These 40 words have a fixed meaning. They cannot be a table, column or alias name, unless you write them in double quotes: "order", "from".

• All other words are normal names, also words like CHECK, INDEX, BEGIN, KEY, USER, DATE, TIME, OPEN and CLOSE: KDB knows them from their place in the statement.
• Notes: PostgreSQL reserves more words. A name that works in KDB may need quotes in PostgreSQL.

Example: Columns called time, date, open, close, key and value need no quotes

CREATE TABLE prices (time TIMESTAMP, date DATE, open DOUBLE, close DOUBLE, key STRING, value DOUBLE) TIMESERIES(time);
INSERT INTO prices VALUES ('2026-09-10', '2026-09-10', 504.6, 504.9, 'CITY', 1);
SELECT time, date, open, close, key, value FROM prices;

Result:

CREATE TABLE prices (

table 'prices' created

INSERT INTO prices VALUES ('2026-09-10', '2026-09-10', 504.6, 504.9, 'CITY', 1)

1 row(s) inserted

SELECT time, date, open, close, key, value FROM prices

(1 row)

Example: A reserved word as a column name (shows an error)

CREATE TABLE transfers (id INT, from INT, to_account INT);

Result:

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

Clause words

Syntax:

ASOF CROSS FULL HAVING INNER LATEST LEFT LT OVER REFRESH RIGHT SAMPLE SPLICE UNION USING

These 15 words start a part of a query (a clause), like LEFT JOIN or SAMPLE BY. They can be table and column names. But they cannot be a short alias without AS: write SELECT 1 AS left, not SELECT 1 left.

Example: Clause words as column names, and as aliases with AS

CREATE TABLE t (left INT, right INT, sample INT, latest INT);
INSERT INTO t VALUES (1, 2, 3, 4);
SELECT left, right, sample AS union, latest AS over FROM t;

Result:

CREATE TABLE t (

table 't' created

INSERT INTO t VALUES (1, 2, 3, 4)

1 row(s) inserted

SELECT left, right, sample AS union, latest AS over FROM t

(1 row)

Example: RIGHT JOIN: every broker, also the ones without trades

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING);
CREATE TABLE trades (id LONG PRIMARY KEY, buyer INT);
INSERT INTO brokers VALUES (1, 'Alpha'), (2, 'Beta');
INSERT INTO trades VALUES (10, 1);

SELECT brokers.name, trades.id FROM trades RIGHT JOIN brokers ON trades.buyer = brokers.id ORDER BY brokers.name;

Result:

(2 rows)

Example: A clause word as a short alias (shows an error)

SELECT 1 left;

Result:

Error: line 1, column 10: expected an operator, AS, ',', FROM, WHERE, GROUP BY, UNION ALL, ORDER BY, LIMIT, ';' or the end of the input but found 'left'

NULL logic

Syntax:

value IS NULL
value IS NOT NULL
NULL = anything   ->  NULL (unknown)

NULL means "unknown". So most things done with NULL give NULL again:

• WHERE and HAVING keep a row only when the condition is TRUE. A NULL condition drops the row, like FALSE.
• So WHERE phone = NULL finds nothing. Write WHERE phone IS NULL.
• WHERE v <> 5 also drops the rows where v is NULL.
• Aggregates skip NULL: count(v) counts only values, sum(v) adds only values. count(*) counts all rows.
• ORDER BY v puts NULL last (with DESC: first).
• Notes: coalesce(v, 0) gives 0 for NULL; see conditional functions.

Example: NULL in expressions

SELECT NULL + 1 AS plus, NULL = NULL AS equal, TRUE AND NULL AS and_true, FALSE AND NULL AS and_false,
       TRUE OR NULL AS or_true, 3 IN (1, NULL) AS in_list, NULL IS NULL AS is_null;

Result:

(1 row)

Example: Users without a phone: = NULL finds nothing, IS NULL works

Setup:

CREATE TABLE users (id INT PRIMARY KEY, email STRING, phone STRING);
INSERT INTO users VALUES (1, 'ram@example.com', NULL), (2, 'sita@example.com', '9800000000'), (3, 'hari@example.com', NULL);

SELECT id FROM users WHERE phone = NULL;
SELECT id FROM users WHERE phone IS NULL;
SELECT id FROM users WHERE phone <> '9800000000';
SELECT count(*) AS users, count(phone) AS with_phone FROM users;

Result:

SELECT id FROM users WHERE phone = NULL

(0 rows)

SELECT id FROM users WHERE phone IS NULL

(2 rows)

SELECT id FROM users WHERE phone != '9800000000'

(0 rows)

SELECT count(*) AS users, count(phone) AS with_phone FROM users

(1 row)