ALTER TABLE - Kinetica Docs

Alters the configuration of a table. Any of the following facets of a table can be altered:

Rename Table

A table can be renamed, following the supported naming criteria.

Rename Table Syntax

ALTER TABLE [<schema name>.]<table name>
RENAME TO <new table name>

All dependent views, materialized views, streams, and SQL procedures will be dropped.

Any tables with foreign keys that target this table must be dropped before it can be renamed.

Move Table

A table can be moved from one schema to another.

Move Table Syntax

ALTER TABLE [<schema name>.]<table name>
< MOVE TO | SET SCHEMA > <other schema name>

All dependent views, materialized views, streams, and SQL procedures will be dropped.

Any tables with foreign keys that target this table must be dropped before it can be moved.

For example, to move the sales_2017 table from the example_olap schema to the example_archive schema:

Move Table Example

ALTER TABLE example_olap.sales_2017
MOVE TO example_archive

Set Access Mode

A table can have its global accessibility modified for all users in the system, independently from and further restricting any role-based access controls in place. Note that changing the access mode cannot widen access for users not already granted access; it can only narrow access for those who already have access. This setting will also trump administrative access to a table.

Set Table Access Mode Syntax

ALTER TABLE [<schema name>.]<table name>
SET ACCESS MODE < NO_ACCESS | READ_ONLY | WRITE_ONLY | READ_WRITE >

Set TTL

A table’s time-to-live (TTL) can be altered.

Set Table TTL Syntax

ALTER TABLE [<schema name>.]<table name>
SET TTL <new ttl>

For example, to set a TTL of 7 minutes on a table:

Set Table TTL Example

ALTER TABLE example.employee
SET TTL 7

To set a table to never expire by TTL timeout:

Set Table No Expiration Example

ALTER TABLE example.employee
SET TTL -1

Add Column

A column can be added, specifying a column definition.

Add Table Column Syntax

ALTER TABLE [<schema name>.]<table name>
ADD <column name> <column definition> [DEFAULT <string/numeric constant | column name>]

A new column can have its values initially populated through the use of the DEFAULT keyword. These values can either be a string/numeric constant or the name of an existing column in the table from which values can be copied into the new column. This default value is only in effect for the column creation; the new column will have no default value after that. Examples To add, to the employee table, a salary column that is a non-nullable, 10-digit number field containing 2 decimal places with a default value of 0:

Add Table Column (Numeric) Example

ALTER TABLE example.employee
ADD salary NUMERIC(10, 2) NOT NULL DEFAULT 0

To add, to the employee table, a category column that is a nullable, dictionary-encoded, 32-character text field:

Add Table Column (CharN/Dictionary-Encoded) Example

ALTER TABLE example.employee
ADD category VARCHAR(32, DICT)

To add, to the employee table, a bio column that is a nullable, text-searchable, unrestricted-width text field:

Add Table Column (String/Text-Searchable) Example

ALTER TABLE example.employee
ADD bio VARCHAR(TEXT_SEARCH)

Rename Column

An existing column can be renamed.

Rename Table Column Syntax

ALTER TABLE [<schema name>.]<table name>
RENAME COLUMN <column current name> TO <column new name>

All dependent views, materialized views, streams, and SQL procedures will be dropped.

Any tables with foreign keys that target the column being renamed must be dropped before it can be renamed.

Rename Table Column Example

ALTER TABLE example.employee
RENAME COLUMN bio TO biography

Modify Column

A column can have its column definition modified, affecting column type, column size, column properties, and nullability.

Modify Table Column Syntax

ALTER TABLE [<schema name>.]<table name>
MODIFY [COLUMN] <column name> <column definition>

Modify Table Column Alternate Syntax

ALTER TABLE [<schema name>.]<table name>
ALTER COLUMN <column name> <column definition>

All dependent views, materialized views, streams, and SQL procedures will be dropped.

Any tables with foreign keys that target the column being modified must be dropped before it can be modified.

If a column is modified to be non-nullable, it will be populated with default values—empty string for string fields and 0 for numeric fields. Examples To change, in the employee table, the first_name column to one that is a non-nullable, dictionary-encoded, 50-character text field:

Modify Table Column Example

ALTER TABLE example.employee
ALTER COLUMN first_name VARCHAR(50, DICT) NOT NULL

