Skip to main content
SereneDBSereneDB
897Contact Us

ALTER TABLE

The ALTER TABLE statement changes the schema of an existing table in the catalog.

RENAME TABLE​

SQL
ALTER TABLE integersRENAME TO integers_old;

The RENAME TO clause renames an entire table, changing its name in the schema. Indexes follow the rename, except in an attached DuckDB file, where a table that has an index can't be renamed. A table that a view, a function or another table's DEFAULT, CHECK or generated column uses can't be renamed: the error lists those dependents, which have to be dropped first and recreated afterwards.

RENAME COLUMN​

To rename a column of a table, use the RENAME or RENAME COLUMN clauses:

SQL
ALTER TABLE integersRENAME COLUMN i TO j;
SQL
ALTER TABLE integersRENAME i TO j;

The RENAME [COLUMN] clause renames a single column within a table. Constraints and indexes that use the column are updated automatically; in an attached DuckDB file, a table that has an index can't rename its columns. A column that a view or a table function reads can't be renamed, and neither can its type be changed; the error lists the dependents. A column that no dependent reads can be renamed or retyped freely.

ADD COLUMN​

To add a column of a table, use the ADD or ADD COLUMN clauses.

E.g., to add a new column with name k to the table integers, it will be filled with the default value NULL:

SQL
ALTER TABLE integersADD COLUMN k INTEGER;

Or:

SQL
ALTER TABLE integersADD k INTEGER;

Add a new column with name l to the table integers, it will be filled with the default value 10:

SQL
ALTER TABLE integersADD COLUMN l INTEGER DEFAULT 10;

The ADD [COLUMN] clause can be used to add a new column of a specified type to a table. The new column will be filled with the specified default value, or NULL if none is specified. Besides DEFAULT, the new column can declare UNIQUE or PRIMARY KEY, and the constraint is enforced from the start. A PRIMARY KEY is also checked against the rows already in the table, which all get the default value: adding id INT DEFAULT 5 PRIMARY KEY to a table of two rows fails because the key contains duplicates. An inline CHECK constraint and a named constraint (CONSTRAINT ⟨name⟩ UNIQUE) are not supported; add them with ADD CONSTRAINT, and NOT NULL with ALTER COLUMN ... SET NOT NULL, in a separate ALTER TABLE step.

DROP COLUMN​

To drop a column of a table, use the DROP or DROP COLUMN clause:

E.g., to drop the column k from the table integers:

SQL
ALTER TABLE integersDROP COLUMN k;

Or:

SQL
ALTER TABLE integersDROP k;

The DROP [COLUMN] clause can be used to remove a column from a table. As in PostgreSQL, every index created with CREATE INDEX that uses the column (as a key, in an indexed expression or in its WHERE predicate) is dropped along with it. A column cannot be removed while an index created as part of a PRIMARY KEY or UNIQUE constraint relies on it, or while a remaining plain index (not an inverted index) uses a column that comes after it. Columns that are part of multi-column check constraints cannot be dropped either. In those cases SereneDB returns a Catalog Error reporting that an index or constraint depends on the column.

[SET [DATA]] TYPE​

Change the type of the column i to the type VARCHAR using a standard cast:

SQL
ALTER TABLE integersALTER i TYPE VARCHAR;

Change the type of the column i to the type VARCHAR, using the specified expression to convert the data for each row:

SQL
ALTER TABLE integersALTER i SET DATA TYPE VARCHAR USING concat(i, '_', j);

The [SET [DATA]] TYPE clause changes the type of a column in a table. Any data present in the column is converted according to the provided expression in the USING clause, or, if the USING clause is absent, cast to the new data type. Note that columns can only have their type changed if they do not have any indexes that rely on them and are not part of any CHECK constraints.

Handling Structs​

There are two options to change the sub-schema of a STRUCT-typed column.

ALTER TABLE with struct_insert​

You can add fields to a STRUCT column with ALTER TABLE: give the new struct type in the TYPE clause and use struct_insert in the USING clause to transform the existing values. For example:

SQL
CREATE TABLE tbl (col STRUCT(i INTEGER));
INSERT INTO tbl VALUES ({'i': 1});
ALTER TABLE tblALTER col TYPE STRUCT(i INTEGER, a INTEGER, b VARCHAR)USING struct_insert(col, a := 42, b := NULL::VARCHAR);
SELECT col FROM tbl;
 col
---------
 (1,42,)

ALTER TABLE with ADD COLUMN / DROP COLUMN / RENAME COLUMN​

SereneDB ALTER TABLE supports the ADD COLUMN, DROP COLUMN and RENAME COLUMN clauses to update the sub-schema of a STRUCT.

SET / DROP DEFAULT​

The SET DEFAULT clause changes the default value of a column:

SQL
ALTER TABLE integersALTER COLUMN i SET DEFAULT 10;

The DROP DEFAULT clause removes the default value of a column, resetting it to NULL:

SQL
ALTER TABLE integersALTER COLUMN i DROP DEFAULT;

