Operations with tables

CREATE TABLE — Create a new table
ALTER TABLE — Change table attributes
INSERT — Add data to a table
DROP TABLE — Delete a table
TRUNCATE TABLE — Remove all rows from a table
SHOW TABLES — Outputs a list of all tables
SHOW COLUMNS — Outputs a list of all columns in all tables
DESCRIBE TABLE — Display information about the table
DELETE — Deleting rows from the table
UPDATE — Updating rows in the table

Create a new table

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] [<table_schema>.]<table_name>
    (<column_name> <column_type>
        [NOT NULL] [DEFAULT <default_expr>]
    ...
    )
    [WITH (<table_param>, ... )]

Creates a new table with the specified name and specified columns.

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] [<table_schema>.]<table_name> AS
    <select_expr>
    [WITH (<table_param>, ... )]

Creates a new table with the specified name based on the result of a SELECT query.

Parameters

<table_name> — name of the table to be created

<table_schema> — schema of the table to be created

<column_name> — column name of the table to be created

<column_type> — data type of the column of the table to be created or the IDENTITY keyword (for autoincrement).

NOT NULL — this column does not accept the value NULL

DEFAULT <default_expr> — default constant or constant expression

<select_expr> — a SELECT expression whose result will be written to the table being created

<table_param> — parameters of the table to be created
table_param ::= [<name> = <value>]
Possible meanings:
- snapshot_ttl = <duration> — depth of snapshot (table version) storage.
  Default: 7 days, but no more than 1000 snapshots.
  For example: '1 week', '2 days', '4 days 3 hours 5 minutes 30 seconds'.
- order_by = <column_name> — column to sort the data at the storage level.
  Read more: Managing table partitioning.

If the OR REPLACE modifier is specified, the final action is equivalent to deleting the existing table and creating a new one with the same name.

The optional IF NOT EXISTS modifier restricts the query to only those cases in which the specified object does not already exist.

The modifiers are mutually exclusive. Specifying them both will result in an error.

If there is no schema prefix (<table_schema>), the table is created in the user’s default schema.

Managing table partitioning

In the order_by parameter, you can specify a column for sorting data at the storage level.

Tables are stored in Iceberg format. Data granularity is maintained by splitting the table data into separate parquet files. Records between files are allocated using the order_by parameter specified when the table was created. This allows to speed up by several times the bulk operations of data modification with the condition of filtering by this parameter.

If the parameter is not specified, the data in the table is stored with arbitrary sorting. If the parameter is specified, the table is partitioned by the column specified in this parameter.

It is recommended to use the order_by parameter when creating tables of large size. This allows you to speed up data modification operations. For example, if a filter by sorting column is specified in the conditions for DELETE, the deletion will be performed as fast as possible — by partition.

Example
Let’s create a table with a given parameter for sorting and specify sorting by column column2:
CREATE TABLE my_table (column1 INT, column2 DATE) WITH (order_by = column2);
Delete some of the data with the filter on the sort column:
DELETE FROM my_table WHERE column2 > '2026-01-01';
In this case, the deletion process will be as efficient as possible for large table sizes.

Autoincrement

When creating a table, you can specify a column that will be automatically filled with new values when rows are added to the table. This is convenient in cases when you need an automatic row identifier when adding data.

To do this, you need to specify the IDENTITY keyword as the column type. Then when new rows are added to the table, integers starting from 0 will be automatically inserted into this column.

Example

Create a table with two columns and for the first column we specify IDENTITY as the type:

CREATE TABLE my_table (column1 IDENTITY, column2 VARCHAR);

+--------+
| status |
+--------+
| CREATE |
+--------+

Let’s insert two identical values into column column2, for column column1 we will not specify values to insert:

INSERT INTO my_table (column2) VALUES
    ('test_value'),
    ('test_value');

+-------+
| count |
+-------+
| 2     |
+-------+

Let’s output all rows of the resulting table:

SELECT * FROM my_table;

