ALTER, RENAME and DROP TABLE

Add, drop, rename and change columns, drop partitions, set a TTL, add and drop rules and keys, turn DEDUP on or off, rename and drop tables.

• Category: Tables
• Keywords: alter table, change table, add column, new column, drop column, remove column, rename column, COLUMN, TO, drop partition, delete old data, OLDER THAN, DROP PARTITION LIST, DROP PARTITION WHERE, add constraint, add check, drop constraint, rename table, drop table, delete table, remove table, IF EXISTS, IF NOT EXISTS, ALTER COLUMN, SET DATA TYPE, TYPE ... USING, USING, change column type, SET DEFAULT, DROP DEFAULT, SET NOT NULL, DROP NOT NULL, ADD UNIQUE, ADD FOREIGN KEY, FOREIGN KEY, NOT VALID, DEDUP ENABLE, DEDUP DISABLE, DEDUP ENABLE UPSERT KEYS, SET (dedup_insert, RESET (dedup_insert, SET (compression, RESET (compression, compression, SET (resident, RESET (resident, resident, keep in memory, ALTER TABLE RENAME TO, schema change, migration
• See also: CREATE TABLE, Constraints, SHOW TABLES, COLUMNS, CREATE TABLE, UPDATE and DELETE, Triggers, Indexes, Partitions, SORT BY and DEDUP KEY

Tables change when your app grows: a new column for the phone number, a better column name, old data to remove. Most of these statements change a table without copying its rows; changing a column's type and turning DEDUP on or off rewrite the rows.

What you cannot change: PARTITION BY, SORT BY, the kind of table, and the type of the time column, of a SORT BY or DEDUP KEY column and of the PRIMARY KEY. To change those, make a new table with CREATE TABLE ... AS or INSERT INTO new SELECT ... FROM old, and drop the old table. ALTER TABLE ... ENABLE TRIGGER and DISABLE TRIGGER are explained in triggers.

Several actions can go in one statement, with commas: ALTER TABLE t ADD COLUMN a INT, DROP COLUMN b. DROP PARTITION must be on its own. These statements cannot be used inside BEGIN ... COMMIT.

Example: The users table of an app grows

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL UNIQUE);
INSERT INTO users (email) VALUES ('ram@example.com'), ('sita@example.com');

ALTER TABLE users ADD COLUMN phone STRING;
ALTER TABLE users ADD COLUMN verified BOOL NOT NULL DEFAULT FALSE;
ALTER TABLE users RENAME COLUMN email TO login_email;
SELECT id, login_email, phone, verified FROM users;

Result:

ALTER TABLE users ADD COLUMN phone STRING

table 'users' altered

ALTER TABLE users ADD COLUMN verified BOOL NOT NULL DEFAULT FALSE

table 'users' altered

ALTER TABLE users RENAME COLUMN email TO login_email

table 'users' altered

SELECT id, login_email, phone, verified FROM users

(2 rows)

ADD COLUMN

Syntax:

ALTER TABLE table ADD [COLUMN] [IF NOT EXISTS] column TYPE [NOT NULL] [UNIQUE] [DEFAULT expr]
                                          [REFERENCES table(column)] [ROUND interval]
                                          [AS (expr) | GENERATED ALWAYS AS (expr) STORED]
                                          [[CONSTRAINT name] CHECK (condition)]

Adds a new column at the end of the table. The word COLUMN is optional.

• Rows already in the table get the DEFAULT value, or NULL when there is no default.
• A stored generated column (GENERATED ALWAYS AS (...) STORED) is worked out for every row already in the table: the rows are written again, so this takes longer on a big table.
• Returns: table 'name' altered.
• Notes: a NOT NULL column needs a DEFAULT (old rows need a value). You cannot add a PRIMARY KEY column. With IF NOT EXISTS, a column that is already there is not an error. A default like now() or gen_random_uuid() is worked out once for all old rows, so they all get the same value; new rows get their own.
• Notes: INSERT INTO t VALUES (...) without a column list now needs a value for the new column too.

Example: Add a phone number and a verified flag

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL);
INSERT INTO users (email) VALUES ('ram@example.com');

ALTER TABLE users ADD COLUMN phone STRING;
ALTER TABLE users ADD verified BOOL NOT NULL DEFAULT FALSE;
INSERT INTO users (email, phone, verified) VALUES ('sita@example.com', '9800000000', TRUE);
SELECT id, email, phone, verified FROM users;

Result:

ALTER TABLE users ADD COLUMN phone STRING

table 'users' altered

ALTER TABLE users ADD COLUMN verified BOOL NOT NULL DEFAULT FALSE

table 'users' altered

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

1 row(s) inserted

SELECT id, email, phone, verified FROM users

(2 rows)

Example: Add a sector link to securities (REFERENCES)

Setup:

CREATE TABLE sectors (name STRING PRIMARY KEY);
CREATE TABLE securities (symbol SYMBOL PRIMARY KEY, name STRING NOT NULL);
INSERT INTO sectors VALUES ('Banking'), ('Energy');
INSERT INTO securities VALUES ('CITY', 'City Bank Limited');

ALTER TABLE securities ADD COLUMN sector STRING REFERENCES sectors(name);
UPDATE securities SET sector = 'Banking' WHERE symbol = 'CITY';
SELECT symbol, name, sector FROM securities;

Result:

ALTER TABLE securities ADD COLUMN sector STRING REFERENCES sectors(name)

table 'securities' altered

UPDATE securities SET sector = 'Banking' WHERE symbol = 'CITY'

1 row(s) updated

SELECT symbol, name, sector FROM securities

(1 row)

Example: Add a computed column to the trade log

Setup:

CREATE TABLE trade_log (date TIMESTAMP, transaction_id LONG, quantity LONG, rate DOUBLE) TIMESERIES(date) DEDUP KEY (date, transaction_id);
INSERT INTO trade_log VALUES ('2026-09-10', 100010, 50, 517.0);

ALTER TABLE trade_log ADD COLUMN amount DOUBLE AS (quantity * rate);
SELECT transaction_id, quantity, rate, amount FROM trade_log;

Result:

ALTER TABLE trade_log ADD COLUMN amount DOUBLE AS (quantity * rate)

table 'trade_log' altered

SELECT transaction_id, quantity, rate, amount FROM trade_log

(1 row)

Example: NOT NULL without a DEFAULT (shows an error)

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING NOT NULL);

ALTER TABLE users ADD COLUMN verified BOOL NOT NULL;

Result:

Error: cannot add column 'verified' to table 'users': rows already in the table need a value for a NOT NULL column; expected NOT NULL DEFAULT <value>

DROP COLUMN

Syntax:

ALTER TABLE table DROP [COLUMN] [IF EXISTS] column

Removes a column and all its values. The word COLUMN is optional.

These columns cannot be dropped:

• Returns: table 'name' altered.
• Notes: there is no undo. Make a backup first when the data matters (backup). With IF EXISTS, a column that is not there is not an error.

Example: Remove a column nobody uses

Setup:

CREATE TABLE news (id LONG PRIMARY KEY AUTO, title STRING NOT NULL, old_category STRING);
INSERT INTO news (title, old_category) VALUES ('City Bank profit rises 18%', 'banking');

ALTER TABLE news DROP COLUMN old_category;
SELECT * FROM news;

Result:

ALTER TABLE news DROP COLUMN old_category

table 'news' altered

SELECT * FROM news

(1 row)

Example: The symbol is part of the sort order (shows an error)

Setup:

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time);

ALTER TABLE ticks DROP COLUMN symbol;

Result:

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

Example: A computed column still uses it (shows an error)

Setup:

CREATE TABLE trade_log (quantity LONG, rate DOUBLE, amount DOUBLE AS (quantity * rate));

ALTER TABLE trade_log DROP COLUMN rate;

Result:

Error: cannot drop column 'rate' from table 'trade_log': computed column 'amount' uses it in AS (quantity * rate); drop 'amount' first

RENAME COLUMN

Syntax:

ALTER TABLE table RENAME [COLUMN] old_name TO new_name

Gives a column a new name. The rows do not change. The word COLUMN is optional.

• KDB also renames the column inside computed columns, CHECK rules, and REFERENCES of other tables.
• Returns: table 'name' altered.
• Notes: views, saved functions, procedures and triggers that use the old name are not changed; fix them yourself. Your app's SQL must use the new name too.

Example: Rename price to ltp in a live prices table

Setup:

CREATE TABLE live_prices (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time);
INSERT INTO live_prices VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 513.0);

ALTER TABLE live_prices RENAME COLUMN price TO ltp;
SELECT time, symbol, ltp FROM live_prices;

Result:

ALTER TABLE live_prices RENAME COLUMN price TO ltp

table 'live_prices' altered

SELECT time, symbol, ltp FROM live_prices

(1 row)

Example: A REFERENCES of another table follows the new name

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING);
CREATE TABLE trades (id LONG PRIMARY KEY, buyer INT REFERENCES brokers(id));

