Operators

Every operator of KDB SQL, which one is done first, and what NULL does to each of them.

• Category: Querying
• Keywords: operator, precedence, order of operations, parentheses, +, -, *, /, %, ||, concat, join text, ->, ->>, ::, cast, , !, <>, NULL rules, three-valued logic, unary minus, precedence, ||, ::, ->, ->>, int_division, integer division, integer division, int_division, 7 / 2
• See also: WHERE, Math functions (operators), Conversion functions, JSON functions, Conditional and NULL functions, Text functions

An operator is a sign or a word between values, like +, =, AND or ||. When an expression has many operators, KDB does the tight ones first: 2 + 3 * 4 is 14, because * binds tighter than +. Use ( ) to choose the order yourself, and to make long conditions easy to read.

Example: A trade log report line: value, commission and a flag (sample data: sample)

SELECT transaction_id, symbol,
       quantity * rate AS value,
       round(quantity * rate * 0.4 / 100, 2) AS commission,
       quantity >= 500 AND rate > 500 AS big_bank_trade
FROM trade_log
WHERE date IN '2026-09-10' AND symbol IN ('CITY', 'COAST')
ORDER BY value DESC
LIMIT 4;

Result:

(4 rows)

Operator precedence

Syntax:

OR
AND
NOT
=  ==  !=  <>  <  <=  >  >=   IS [NOT] NULL   [NOT] IN   [NOT] BETWEEN   [NOT] LIKE   [NOT] ILIKE
+  -  ||
*  /  %
-value
::  ->  ->>

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

• Notes: operators of the same level are done from left to right: 10 - 4 - 3 is 3, and a = b IS NULL means (a = b) IS NULL.
• a = b = c is an error when c is not true or false: the first = gives true or false, which is then compared with c.
• AND and OR stop early: false AND ... and true OR ... do not compute the right side.
• Nesting is limited to 100 levels of ( ) and operators.

Example (sample data: sample)

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

Result:

(1 row)

Example: Mixing AND and OR: ( ) change the answer (sample data: sample)

SELECT count(*) AS without_parens FROM trade_log WHERE symbol = 'TELCO' OR symbol = 'COAST' AND quantity >= 1000;
SELECT count(*) AS with_parens FROM trade_log WHERE (symbol = 'TELCO' OR symbol = 'COAST') AND quantity >= 1000;

Result:

SELECT count(*) AS without_parens FROM trade_log WHERE symbol = 'TELCO' OR symbol = 'COAST' AND quan

(1 row)

SELECT count(*) AS with_parens FROM trade_log WHERE (symbol = 'TELCO' OR symbol = 'COAST') AND quant

(1 row)

Example: || and + are on the same level (sample data: sample; shows an error)

SELECT 'Total: ' || 2 + 3 AS label;

Result:

Error: line 1, column 23: 'Total: 2' and '3' are not both numbers

Example: The fix: ( ) around the sum (sample data: sample)

SELECT 'Total: ' || (2 + 3) AS label;

Result:

(1 row)

Arithmetic: + - * / %

Syntax:

a + b    a - b    a * b    a / b    a % b    -a

Add, subtract, multiply, divide and remainder. The full rules are in arithmetic operators.

• NULL: NULL on either side gives NULL.
• Notes: dividing by zero (5 / 0, 5.0 / 0, 5 % 0) stops the query with "division by zero" (SQLSTATE 22012), as in PostgreSQL. Write x / NULLIF(y, 0) to get NULL instead.
• Text is not a number: 'abc' + 1 is an error. + does not join text; use ||.
• TRUE + 1 is an error: BOOL is not a number.

Example: Daily range and change in percent (sample data: sample)

SELECT symbol, round(high - low, 2) AS day_range, round((close - open) / open * 100, 2) AS change_pct
FROM ochl_day
WHERE time IN '2026-09-10';

Result:

(3 rows)

Example: Nepal clock hour: add 5 h 45 min in milliseconds (sample data: sample)

SELECT hour(time + 20700000) AS npt_hour, sum(volume) AS volume
FROM ochl_minutes
WHERE symbol = 'CITY' AND time IN '2026-09-10'
GROUP BY hour(time + 20700000)
ORDER BY npt_hour;

Result:

(4 rows)

Example (sample data: sample)