+---------+------------+
| column1 | column2    |
+---------+------------+
| 0       | test_value |
+---------+------------+
| 1       | test_value |
+---------+------------+

We can see that integers starting with 0 have been automatically inserted into column column1.

Inside the autoincrement mechanism, the function nextval_tngri is used.

Change table attributes

Rename table

ALTER TABLE [<table_schema>.]<old_table_name>
    RENAME TO [<table_schema>.]<new_table_name>;

Renames an existing table to the specified name <new_table_name>. All attributes and permissions are retained.

Specifying a schema before the new table name is optional.

Adding a column

ALTER TABLE [<table_schema>.]<table_name>
    ADD COLUMN <column_name> <column_type>;

Adds a column with the specified name and the specified data type to the table. The value NULL is written to all rows of the added column.

Deleting a column

ALTER TABLE [<table_schema>.]<table_name>
    DROP COLUMN <column_name>;

Deletes the column with the specified name from the table.

Add data to a table

INSERT INTO <target_table> [ ( <target_col_name> [ , ... ] ) ]
       {
         VALUES ( { <value> | DEFAULT | NULL } [ , ... ] ) [ , ( ... ) ]  |
         <query>
       }

Updates the table by inserting one or more rows into the table. The values inserted into each column of the table can be specified explicitly or obtained from a nested query.

Parameters

<target_table> — the name of the target table into which the rows will be inserted

<target_col_name> — the names of the columns into which the values will be inserted. If any table columns are not specified, default values will be inserted into them.

VALUES ( value | DEFAULT | NULL [ , … ] ) [ , ( … ) ] — specifies one or more values to insert into the appropriate columns of the target table.
- value — an explicitly specified value; can be a literal or an expression.
- DEFAULT — the default value for the corresponding column of the target table.
- NULL — an empty value.
Values are separated by commas.

You can insert multiple strings by specifying additional sets of values in the expression.

query — a query that returns values to insert into the target table. This allows you to insert rows into the target table from one or more source tables.

Example of inserting explicitly specified values

Insert values for the country and capital columns into the capitals table:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');

SELECT * FROM capitals;

+---------+---------+
| country | capital |
+---------+---------+
| France  | Paris   |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

Example of inserting results of a nested query

Now let’s create another table capitals_m and insert the rows from capitals that will be the result of the nested SELECT query. Let’s insert such rows from capitals where the value of capital contains M.

CREATE TABLE capitals_m (country VARCHAR, capital VARCHAR);

INSERT INTO capitals_m (country, capital)
    SELECT * FROM capitals
        WHERE capital LIKE '%M%';

SELECT * FROM capitals_m;

+---------+---------+
| country | capital |
+---------+---------+
| Russia | Moscow |
+---------+---------+
| Spain | Madrid |
+---------+---------+

Delete a table

DROP TABLE [IF EXISTS] [<table_schema>.]<table_name>;

Deletes the table with the specified name.

The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.

Remove all rows from a table

TRUNCATE TABLE [IF EXISTS] [<table_schema>.]<table_name>;

Deletes all rows from the table, but does not delete the table itself (columns, column data types, and table privileges stay intact).

The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.

Outputs a list of all tables

SHOW TABLES;

Outputs a list of all tables available to the user.

Output format:

+-------------+------------+
| schema_name | table_name |
+-------------+------------+
| ...         | ...        |
+-------------+------------+

Outputs a list of all columns in all tables

SHOW COLUMNS;

Outputs a list of all columns in all tables accessible to the user.

Output format:

+-------------+------------+-------------+
| schema_name | table_name | column_name |
+-------------+------------+-------------+
| ...         | ...        | ...         |
+-------------+------------+-------------+

Display information about the table

DESC[RIBE] TABLE <table_name>;

Displays information about the table.

Output format:

+-------------+-------------+------+---------+
| column_name | column_type | null | default |
+-------------+-------------+------+---------+
| ...         | ...         | ...  | ...     |
+-------------+-------------+------+---------+

column_name — column name
column_type — data type of the column
null — whether NULL values are allowed in the column
default — default value of the column