ALTER TABLE brokers RENAME id TO broker_no;
SHOW COLUMNS FROM trades;

Result:

ALTER TABLE brokers RENAME COLUMN id TO broker_no

table 'brokers' altered

SHOW COLUMNS FROM trades

(2 rows)

Example: The new name is already used (shows an error)

Setup:

CREATE TABLE users (id LONG PRIMARY KEY, email STRING, mail STRING);

ALTER TABLE users RENAME COLUMN mail TO email;

Result:

Error: cannot rename column 'mail' of table 'users' to 'email': the table already has a column named 'email'

ALTER COLUMN

Syntax:

ALTER TABLE table ALTER [COLUMN] column [SET DATA] TYPE type [USING expression]
ALTER TABLE table ALTER [COLUMN] column SET DEFAULT expression
ALTER TABLE table ALTER [COLUMN] column DROP DEFAULT
ALTER TABLE table ALTER [COLUMN] column SET NOT NULL
ALTER TABLE table ALTER [COLUMN] column DROP NOT NULL

Changes one column. These are the PostgreSQL forms, so a PostgreSQL migration script runs.

• Returns: table 'name' altered, with (N row change(s) written) when rows were rewritten, or table 'name' not changed (already as asked).
• Notes:
  • When one stored value does not fit the new type, nothing changes and the message names the value: 12.7 is not a whole number. Write a USING that fits, or fix the rows first.
  • The time column, a SORT BY column and a DEDUP KEY column cannot change type: the rows are stored by them, and KDB refuses. A PRIMARY KEY column can: KDB writes the whole table again in the new key order, in one commit (it takes as long as a copy of the table). A change that makes two keys equal, such as TYPE INT USING round(id), is refused with "(duplicate)".
  • An index on the column is built again for the new type.
  • COLLATE after the type is read and ignored. Write COLLATE in the query instead (ORDER BY).