ADD PRIMARY KEY​

The ADD PRIMARY KEY clause promotes one or more existing columns to the table's primary key. The chosen columns are made implicitly NOT NULL, and the constraint is enforced from that point on:

SQL
ALTER TABLE integersADD PRIMARY KEY (i);

A primary key can also span multiple columns:

SQL
ALTER TABLE integersADD PRIMARY KEY (i, j);

The statement fails if the table already has a primary key, if an index depends on the table or if the existing data would violate the new constraint (duplicate or NULL values in the key columns).

SET / RESET (Table Options)​

Attempting to set table options returns an error:

SQL
ALTER TABLE my_tableSET ('option_name' = 'value');
db error: ERROR: SET (<options>) is not supported

Attempting to reset table options returns an error:

SQL
ALTER TABLE my_tableRESET ('option_name');
db error: ERROR: RESET (<options>) is not supported

Attempting to set or reset multiple options in a single statement returns an error:

SQL
ALTER TABLE my_tableSET ('option1' = 'value1', 'option2' = 'value2');
ALTER TABLE my_tableRESET ('option1', 'option2');
db error: ERROR: SET (<options>) is not supported

db error: ERROR: RESET (<options>) is not supported

DROP CONSTRAINT​

The DROP CONSTRAINT clause removes a named CHECK constraint from a table:

SQL
ALTER TABLE itemsDROP CONSTRAINT positive_id;

DROP CONSTRAINT (and RENAME CONSTRAINT) operate on named CHECK constraints. Index-backed constraints — those created by PRIMARY KEY and UNIQUE — cannot be dropped this way.

ADD CONSTRAINT​

The ADD CONSTRAINT clause adds a CHECK, UNIQUE or PRIMARY KEY constraint to an existing table:

SQL
ALTER TABLE itemsADD CONSTRAINT positive_id CHECK (id > 0);

FOREIGN KEY constraints cannot be added with ADD CONSTRAINT.

SET / RESET storage options​

For a table created with WITH (storage = 'search'), SET (option = value, …) changes the background maintenance options it was created with, and RESET (option, …) returns them to the current session defaults. These options can be changed: refresh_interval, compaction_interval, cleanup_interval_step, compaction_max_segments, compaction_max_segments_bytes and compaction_floor_segment_bytes (see Background compaction). A change reaches the table's background tasks at once and is undone if its transaction rolls back. row_group_size, segment_memory_max and optimize_top_k are fixed at CREATE TABLE. The current values are listed in pg_class.reloptions.

SQL
ALTER TABLE events SET (compaction_interval = 0);
ALTER TABLE events RESET (compaction_interval);

SET and RESET of storage options are supported only for search tables.

ENABLE / DISABLE TRIGGER​

DISABLE TRIGGER name stops a trigger of the table from firing, and ENABLE TRIGGER name lets it fire again. With ALL or USER instead of a name the change applies to every trigger of the table. Like in PostgreSQL, a trigger can also be enabled for a replication role: ENABLE REPLICA TRIGGER makes it fire only in sessions where session_replication_role is replica, such as the apply worker of a subscription, and ENABLE ALWAYS TRIGGER makes it fire in every session. A plain ENABLE TRIGGER trigger fires when the role is origin (the default) or local. pg_trigger.tgenabled shows the state as O, D, R or A. The change is transactional.

SQL
ALTER TABLE orders DISABLE TRIGGER orders_audited;
ALTER TABLE orders ENABLE ALWAYS TRIGGER orders_audited;
SELECT tgname, tgenabled FROM pg_trigger WHERE tgrelid = 'orders'::regclass;
 tgname         | tgenabled
----------------+-----------
 orders_audited | A

Foreign keys are not triggers in SereneDB, so DISABLE TRIGGER ALL does not turn off foreign key checks; a session with session_replication_role set to replica skips them.

Search tables​

A search table's columns are fixed: ADD COLUMN, DROP COLUMN, ALTER COLUMN TYPE, DROP CONSTRAINT and adding a PRIMARY KEY or UNIQUE constraint are rejected. Renaming the table or a column, ALTER COLUMN SET DEFAULT / DROP DEFAULT, SET NOT NULL / DROP NOT NULL, adding a CHECK constraint and COMMENT ON COLUMN change only the table's definition. A search table does not check NOT NULL and CHECK constraints when rows are written, whether they were declared at CREATE TABLE or added later.

Limitations​

ALTER COLUMN fails if values of conflicting types have occurred in the table at any point, even if they have been deleted:

SQL
CREATE TABLE tbl (col VARCHAR);
INSERT INTO tblVALUES ('asdf'), ('42');
DELETE FROM tblWHERE col = 'asdf';
ALTER TABLE tblALTER COLUMN col TYPE INTEGER;
db error: ERROR: Could not convert string 'asdf' to INT32

Currently, this is expected behavior. As a workaround, you can create a copy of the table:

SQL
CREATE TABLE tbl_copy AS FROM tbl;
Edit this page ↗