Drop Column

An existing column can be removed from a table.

Drop Table Column Syntax

ALTER TABLE [<schema name>.]<table name>
DROP COLUMN <column name>

All dependent views, materialized views, streams, and SQL procedures will be dropped.

Any tables with foreign keys that target the column being dropped must be dropped before it can be dropped.

Add Column Index

A column (attribute) index can be added to a table column in order to improve the performance of operations whose expressions contain relational operators against the column. See Limitations for restrictions.

Add Table Column Index Syntax

ALTER TABLE [<schema name>.]<table name>
ADD INDEX (<column name>)

For example, to index the employee table’s last_name column:

Add Table Column Index Example

ALTER TABLE example.employee
ADD INDEX (last_name)

Drop Column Index

An existing column (attribute) index can be removed from a table.

Drop Table Column Index Syntax

ALTER TABLE [<schema name>.]<table name>
DROP INDEX (<column name>)

For example, to drop the index on the employee table’s last_name column:

Drop Table Column Index Example

ALTER TABLE example.employee
DROP INDEX (last_name)

Add Low-Cardinality Index

A low-cardinality index can be added to a table column with few distinct values in order to improve the performance of operations whose expressions contain relational operators against the column. See Limitations for restrictions.

Add Table Low-Cardinality Index Syntax

ALTER TABLE [<schema name>.]<table name>
ADD LOW CARDINALITY INDEX (<column name>)

For example, to index the employee table’s dept_id column:

Add Table Low-Cardinality Index Example

ALTER TABLE example.employee
ADD LOW CARDINALITY INDEX (dept_id)

Drop Low-Cardinality Index

An existing low-cardinality index can be removed from a table.

Drop Table Low-Cardinality Index Syntax

ALTER TABLE [<schema name>.]<table name>
DROP LOW CARDINALITY INDEX (<column name>)

For example, to drop the index on the employee table’s dept_id column:

Drop Table Low-Cardinality Index Example

ALTER TABLE example.employee
DROP LOW CARDINALITY INDEX (dept_id)

Add Chunk Skip Index

A chunk skip index can be added to a table column in order to improve the performance of operations containing equality-based filters against the column. See Limitations for restrictions.

Add Table Chunk Skip Index Syntax

ALTER TABLE [<schema name>.]<table name>
ADD CHUNK [SKIP] INDEX (<column name>)

For example, to index the employee table’s employee ID column:

Add Table Chunk Skip Index Example

ALTER TABLE example.employee
ADD CHUNK SKIP INDEX (id)

Drop Chunk Skip Index

An existing chunk skip index can be removed from a table.

Drop Table Chunk Skip Index Syntax

ALTER TABLE [<schema name>.]<table name>
DROP CHUNK [SKIP] INDEX (<column name>)

For example, to drop the chunk skip index on the employee table’s employee ID column:

Drop Table Chunk Skip Index Example

ALTER TABLE example.employee
DROP CHUNK SKIP INDEX (id)

Add Geospatial Index

A geospatial index can be added to one or more table columns to improve the performance of geospatial functions applied to them.

Add Table Geospatial Index Syntax

ALTER TABLE [<schema name>.]<table name>
ADD GEOSPATIAL INDEX (<column name>[,...])

For example, to index the employee table’s work district WKT column:

Add Table WKT Geospatial Index Example

ALTER TABLE example.employee
ADD GEOSPATIAL INDEX (work_district)

To index the employee table’s office location coordinate pair columns:

Add Table Coordinate Pair Geospatial Index Example

ALTER TABLE example.employee
ADD GEOSPATIAL INDEX (office_longitude, office_latitude)

Drop Geospatial Index

An existing geospatial index can be removed from a table.

Drop Table Geospatial Index Syntax

ALTER TABLE [<schema name>.]<table name>
DROP GEOSPATIAL INDEX (<column name>[,...])

For example, to drop the geospatial index on the employee table’s work district WKT column:

Drop Table WKT Geospatial Index Example

ALTER TABLE example.employee
DROP GEOSPATIAL INDEX (work_district)

To drop the geospatial index on the employee table’s office location coordinate pair columns:

Drop Table Coordinate Pair Geospatial Index Example

ALTER TABLE example.employee
DROP GEOSPATIAL INDEX (office_longitude, office_latitude)