Example: Ages were saved as text: make them numbers

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING, age STRING);
INSERT INTO users (email, age) VALUES ('ram@example.com', '31'), ('sita@example.com', 'unknown');

ALTER TABLE users ALTER COLUMN age TYPE INT USING CASE WHEN age = 'unknown' THEN NULL ELSE CAST(age AS INT) END;
SELECT id, email, age FROM users ORDER BY id;

Result:

ALTER TABLE users ALTER COLUMN age TYPE INT USING CASE WHEN age = 'unknown' THEN NULL ELSE CAST(age

table 'users' altered (2 row change(s) written)

SELECT id, email, age FROM users ORDER BY id

(2 rows)

Example: A default for new rows, then a NOT NULL rule

Setup:

CREATE TABLE posts (id LONG PRIMARY KEY AUTO, author STRING, lang STRING);
INSERT INTO posts (author, lang) VALUES ('ram', 'en');

ALTER TABLE posts ALTER COLUMN lang SET DEFAULT 'ne';
ALTER TABLE posts ALTER COLUMN author SET NOT NULL;
INSERT INTO posts (author) VALUES ('sita');
SELECT id, author, lang FROM posts ORDER BY id;

Result:

ALTER TABLE posts ALTER COLUMN lang SET DEFAULT 'ne'

table 'posts' altered

ALTER TABLE posts ALTER COLUMN author SET NOT NULL

table 'posts' altered

INSERT INTO posts (author) VALUES ('sita')

1 row(s) inserted

SELECT id, author, lang FROM posts ORDER BY id

(2 rows)

Example: A value that does not fit the new type (shows an error)

Setup:

CREATE TABLE prices (id INT PRIMARY KEY, price DOUBLE);
INSERT INTO prices VALUES (1, 12.7);

ALTER TABLE prices ALTER COLUMN price TYPE INT;

Result:

Error: cannot change column 'price' of table 'prices': a row already there does not fit (column 'price' (INT): 12.7 is not a whole number)

Example: SET NOT NULL while a row has NULL (shows an error)

Setup:

CREATE TABLE users (id LONG PRIMARY KEY AUTO, email STRING);
INSERT INTO users (email) VALUES ('ram@example.com'), (NULL);

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Result:

Error: column 'email' of table 'users' contains NULL values; set them first (UPDATE ... SET email = ... WHERE email IS NULL)

Example: A SORT BY column keeps its type (shows an error)

Setup:

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, qty INT) TIMESERIES(time);

ALTER TABLE ticks ALTER COLUMN symbol TYPE STRING;

Result:

Error: cannot alter column 'symbol' of table 'ticks': it is the TIMESERIES column or part of SORT BY / DEDUP KEY

DROP PARTITION

Syntax:

ALTER TABLE table DROP PARTITION [LIST] 'key' [, 'key' ...]
ALTER TABLE table DROP PARTITION OLDER THAN n HOURS | DAYS | WEEKS | MONTHS | YEARS
ALTER TABLE table DROP PARTITION WHERE time_column < '2026-09-01'

Deletes whole partitions of a time-series table: all rows of that day, month or year. It is much faster than DELETE for old data. OLDER THAN works out the keys itself, by the same rule as a TTL: a partition goes when its last moment is more than that long ago. It is the form for a timer that cleans up on a set day, where a TTL cleans up by itself all the time.

LIST and WHERE are QuestDB's forms. LIST 'a', 'b' is the same as 'a', 'b'. WHERE drops each partition whose whole time range matches the condition:

• The condition may only compare the time column with fixed times: =, <>, <, <=, >, >=, BETWEEN, IN (...), IN '2026-09', joined by AND, OR and NOT. now() and dateadd('d', -30, now()) count as fixed. Anything else (another column, a function of the time column) is an error, and nothing is dropped.
• A partition the condition cuts through is kept whole, because dropping it would delete rows the condition does not name. Delete those rows with DELETE. QuestDB only tests a partition's first moment, so it would drop that partition too.
• Returns (WHERE): N partition(s) of 'table' dropped, then ; kept partition(s) only part of which matches: ... when there are some; no partition of 'table' matches as a whole when nothing goes.
• The partitions it finds are written to the database log by name, so a restart or power cut repeats exactly the same drop.

• The keys are UTC dates, in single quotes.
• Returns: N partition(s) of 'table' dropped, or for OLDER THAN with nothing that old, no partition of 'table' is older than 6 MONTHS.
• Notes: a key that does not exist is an error, and the message lists the keys that do. A relational table and a table with PARTITION BY NONE have no partitions to drop; use DELETE or TRUNCATE.
• Notes: with PARTITION BY YEAR, SYMBOL(col) the key is the time part: '2025' drops every symbol of 2025.
• Notes: it answers at once, even for hundreds of partitions on a busy database. The rows are gone for every query straight away; their files move to the database's _trash a moment later, in the background.

Example: Remove the 2025 daily candles

Setup:

CREATE TABLE ochl_day (time TIMESTAMP ROUND 1d, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY YEAR DEDUP KEY (symbol, time);
INSERT INTO ochl_day VALUES ('2025-12-31', 'CITY', 542.4), ('2026-09-10', 'CITY', 504.9);

SELECT count(*) AS rows_2025 FROM ochl_day WHERE time IN '2025';
ALTER TABLE ochl_day DROP PARTITION '2025';
SELECT time, symbol, close FROM ochl_day;

Result:

SELECT count(*) AS rows_2025 FROM ochl_day WHERE time IN '2025'

(1 row)

ALTER TABLE ochl_day DROP PARTITION '2025'

1 partition(s) of 'ochl_day' dropped

SELECT time, symbol, close FROM ochl_day

(1 row)

Example: Drop what ended more than a year ago (by hand, or from a monthly timer)

Setup:

CREATE TABLE trades_log (time TIMESTAMP, symbol SYMBOL, qty LONG) TIMESERIES(time) PARTITION BY MONTH;
INSERT INTO trades_log VALUES ('2024-01-15', 'CITY', 100), ('2024-02-15', 'CITY', 200), ('2099-01-15', 'CITY', 300);

ALTER TABLE trades_log DROP PARTITION OLDER THAN 1 YEAR;
SELECT time, symbol, qty FROM trades_log;

Result:

ALTER TABLE trades_log DROP PARTITION OLDER THAN 1 YEAR

2 partition(s) of 'trades_log' dropped

SELECT time, symbol, qty FROM trades_log

(1 row)

Example: Drop two days of trade log at once

Setup:

CREATE TABLE trade_log (date TIMESTAMP ROUND 1d, transaction_id LONG, symbol SYMBOL) TIMESERIES(date) PARTITION BY DAY DEDUP KEY (date, transaction_id);
INSERT INTO trade_log VALUES ('2026-09-08', 1, 'CITY'), ('2026-09-09', 2, 'COAST'), ('2026-09-10', 3, 'TELCO');

ALTER TABLE trade_log DROP PARTITION '2026-09-08', '2026-09-09';
SELECT date, transaction_id, symbol FROM trade_log;

Result:

ALTER TABLE trade_log DROP PARTITION '2026-09-08', '2026-09-09'

2 partition(s) of 'trade_log' dropped

SELECT date, transaction_id, symbol FROM trade_log

(1 row)

Example: QuestDB's forms: LIST, and WHERE on the time column

Setup:

CREATE TABLE floorsheet (date TIMESTAMP, symbol SYMBOL, qty LONG) TIMESTAMP(date) PARTITION BY DAY;
INSERT INTO floorsheet VALUES ('2026-09-20T10:00', 'CITY', 1), ('2026-09-21T10:00', 'CITY', 2), ('2026-09-22T10:00', 'CITY', 3), ('2026-09-23T10:00', 'CITY', 4);

ALTER TABLE floorsheet DROP PARTITION LIST '2026-09-20';
ALTER TABLE floorsheet DROP PARTITION WHERE date < '2026-09-22T12:00';
SELECT date, qty FROM floorsheet;

Result:

ALTER TABLE floorsheet DROP PARTITION '2026-09-20'

1 partition(s) of 'floorsheet' dropped

ALTER TABLE floorsheet DROP PARTITION WHERE date < '2026-09-22T12:00'

1 partition(s) of 'floorsheet' dropped; kept partition(s) only part of which matches: 2026-09-22 (use DELETE for those rows)

SELECT date, qty FROM floorsheet

(2 rows)

With LIST, as in QuestDB, a partition that is not there is skipped and the others are still dropped. Without LIST, a partition that is not there is an error and nothing is dropped.

Example: LIST skips a day that has no partition

Setup:

CREATE TABLE floorsheet (date TIMESTAMP, symbol SYMBOL, qty LONG) TIMESTAMP(date) PARTITION BY DAY;
INSERT INTO floorsheet VALUES ('2026-09-20T10:00', 'CITY', 1), ('2026-09-21T10:00', 'CITY', 2);

ALTER TABLE floorsheet DROP PARTITION LIST '2026-09-20', '2000-01-01';
SELECT date, qty FROM floorsheet;

Result:

ALTER TABLE floorsheet DROP PARTITION '2026-09-20', '2000-01-01'

1 partition(s) of 'floorsheet' dropped

SELECT date, qty FROM floorsheet

(1 row)

Example: WHERE takes only the time column (shows an error)

Setup:

CREATE TABLE floorsheet (date TIMESTAMP, symbol SYMBOL, qty LONG) TIMESTAMP(date) PARTITION BY DAY;
INSERT INTO floorsheet VALUES ('2026-09-20T10:00', 'CITY', 1);

ALTER TABLE floorsheet DROP PARTITION WHERE qty > 0;

Result:

Error: DROP PARTITION WHERE compares the time column 'date' with fixed times only (=, <>, <, <=, >, >=, BETWEEN, IN, joined by AND, OR, NOT), as in WHERE date < '2026-09-01'

Example: A month key for a table partitioned by day (shows an error)

Setup:

CREATE TABLE trade_log (date TIMESTAMP, transaction_id LONG) TIMESERIES(date) PARTITION BY DAY;
INSERT INTO trade_log VALUES ('2026-09-10', 1);

ALTER TABLE trade_log DROP PARTITION '2026-09';

Result:

Error: table 'trade_log' has no partition '2026-09'; partitions: 2026-09-10

SET TTL

Syntax:

ALTER TABLE table SET TTL n HOURS | DAYS | WEEKS | MONTHS | YEARS
ALTER TABLE table SET TTL NONE
CREATE TABLE ... TIMESERIES(time) PARTITION BY DAY TTL 90 DAYS

A TTL (time to live) keeps a time-series table small. KDB drops whole partitions whose time ended more than the TTL ago. It never deletes single rows.

• A background thread checks once a minute. It only runs when no statement is writing.
• A partition goes when its last moment is older than the TTL. With TTL 3 DAYS on 2026-09-10 12:00, the day 2026-09-06 goes and 2026-09-07 stays.
• MONTHS and YEARS are calendar months and years.
• Returns: table 'name' drops partitions older than 90 DAYS, or table 'name' keeps its partitions (no TTL) for NONE.
• Notes:
  • Only for time-series tables with PARTITION BY DAY, MONTH or YEAR.
  • SHOW CREATE TABLE shows the TTL.
  • Every drop is written to the audit log (event ttl_drop) and to the database log, like DROP PARTITION.
  • A materialized view keeps its rows when the TTL drops partitions of its table.
  • The TTL is checked every minute. It also runs on a busy database. It locks only a table that has a partition past its TTL, one table at a time, and waits only briefly for it. A table held by a long load or an open transaction is left for the next check, and writers of other tables never wait for it. Writers of the table being trimmed wait while the old partitions go, which is quick.

Example: Keep 90 days of live prices

Setup:

CREATE TABLE live_prices (time TIMESTAMP, symbol SYMBOL, ltp DOUBLE) TIMESERIES(time) PARTITION BY DAY;

ALTER TABLE live_prices SET TTL 90 DAYS;
SHOW CREATE TABLE live_prices;

Result:

ALTER TABLE live_prices SET TTL 90 DAYS

table 'live_prices' drops partitions older than 90 DAYS

SHOW CREATE TABLE live_prices

(1 row)

Example: TTL when the table is made, and taken off again

Setup:

CREATE TABLE candles_1m (time TIMESTAMP, symbol SYMBOL, close DOUBLE) TIMESERIES(time) PARTITION BY MONTH TTL 2 YEARS;

ALTER TABLE candles_1m SET TTL NONE;
SHOW CREATE TABLE candles_1m;

Result:

ALTER TABLE candles_1m SET TTL NONE

table 'candles_1m' keeps its partitions (no TTL)

SHOW CREATE TABLE candles_1m

(1 row)

Example: A relational table has no partitions to drop (shows an error)

Setup:

CREATE TABLE notes (id LONG PRIMARY KEY, body STRING);

ALTER TABLE notes SET TTL 30 DAYS;

Result:

Error: TTL drops whole time partitions, so it needs a time-series table with PARTITION BY DAY, MONTH or YEAR; table 'notes' has none

DEDUP ENABLE and DEDUP DISABLE

Syntax:

ALTER TABLE table DEDUP ENABLE UPSERT KEYS (column, ..., time_column)
ALTER TABLE table DEDUP DISABLE

Sets or removes the DEDUP KEY of a time-series table. It is QuestDB's statement; UPSERT may be left out.

• DEDUP ENABLE rewrites the rows by the new key. Rows that already share a key are folded: the newest of each key stays.
• DEDUP DISABLE takes the key off. From then on every row is kept, also exact copies.
• Returns: table 't' now dedups on (time, symbol) (N row(s) rewritten, M duplicate row(s) folded: the newest row of each key was kept), or table 't' no longer dedups (N row(s) rewritten).
• Notes:
  • The key must include the time column, as in CREATE TABLE. With SYMBOL(col) partitions it must include that column too.
  • It reads and rewrites every row, so on a big table it takes a while. Folded rows cannot be brought back: make a backup first when they matter.
  • Only for time-series tables. A relational table uses a PRIMARY KEY.
  • This statement leaves the table's dedup_insert as it was: on a table that did not have it, a plain INSERT of a key that is there still fails. To make it replace the row, as QuestDB does, add ALTER TABLE t SET (dedup_insert = 'upsert'). (A table made with CREATE TABLE ... DEDUP UPSERT KEYS (...) has it from the start.)

Example: A tick table loaded twice: turn DEDUP on and the copies fold

Setup:

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), ('2026-09-10T11:01:00+05:45', 'CITY', 511);
INSERT INTO ticks VALUES ('2026-09-10T11:00:00+05:45', 'CITY', 510.5);

