Rules

Name a check, the trading hours or the dedup choice of a table once, and KDB applies it to every row written - saying which rule refused a row and why.

• Category: Tables
• Keywords: rule, rules, CREATE RULE, ALTER RULE, DROP RULE, SHOW RULES, kdb_rules, price guard, value range, session hours, trading hours, ON FAIL SKIP, ON FAIL REJECT, dedup_keep, keep first, check rule
• See also: CREATE TABLE, Trading calendars, Partitions, SORT BY and DEDUP KEY, Time and time zones

A rule is a named check or setting of one table. It applies to every row written to the table - INSERT, UPDATE, COPY, /imp, /write and line protocol - and a row it refuses says which rule and why. Rules are listed by SHOW RULES, switched off and on by ALTER RULE, and saved with the table.

CREATE RULE

Syntax:

CREATE RULE [IF NOT EXISTS] name ON table
    CHECK (condition)
  | SESSION 'HH:MM' TO 'HH:MM' [TIME ZONE 'zone'] [CALENDAR calendar]
  | SET dedup_keep = 'first' | 'last'
  [ON FAIL REJECT | SKIP] [MESSAGE 'text']

• Rows already there: a new rule checks rows written from then on. Find older rows that break it with SELECT ... WHERE NOT (condition).
• Names: a rule name is unique in the database. DROP TABLE drops the table's rules with it.
• Role: ddl, like CREATE TABLE.
• Not yet: SET null_compare (comparisons with NULL as QuestDB does them) is refused with that reason; comparisons follow standard SQL.

Example: A price guard: the refused row names its rule (shows an error)

Setup:

CREATE TABLE ohlc (time TIMESTAMP, symbol SYMBOL, open DOUBLE, close DOUBLE) TIMESERIES(time) PARTITION BY DAY;
CREATE RULE price_guard ON ohlc CHECK (open > 0 AND close > 0) MESSAGE 'prices must be above 0';

INSERT INTO ohlc VALUES ('2026-10-01T05:30:00Z', 'NABIL', 500, 0);

Result:

Error: rule 'price_guard' of table 'ohlc' refused the row: prices must be above 0 (open > 0 AND close > 0 is false for the row open='500', close='0')

Example: ON FAIL SKIP leaves the bad row out and keeps the others

Setup:

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE, qty DOUBLE) TIMESERIES(time) PARTITION BY DAY;
CREATE RULE no_negative_qty ON ticks CHECK (qty >= 0) ON FAIL SKIP;

INSERT INTO ticks VALUES ('2026-10-01T05:30:00Z', 'NABIL', 500, 10), ('2026-10-01T05:31:00Z', 'NABIL', 501, -3),
  ('2026-10-01T05:32:00Z', 'NABIL', 502, 7);

Result:

2 row(s) inserted, 1 left out by rules (ON FAIL SKIP)

Example: Trading hours of NEPSE: 11:00 to 15:00 in Kathmandu, on its trading days (shows an error)

Setup:

CREATE CALENDAR nepse_days (TIME ZONE 'Asia/Kathmandu', ERA FROM '2022-01-01' DAYS sun-thu);
CREATE TABLE trades (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time) PARTITION BY DAY;
CREATE RULE nepse_hours ON trades SESSION '11:00' TO '15:00' TIME ZONE 'Asia/Kathmandu' CALENDAR nepse_days;

INSERT INTO trades VALUES ('2026-10-01T10:30:00Z', 'NABIL', 500);

Result:

Error: rule 'nepse_hours' of table 'trades' refused the row: its time 2026-10-01T16:15:00 Asia/Kathmandu is outside the session 11:00 to 15:00

ALTER RULE

Syntax:

ALTER RULE [IF EXISTS] name ENABLE | DISABLE | RENAME TO new_name

A disabled rule stays, and checks nothing until ENABLE.

DROP RULE

Syntax:

DROP RULE [IF EXISTS] name [, name ...]

See the rules: SHOW RULES

SHOW RULES is SELECT * FROM kdb_rules: one row per rule.

Example: All rules of the database

Setup:

CREATE TABLE quotes (time TIMESTAMP, symbol SYMBOL, bid DOUBLE, ask DOUBLE) TIMESERIES(time) PARTITION BY DAY;
CREATE RULE spread_ok ON quotes CHECK (ask >= bid) MESSAGE 'ask below bid';

SHOW RULES;

Result:

(1 row)