SELECT 7 / 2 AS a, 7 % 2 AS b, 5 / NULLIF(0, 0) AS c, 9223372036854775807 * 2.0 AS big_as_double, CAST('2026-09-10' AS DATE) + 1 AS next_day;

Result:

(1 row)

Example: Dividing by zero is an error (sample data: sample; shows an error)

SELECT 5 / 0;

Result:

Error: line 1, column 10: division by zero

Example: + does not join text (sample data: sample; shows an error)

SELECT 'CITY' + 1;

Result:

Error: line 1, column 15: 'CITY' and '1' are not both numbers

Integer division: SET int_division

Syntax:

SET int_division = integer | float | default

In KDB, / gives a DOUBLE: 7 / 2 is 3.5. PostgreSQL gives 3: a whole number divided by a whole number stays a whole number there. An app written for PostgreSQL may count on that, for example to read sum(ms) / 1000 into an integer.

• Returns: with integer, LONG / LONG is a LONG, cut toward zero. When either side is a DOUBLE, the result is still a DOUBLE; a DECIMAL divided by a DECIMAL or a whole number is an exact DECIMAL in both settings (DECIMAL).
• Scope: the setting is for the session: the rest of one /exec request, a shell, or one Postgres connection. Triggers and procedures that its statements run use it too. default goes back to what the session started with.
• Postgres port: kdb serve --pg-int-division on (or KDB_PG_INT_DIVISION=on) makes integer the start value of every Postgres connection. A connection can also ask for it in its connection string (the startup parameter int_division=integer), or send SET int_division = integer. SHOW int_division gives the value.
• Table expressions: a table's own CHECK, DEFAULT, computed columns, index expressions and materialized view queries always use the default DOUBLE division, whatever the session that writes or reads. They are shared by every session, so the answer must not depend on who asked. A query in integer mode does not use an index on an expression with /; it reads the table instead.
• Notes: division by zero is an error in both modes. To get a DOUBLE in integer mode, cast one side: CAST(x AS DOUBLE) / y. To get a whole number in float mode, use trunc(x / y).

Example: The same division in both modes (sample data: sample)

SELECT 7 / 2 AS a, -7 / 2 AS b, 7 / 2.0 AS c;
SET int_division = integer;
SELECT 7 / 2 AS a, -7 / 2 AS b, 7 / 2.0 AS c, 7 / NULLIF(0, 0) AS d;

Result:

SELECT 7 / 2 AS a, -7 / 2 AS b, 7 / 2.0 AS c

(1 row)

SET int_division = 'integer'

whole number / whole number gives a whole number for this session (7 / 2 = 3)

SELECT 7 / 2 AS a, -7 / 2 AS b, 7 / 2.0 AS c, 7 / nullif(0, 0) AS d

(1 row)

Example: An average in whole shares, as a PostgreSQL app expects (sample data: sample)

SET int_division = integer;
SELECT symbol, sum(quantity) / count(*) AS avg_whole_shares FROM trade_log WHERE date IN '2026-09-10' GROUP BY symbol ORDER BY symbol LIMIT 3;

Result:

SET int_division = 'integer'

whole number / whole number gives a whole number for this session (7 / 2 = 3)

SELECT symbol, sum(quantity) / count(*) AS avg_whole_shares FROM trade_log WHERE date IN '2026-09-10

(3 rows)

Example: Only integer and float (sample data: sample; shows an error)

SET int_division = half;

Result:

Error: int_division must be integer or float, not 'half'

Comparison operators

Syntax:

a = b    a == b    a != b    a <> b    a < b    a <= b    a > b    a >= b

Compare two values and give true or false. They are mostly used in WHERE, but a comparison is a normal value: you can put it in the select list.

• Returns: BOOL.
• NULL: NULL on either side gives NULL.
• Notes: a text compared with a TIMESTAMP or DATE is read as a time. A number compared with a text that holds a number is compared as numbers. Other mixes (text with a number, a BOOL with a number) are an error.
• IS NULL, IN, BETWEEN, LIKE and ILIKE are on the same level; see WHERE.

Example: Up day or down day, as a true/false column (sample data: sample)

SELECT time, close, close > open AS up_day, close = high AS closed_at_high
FROM ochl_day
WHERE symbol = 'VOLT' AND time >= '2026-09-06';

Result:

(5 rows)

Example: A BOOL cannot be compared with a number (sample data: sample; shows an error)

SELECT 1 = 1 = 1;

Result:

Error: line 1, column 14: cannot compare BOOL 'true' with LONG '1'

|| : join text

Syntax:

value || value

Joins two values into one text. Numbers, times and TRUE/FALSE are turned into text first.

• Returns: STRING. Two BYTES values give BYTES.
• NULL: NULL on either side gives NULL. To skip NULL values, use concat(...) or coalesce(x, '') (see text functions).
• Notes: || is on the same level as + and -. Put ( ) around calculations: 'Total: ' || (a + b).

Example: A label for a chart legend (sample data: sample)

SELECT symbol || ' - ' || name AS label FROM securities WHERE sector = 'Banking';

Result:

(2 rows)

Example: Full name with city; NULL makes the whole text NULL (sample data: query-app)

SELECT username, full_name || ' (' || city || ')' AS shown,
       full_name || ' (' || coalesce(city, 'no city') || ')' AS shown_safe
FROM app_users
WHERE id IN (1, 6, 8);

Result:

(3 rows)

Example (sample data: sample)

SELECT 'CITY' || '-' || 1 AS a, 5.5 || '%' AS b, 'x' || NULL AS c;

Result:

(1 row)

-> and ->> : parts of JSON

Syntax:

json -> 'key'      json -> index
json ->> 'key'     json ->> index

-> takes a part of a JSON value and gives it as JSON. ->> gives the part as text. An index counts from 0; -1 is the last element. The JSON functions are explained in JSON functions.

• Returns: -> gives JSON, ->> gives STRING.
• NULL: NULL when the key or index is not there, or when the value is NULL.
• Notes: you can chain them: doc -> 'a' -> 'b' ->> 0. They bind as tight as ::, from left to right, so doc ->> 'n'::INT means (doc ->> 'n')::INT.

Example (sample data: sample)

SELECT '{"symbol": "CITY", "prices": [512, 515.5, 509]}' ->> 'symbol' AS symbol,
       '{"symbol": "CITY", "prices": [512, 515.5, 509]}' -> 'prices' AS prices,
       '{"symbol": "CITY", "prices": [512, 515.5, 509]}' -> 'prices' ->> -1 AS last_price;

Result:

(1 row)

:: : change the type

Syntax:

value::TYPE
value::DECIMAL(scale)

x::TYPE is the short form of CAST(x AS TYPE). See conversion functions for every type and rule.

• Returns: the value in the new type.
• NULL: NULL stays NULL.
• Notes: :: binds tighter than every other operator: '5'::INT * 2 is 10, and -5::STRING is the text -5.
• A value that cannot be changed is an error, for example 'abc'::INT, or 512.456::DECIMAL(2) (too many decimal places: use round(x, 2) first).

Example (sample data: sample)

SELECT '42'::INT + 1 AS a, 512.45::DECIMAL(2) AS b, '2026-09-10T11:00:00+05:45'::TIMESTAMP AS c, 7::DOUBLE / 2 AS d;

Result:

(1 row)

Example: Volume as a whole number (sample data: sample)

SELECT time, symbol, volume::LONG AS volume FROM ochl_day WHERE time IN '2026-09-10';

Result:

(3 rows)

Example: Not a number (sample data: sample; shows an error)

SELECT 'abc'::INT;

Result:

Error: line 1, column 13: cannot convert 'abc' to INT

NULL rules

NULL means "no value" or "unknown". Most operators give NULL when a value is NULL, because the answer is unknown too.

• Notes: WHERE, HAVING and join ON keep a row only when the condition is true; NULL counts as false (see NULL in WHERE).
• Aggregates like sum, avg, min and count(x) skip NULL values. count(*) counts every row.
• Use coalesce(x, 0) to use a default instead of NULL (see conditional functions).

Example (sample data: sample)

SELECT NULL + 1 AS plus, NULL = NULL AS equals, FALSE AND NULL AS and_false, TRUE OR NULL AS or_true,
       NOT NULL AS not_null, NULL IS NULL AS is_null, coalesce(NULL, 0) + 1 AS with_default;

Result:

(1 row)

Example: Balance of each account: NULL debits and credits need coalesce (sample data: query-app)

SELECT account,
       sum(debit) - sum(credit) AS wrong_when_one_side_is_empty,
       coalesce(sum(debit), 0) - coalesce(sum(credit), 0) AS balance
FROM ledger
GROUP BY account
ORDER BY account;

Result:

(9 rows)