ALTER TABLE ticks DEDUP ENABLE UPSERT KEYS (time, symbol);
SELECT time, symbol, price FROM ticks;

Result:

ALTER TABLE ticks DEDUP ENABLE UPSERT KEYS (time, symbol)

table 'ticks' now dedups on (time, symbol) (2 row(s) rewritten, 1 duplicate row(s) folded: the newest row of each key was kept)

SELECT time, symbol, price FROM ticks

(2 rows)

Example: The key needs the time column (shows an error)

Setup:

CREATE TABLE ticks (time TIMESTAMP, symbol SYMBOL, price DOUBLE) TIMESERIES(time);

ALTER TABLE ticks DEDUP ENABLE UPSERT KEYS (symbol);

Result:

Error: DEDUP UPSERT KEYS must include the time column 'time' of table 'ticks', or a stored column GENERATED ALWAYS AS (date_trunc('minute', time)) STORED

ADD CONSTRAINT ... CHECK

Syntax:

ALTER TABLE table ADD [CONSTRAINT name] CHECK (condition)

Adds a rule that every row must follow. KDB first reads every row: if one row already breaks the rule, the rule is refused and the message names that row.

• Without CONSTRAINT name, KDB makes the name table_check (or table_check1, ...).
• Returns: table 'name' altered.
• Notes: on a very big time-series table this reads all rows once, so it can take a while. The rules for the condition are in CHECK.

Example: No negative balances from now on: the rule stops a bad UPDATE (shows an error)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY AUTO, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL DEFAULT 0);
INSERT INTO accounts (owner, balance) VALUES ('Ram', 1000.00), ('Sita', 0);

ALTER TABLE accounts ADD CONSTRAINT balance_not_negative CHECK (balance >= 0);
UPDATE accounts SET balance = balance - 50 WHERE owner = 'Sita';

Result:

table 'accounts' altered

Error: CHECK constraint 'balance_not_negative' of table 'accounts' failed: balance >= 0 is false for the row id='2', balance='-50.00'

Example: A row already breaks the rule (shows an error)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY AUTO, owner STRING NOT NULL, balance DECIMAL(2) NOT NULL DEFAULT 0);
INSERT INTO accounts (owner, balance) VALUES ('Ram', -20.00);

ALTER TABLE accounts ADD CONSTRAINT balance_not_negative CHECK (balance >= 0);

Result:

Error: cannot add the constraint: a row already in table 'accounts' breaks it (CHECK constraint 'balance_not_negative' of table 'accounts' failed: balance >= 0 is false for the row id='1', balance='-20.00'); fix or delete such rows first

ADD UNIQUE, ADD FOREIGN KEY

Syntax:

ALTER TABLE table ADD [CONSTRAINT name] UNIQUE (column [, column ...])
ALTER TABLE table ADD [CONSTRAINT name] FOREIGN KEY (column [, ...]) REFERENCES other (column [, ...])
                      [ON DELETE action] [ON UPDATE action] [NOT VALID]

Adds a key to a table that already has rows, the PostgreSQL way. KDB first reads every row.