Add CAGRA Index

A CAGRA index can be added to a table column in order to improve the performance of vector searches applied to the column.

Add Table CAGRA Index Syntax

ALTER TABLE [<schema name>.]<table name>
ADD CAGRA INDEX (<column name>)

For example, to add a CAGRA index on the employee table’s profile column:

Add Table CAGRA Index Example

ALTER TABLE example.employee
ADD CAGRA INDEX (profile)

Refresh CAGRA Index

An existing CAGRA index can be refreshed.

Refresh Table CAGRA Index Syntax

ALTER TABLE [<schema name>.]<table name>
REFRESH CAGRA INDEX ON (<column name>)

For example, to refresh the CAGRA index on the employee table’s profile column:

Refresh Table CAGRA Index Example

ALTER TABLE example.employee
REFRESH CAGRA INDEX ON (profile)

Drop CAGRA Index

An existing CAGRA index can be removed from a table.

Drop Table CAGRA Index Syntax

ALTER TABLE [<schema name>.]<table name>
DROP CAGRA INDEX (<column name>)

For example, to drop the CAGRA index on the employee table’s profile column:

Drop Table CAGRA Index Example

ALTER TABLE example.employee
DROP CAGRA INDEX (profile)

Add HNSW Index

An HNSW index can be added to a table column in order to improve the performance of vector searches applied to the column.

Add Table HNSW Index Syntax

ALTER TABLE [<schema name>.]<table name>
ADD HNSW INDEX (<column name>)

For example, add an HNSW index on the employee table’s profile column:

Add Table HNSW Index Example

ALTER TABLE example.employee
ADD HNSW INDEX (profile)

Drop HNSW Index

An existing HNSW index can be removed from a table.

Drop Table HNSW Index Syntax

ALTER TABLE [<schema name>.]<table name>
DROP HNSW INDEX (<column name>)

For example, to drop the HNSW index on the employee table’s profile column:

Drop Table HNSW Index Example

ALTER TABLE example.employee
DROP HNSW INDEX (profile)

Add Foreign Key

A foreign key can be added to any column or set of columns with primary key applicable types in order to improve the performance of join operations between the table being altered and the table referenced in the foreign key.

Add Table Foreign Key Syntax

ALTER TABLE [<schema name>.]<table name>
ADD FOREIGN KEY (<column name>,...)
    REFERENCES [<foreign table schema name>.]<foreign table name>(<foreign column name>,...) [AS <foreign key name>]

For example, to add a foreign key on the employee table’s department ID column, linking it to the department table’s department ID column:

Add Table Foreign Key Example

ALTER TABLE example.employee
ADD FOREIGN KEY (dept_id) REFERENCES example.department(id) AS fk_emp_dept

Drop Foreign Key

An existing foreign key can be removed from a table, either by the name (alias) given to it during creation or by its definition:

Drop Table Foreign Key by Name Syntax

ALTER TABLE [<schema name>.]<table name>
DROP FOREIGN KEY <foreign key name>

Drop Table Foreign Key by Definition Syntax

ALTER TABLE [<schema name>.]<table name>
DROP FOREIGN KEY (<column name>,...)
    REFERENCES [<foreign table schema name>.]<foreign table name>(<foreign column name>,...)

For example, to drop the foreign key on the employee table’s department ID column:

Drop Table Foreign Key by Name Example

ALTER TABLE example.employee
DROP FOREIGN KEY fk_emp_dept

Drop Table Foreign Key by Definition Example

ALTER TABLE example.employee
DROP FOREIGN KEY (dept_id) REFERENCES example.department(id)

Add Partition

A partition can be added to a range-partitioned or list-partitioned table.

Defining (adding) partitions after data has been loaded will result in a performance penalty as the database moves existing records targeted for the new partition from the default partition into it.

Range Partition

The new partition can be given a minimum bound (inclusive) and a maximum bound (exclusive). If the new partition would come before an existing partition, omitting the maximum bound would cause the new partition to take on the nearest following existing partition’s minimum bound as its maximum bound. If the new partition would come after an existing partition, omitting the minimum bound would cause the new partition to take on the nearest preceding partition’s maximum bound as its minimum bound. If no partitions are present in the table, the new partition will have to declare both a minimum and maximum bound.

Add Table Range Partition Syntax