• Returns: table 'name' altered.
• Notes:
  • Without CONSTRAINT name, KDB makes the name: trades_ref_key, trades_buyer_fkey. SHOW CREATE TABLE shows it, and DROP CONSTRAINT removes it.
  • A FOREIGN KEY must point at the PRIMARY KEY or a UNIQUE column of the other table, of the same kind of type. The ON DELETE actions are in REFERENCES.
  • When a row breaks the rule, nothing changes and the message names the row.

Example: Trades point at brokers from now on

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);
CREATE TABLE trades (id LONG PRIMARY KEY, buyer INT, qty LONG);
INSERT INTO brokers VALUES (1, 'Keystone Securities'), (45, 'Imperial Securities');
INSERT INTO trades VALUES (1, 1, 10), (2, 45, 20);

ALTER TABLE trades ADD CONSTRAINT trades_buyer_fk FOREIGN KEY (buyer) REFERENCES brokers(id);
ALTER TABLE brokers ADD CONSTRAINT brokers_name_key UNIQUE (name);
SHOW CREATE TABLE trades;

Result:

ALTER TABLE trades ADD CONSTRAINT trades_buyer_fk FOREIGN KEY (buyer) REFERENCES brokers (id)

table 'trades' altered

ALTER TABLE brokers ADD CONSTRAINT brokers_name_key UNIQUE (name)

table 'brokers' altered

SHOW CREATE TABLE trades

(1 row)

Example: Old rows break the rule: NOT VALID checks only new rows (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING NOT NULL);
CREATE TABLE trades (id LONG PRIMARY KEY, buyer INT);
INSERT INTO brokers VALUES (1, 'Keystone Securities');
INSERT INTO trades VALUES (1, 1), (2, 77);

ALTER TABLE trades ADD CONSTRAINT trades_buyer_fk FOREIGN KEY (buyer) REFERENCES brokers(id) NOT VALID;
INSERT INTO trades VALUES (3, 99);

Result:

table 'trades' altered

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

Example: Two rows already have the same value (shows an error)

Setup:

CREATE TABLE users (id LONG PRIMARY KEY, email STRING);
INSERT INTO users VALUES (1, 'ram@example.com'), (2, 'ram@example.com');

ALTER TABLE users ADD UNIQUE (email);

Result:

Error: cannot add constraint 'users_email_key': two rows of table 'users' have email='ram@example.com' (duplicate)

DROP CONSTRAINT

Syntax:

ALTER TABLE table DROP CONSTRAINT name

Removes a CHECK rule, a UNIQUE key or a FOREIGN KEY by its name. SHOW CREATE TABLE shows the names, also the names KDB made for you (like accounts_balance_check). The PRIMARY KEY cannot be dropped: the rows are stored by it.

• Returns: table 'name' altered.
• Notes: a name that does not exist is an error; the message lists the names the table has. A column called constraint is dropped with ALTER TABLE t DROP constraint (no name after it).

Example: Remove a limit that is too strict

Setup:

CREATE TABLE ledger (id LONG PRIMARY KEY AUTO, amount DECIMAL(2) NOT NULL, CONSTRAINT max_amount CHECK (amount <= 100000));

ALTER TABLE ledger DROP CONSTRAINT max_amount;
INSERT INTO ledger (amount) VALUES (250000.00);
SELECT id, amount FROM ledger;

Result:

ALTER TABLE ledger DROP CONSTRAINT max_amount

table 'ledger' altered

INSERT INTO ledger (amount) VALUES (250000.00)

1 row(s) inserted

SELECT id, amount FROM ledger

(1 row)

Example: A wrong constraint name (shows an error)

Setup:

CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(2) CHECK (balance >= 0));

ALTER TABLE accounts DROP CONSTRAINT balance_check;

Result:

Error: table 'accounts' has no constraint 'balance_check' (it has: accounts_pkey, accounts_balance_check)

RENAME TABLE

Syntax:

RENAME TABLE old_name TO new_name

Gives a table a new name. REFERENCES in other tables follow the new name.

• Returns: table 'old' renamed to 'new'.
• Notes: refused when a table with the new name exists, and while the table has triggers (drop them first). Views, procedures and triggers that use the old name are not changed. The PostgreSQL form ALTER TABLE a RENAME TO b works the same.

Example: Rename a table; the REFERENCES follows

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING);
CREATE TABLE trades (id LONG PRIMARY KEY, buyer INT REFERENCES brokers(id));

RENAME TABLE brokers TO members;
SHOW COLUMNS FROM trades;

Result:

RENAME TABLE brokers TO members

table 'brokers' renamed to 'members'

SHOW COLUMNS FROM trades

(2 rows)

Example: The new name is taken (shows an error)

Setup:

CREATE TABLE posts (id LONG PRIMARY KEY);
CREATE TABLE posts_old (id LONG PRIMARY KEY);

RENAME TABLE posts TO posts_old;

Result:

Error: cannot rename table 'posts' to 'posts_old': a table named 'posts_old' already exists

DROP TABLE

Syntax:

DROP TABLE [IF EXISTS] table [CASCADE | RESTRICT]

Deletes a table with all its rows and its indexes. With IF EXISTS, a table that is not there is not an error.

• Returns: table 'name' dropped, or table 'name' does not exist with IF EXISTS.
• Notes: the table goes to the trash for some days, and UNDROP TABLE brings it back. It answers at once: it waits only for the statements running in the database at that moment, not for its files to be written or moved. Refused while another table REFERENCES it, and while it has triggers. To remove all rows and keep the table, use TRUNCATE.
• CASCADE and RESTRICT: PostgreSQL writes DROP TABLE t CASCADE (drop what depends on it too) or DROP TABLE t RESTRICT (refuse while something depends on it). KDB always works like RESTRICT: it never drops anything but the table.
  • RESTRICT is read and changes nothing.
  • CASCADE works when nothing uses the table, so a PostgreSQL script with DROP TABLE IF EXISTS t CASCADE runs.
  • CASCADE is refused while a view, a materialized view or another table's foreign key uses the table: DROP TABLE ... CASCADE is not supported (PostgreSQL 0A000). The message names what uses it. Drop those first, then the table.
  • Without CASCADE, a plain view that reads the table does not stop the drop: the view stays and fails when it is read (see views).
  • DROP FUNCTION, DROP PROCEDURE and DROP TRIGGER read both words and ignore them.

Example: Remove a test table; safe to run twice

Setup:

CREATE TABLE test_ticks (time TIMESTAMP, price DOUBLE) TIMESERIES(time);

DROP TABLE test_ticks;
DROP TABLE IF EXISTS test_ticks;
SHOW TABLES;

Result:

DROP TABLE test_ticks

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

DROP TABLE IF EXISTS test_ticks

table 'test_ticks' does not exist

SHOW TABLES

(0 rows)

Example: Brokers are still used by the trades (shows an error)

Setup:

CREATE TABLE brokers (id INT PRIMARY KEY, name STRING);
CREATE TABLE trades (id LONG PRIMARY KEY, buyer INT REFERENCES brokers(id));

DROP TABLE brokers;

Result:

Error: cannot drop table 'brokers': column 'buyer' of table 'trades' REFERENCES it; drop that column or table first

Example: A table that is not there (shows an error)

DROP TABLE nothing;

Result:

Error: cannot drop table 'nothing': it does not exist

Example: CASCADE and RESTRICT on tables nothing uses

Setup:

CREATE TABLE test_a (id INT PRIMARY KEY);
CREATE TABLE test_b (id INT PRIMARY KEY);

DROP TABLE test_a CASCADE;
DROP TABLE test_b RESTRICT;
SHOW TABLES;

Result:

DROP TABLE test_a

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

DROP TABLE test_b

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

SHOW TABLES

(0 rows)

Example: CASCADE would have to drop a view: refused (shows an error)

Setup:

CREATE TABLE test_prices (symbol SYMBOL, price DOUBLE);
CREATE VIEW test_expensive AS SELECT symbol FROM test_prices WHERE price > 1000;

DROP TABLE test_prices CASCADE;

Result:

Error: DROP TABLE ... CASCADE is not supported: it would not drop view 'test_expensive', which uses table 'test_prices'. Drop the dependent views first (DROP VIEW test_expensive), then DROP TABLE test_prices

SET (resident = on): keep a table in memory

Syntax:

ALTER TABLE <t> SET (resident = on | off)
ALTER TABLE <t> RESET (resident)
CREATE TABLE <t> (...) WITH (resident = on)

A table an app reads on every request (sessions, users, symbols) should not be read from disk again because another query needed memory. With resident = on, its partitions are never unloaded once a read has loaded them: not for a big scan of the same database, not for another database, not for the process memory limit.

• Returns: table 't' stays in memory once loaded (resident = on); it counts against the database's MEMORY_LIMIT.
• Notes:
  • It loads nothing by itself: the first read that needs the rows loads them.
  • What it holds still counts in --memory and in the database's MEMORY_LIMIT, so keep it for small and medium tables.
  • GET /api/stats shows what resident tables hold as resident_bytes in each database's memory.
  • A database with a resident table needs KDB 2026-10-02 or newer; RESET (resident) lets an older KDB open it again.

Example: The sessions table of an app stays loaded

Setup:

CREATE TABLE sessions (token STRING PRIMARY KEY, user_id LONG);

ALTER TABLE sessions SET (resident = on);

Result:

table 'sessions' stays in memory once loaded (resident = on); it counts against the database's MEMORY_LIMIT