ALTER TABLE [<schema name>.]<table name>
ADD PARTITION <partition name> [ MIN ( <least value> ) ] [ MAX ( <greatest value> ) ]

For example, to add a partition to the customer_order_range_by_year table, containing all records with a partition key less than 2020 and greater than or equal to the maximum bound of the nearest preceding partition:

Add Table Range Partition Example

ALTER TABLE example.customer_order_range_partition_by_year
ADD PARTITION order_2020 MAX(2021)

List Partition

The new partition can be given a list of values to match against the partition key values of incoming records.

Add Table List Partition Syntax

ALTER TABLE [<schema name>.]<table name>
ADD PARTITION <partition name> VALUES ( <value lists> )

For example, to add a partition to the customer_order_manual_list_partition_by_year table, containing all records from 2020:

Add Table List Partition (Year) Example

ALTER TABLE example.customer_order_manual_list_partition_by_year
ADD PARTITION order_2020 VALUES (2020)

For example, to add a partition to the customer_order_manual_list_partition_by_year_and_month table, containing all records from February 2020 & April 2020:

Add Table List Partition (Month) Example

ALTER TABLE example.customer_order_manual_list_partition_by_year_and_month
ADD PARTITION order_2020_0204 VALUES ((2020, 2), (2020, 4))

Remove Partition

An existing partition can be removed from a range-partitioned or list-partitioned table, sending all data contained within that partition back to the default partition.

Remove Table Partition Syntax

ALTER TABLE [<schema name>.]<table name>
REMOVE PARTITION <partition name>

For example, to remove a partition named order_2017 from the customer_order_range_by_year table:

Remove Table Partition Example

ALTER TABLE example.customer_order_range_partition_by_year
REMOVE PARTITION order_2017

Delete Partition

An existing partition can be dropped from a range-partitioned or list-partitioned table, deleting all data contained within it.

Delete Table Partition Syntax

ALTER TABLE [<schema name>.]<table name>
DELETE PARTITION <partition name>

For example, to drop a partition named order_2014_2016 from the customer_order_range_by_year table, deleting all data within that partition:

Delete Table Partition Example

ALTER TABLE example.customer_order_range_partition_by_year
DELETE PARTITION order_2014_2016

Set Tier Strategy

A table’s eviction priorities can be adjusted by setting its tier strategy.

Set Table Tier Strategy Syntax

ALTER TABLE [<schema name>.]<table name>
SET TIER STRATEGY (<tier strategy>)

For example, to set the customer_order table’s tier strategy, to one with a below-average eviction priority in the RAM Tier:

Set Table Tier Strategy Example

ALTER TABLE example.customer_order
SET TIER STRATEGY
(
    ( ( VRAM 1, RAM 3, PERSIST 5 ) )
)

The tier strategy can also be reset to the system default strategy.

Reset Table Tier Strategy Syntax

ALTER TABLE [<schema name>.]<table name>
RESET TIER STRATEGY

For example, to reset the customer_order table’s tier strategy:

Reset Table Tier Strategy Example

ALTER TABLE example.customer_order
RESET TIER STRATEGY

Set Write-Ahead Log Options

The write-ahead log configuration of a table can be modified, or reverted to the database-wide configuration.

Set Write-Ahead Log Options Syntax

ALTER TABLE [<schema name>.]<table name>
SET WAL OPTIONS (<option name> = '<option value>'[,...])

Only one table can be altered per statement, and the target must be a table; altering the write-ahead log of a view or a materialized view will return an error. Option names are case-insensitive.

Changes take effect immediately and persist across database restarts. Raising SEGMENT_COUNT reserves no disk space in advance.

Once a table’s write-ahead log has been configured here, it will no longer pick up later database-wide write-ahead log changes; a table that has never been configured individually continues to receive them. Use RESTORE_SYSTEM_SETTINGS to return a table to the database-wide configuration and have it accept subsequent database-wide changes again. Where the database determines segment count automatically, a restored table returns to the default count and is re-evaluated the next time the database restarts.

Options

MAX_SEGMENT_SIZE

Maximum size, in bytes, of an individual segment file

SEGMENT_COUNT

Approximate number of segment files across which the write-ahead log is split; must be at least 2

SYNC_POLICY

How write-ahead log entries are synchronized to disk:

none - disables the write-ahead log for the table
background - entries are written periodically, rather than immediately after each operation
flush - entries are protected in the event of a database crash
fsync - entries are protected in the event of an OS crash

CHECKSUM

Whether each entry is verified against a protective checksum; the default is true

RESTORE_SYSTEM_SETTINGS

Whether to revert the table to the database-wide write-ahead log configuration; cannot be used with any other option. The default is false

For example, to split the write-ahead log of the wal_product table across 20 segments and protect its entries against an OS crash:

Set Write-Ahead Log Options Example

ALTER TABLE example.wal_product
SET WAL OPTIONS (SEGMENT_COUNT = '20', SYNC_POLICY = 'fsync')

To revert that table to the database-wide write-ahead log configuration:

Restore Write-Ahead Log Settings Example

ALTER TABLE example.wal_product
SET WAL OPTIONS (RESTORE_SYSTEM_SETTINGS = 'true')

Restore Write-Ahead Log Settings Output

+---------------+---------------+------------+----------------+----------------------+---------------+-------------------------------------------------------------------------------------------------+
| SCHEMA_NAME   | TABLE_NAME    |   WAL_SIZE |   WAL_CAPACITY |         PERCENT_USED |   UNCOMMITTED | SETTINGS                                                                                        |
+---------------+---------------+------------+----------------+----------------------+---------------+-------------------------------------------------------------------------------------------------+
| example       | wal_product   |          0 |    10000000000 |   0.0                |             0 | {"checksum":"TRUE","max_segment_size":"500000000","segment_count":"10","sync_policy":"flush"}   |
+---------------+---------------+------------+----------------+----------------------+---------------+-------------------------------------------------------------------------------------------------+

The flush_frequency setting, which governs how often entries are written under the background sync policy, applies database-wide and has no per-table equivalent. It, and any other database-wide write-ahead log change, is made via /alter/wal rather than through SQL.

Manage Subscription

Any table that is subscribed to a streaming external data source can have that subscription paused, resumed, canceled, or dropped.

Manage External Table Subscription Syntax

ALTER TABLE [<schema name>.]<table name>
<PAUSE | RESUME | CANCEL | DROP> SUBSCRIPTION <data source name>

Once unsubscribed, there is no way to re-subscribe the table to the data source via ALTER TABLE. An external table will need to be re-created, while a table whose subscription was initiated through the LOAD INTO command can have that command re-executed.

For example, to manage a subscription on the ext_product external table through the product_ds data source:

ALTER TABLE example.ext_product
PAUSE SUBSCRIPTION example.product_ds

ALTER TABLE example.ext_product
RESUME SUBSCRIPTION example.product_ds

ALTER TABLE example.ext_product
CANCEL SUBSCRIPTION example.product_ds

ALTER TABLE example.ext_product
DROP SUBSCRIPTION example.product_ds

​Rename Table

​Move Table

​Set Access Mode

​Set TTL

​Add Column

​Rename Column

​Modify Column

​Drop Column

​Add Column Index

​Drop Column Index

​Add Low-Cardinality Index

​Drop Low-Cardinality Index

​Add Chunk Skip Index

​Drop Chunk Skip Index

​Add Geospatial Index

​Drop Geospatial Index

​Add CAGRA Index

​Refresh CAGRA Index

​Drop CAGRA Index

​Add HNSW Index

​Drop HNSW Index

​Add Foreign Key

​Drop Foreign Key

​Add Partition

​Range Partition

​List Partition

​Remove Partition

​Delete Partition

​Set Tier Strategy

​Set Write-Ahead Log Options

​Options

​Manage Subscription

Rename Table

Move Table

Set Access Mode

Set TTL

Add Column

Rename Column

Modify Column

Drop Column

Add Column Index

Drop Column Index

Add Low-Cardinality Index

Drop Low-Cardinality Index

Add Chunk Skip Index

Drop Chunk Skip Index

Add Geospatial Index

Drop Geospatial Index

Add CAGRA Index

Refresh CAGRA Index

Drop CAGRA Index

Add HNSW Index

Drop HNSW Index

Add Foreign Key

Drop Foreign Key

Add Partition

Range Partition

List Partition

Remove Partition

Delete Partition

Set Tier Strategy

Set Write-Ahead Log Options

Options

Manage Subscription