This group of DDL SQL statements allows database administrators to manage tables. Database administrators can create, modify, drop, and export tables. You can also truncate segments on a table. Additional statements are available for unique settings like segment redundancy, compression settings, and streamloader properties. You can view information about tables using the sys.tables system catalog table.

For information on other database components, see the pages on Databases, Schemas, Views, and Indexes.

CREATE TABLE

Creates a new table in the current database.

The table name must be distinct from the name of any existing tables in the database unless the REPLACE keyword is specified. To use REPLACE in the CREATE TABLE statement, you must have DELETE privileges.

By default, columns are nullable unless otherwise specified.

For faster query results, you can define one TimeKey® for the table, which must be a timestamp, date, or time column, with a specified bucket resolution. Tables with a specified TimeKey can perform query operations faster, especially if they involve time filtering.  

You can specify a Clustering Key composed of one or more fixed-length columns. Designating columns as cluster keys that are frequently referenced in queries can greatly improve performance. 

For details about defining TimeKeys and clustering indexes, see TimeKeys and Clustering Keys.

See the Data Types section for table-supported data types.

Required Privileges: You must have the CREATE TABLE privilege for the current database. 

Syntax

CREATE [ OR REPLACE ] TABLE [ IF NOT EXISTS ] table_name
    [ ( <column_definition> [, ...] | <clustering_definition> ) ]
    [ [ WITH ] <create_option> [, ... ] ]
    [ AS (query) ]
<column_definition> ::=
   column_name [ data_type ] [ <timekey_definition> ] [ <column_constraint> ] [, ...] ]
<timekey_definition> ::=
  TIME KEY BUCKET (bucket_granularity, bucket_value)
<column_constraint> ::=
   | [ NOT ] NULL
   | DEFAULT literal
   | COMMENT comment
   | COMPRESSION GDC [ (compression_value), EXISTING schema_name ]
   | COMPRESSION [ compression_scheme ]
<clustering_definition> ::=
    CLUSTERING KEY key_name (ck_col1, ck_col2 [, ...])
        [, INDEX index_name (idx_col1, idx_col2 [, ...])
        [, INDEX ... ] ]
 <create_option> ::=
   INDEX index_name (index_column) [ USING index_type ]
   | RETENTION POLICY AGE retention_granularity retention_value
   | STORAGESPACE storage_space_name
   | SEGMENTSIZE segment_value
   | REDUNDANCY segment_part (redundancy_scheme)
   | STREAMLOADER_PROPERTIES streamloader_json
   | COMMENT '<string>'

Column Definition (<column_definition>)

The parameters listed here are required for defining each column in a table.

Clustering Key and IndexDefinition (<clustering_definition>)

The parameters listed here are required for defining a Clustering Key or clustering indexes on a table.

For details about how to apply clustering columns, see Clustering Key.

CLUSTERING KEY key_name (ck_col1, ck_col2 [, ...])
    [, INDEX index_name (idx_col1, idx_col2 [, ...])
    [, INDEX ... ] ]

TimeKey Definition (<timekey_definition>)

This syntax example is for a single TimeKey column, which can be included in the column definition of a CREATE TABLE statement. For the full syntax, see CREATE TABLE.

For details about using TimeKeys, see TimeKeys.

column_name [ data_type ] TIME KEY BUCKET (bucket_granularity, bucket_value [, NOINDEX] )

Column Constraint (<column_constraint>)

The parameters listed here include constraints and other configurations for individual columns. For best performance, one column with date or time data in each table should be defined as the TIME KEY with a specified bucket_value.

For details about TimeKey columns, see TimeKeys.

Create Option (<create_option>)

The parameters listed here include various options to configure table storage space, segments, redundancy, streamloading, and indexes.

Example

This example creates a new table in the current database and schema named trades.

The table uses the TIMESTAMP column created_at as the TimeKey, with the granularity set at 1 hour. The columns ticker_symbol and t_type are defined as the table clustering keys. The example also includes a streamloader property pageQueryExclusionDuration to delay how soon data pages that were recently added can be included in query results.

 CREATE TABLE trades (
    id UUID NOT NULL,
    ticker_symbol VARCHAR(255) NOT NULL COMPRESSION GDC(2),
    t_type VARCHAR(255) NOT NULL COMPRESSION GDC(1),
    raw_ticker_data VARCHAR(255),
    created_at TIMESTAMP NOT NULL TIME KEY BUCKET(1, HOUR),
    array_of_tuples tuple<<varchar(2040), byte, bigint, double, timestamp, date, time, decimal(3,2), st_point, boolean, binary(6), hash(8), ip, uuid>>[],
    CLUSTERING INDEX idx_ticker_symbol_type (ticker_symbol, t_type),
    INDEX idx_type (t_type)
 )
    STORAGESPACE ocient_storage,
    REDUNDANCY DATA (PARITY),
    REDUNDANCY MANIFEST (COPY),
    STREAMLOADER_PROPERTIES '{
    "pageQueryExclusionDuration" : "2700s"
}';

CREATE TABLE AS SELECT (CTAS)

CTAS provides the ability to create and load a new table from the result of a query on one or more existing tables. The first column of the query result maps to the first column of the new table definition, the second column maps to the second column of the new table, and so on. The new table is available for querying after it has been created, and the entire result set from the query has been loaded into the table. When you receive a response to the CTAS SQL statement, the load is complete, and the table is ready.

When you create a table from a SELECT SQL statement, the schema for the table can be automatically determined based on the query results. You can override this behavior with an alternative schema, provided the query results can automatically be cast to the target column types. CTAS also supports all syntax options for the new table. CTAS does not support default values and explicit nullable definitions on the column of the table.

CTAS statements support secondary and prefix indexes.

To create a table, you must have both the CREATE TABLE privilege for the current database and the SELECT privilege on all referenced tables and views.

For syntax and parameter information, see CREATE TABLE.

Default Table Definitions

By default, a new table created with a CTAS statement retains column names, data types, and nullable definitions from the queried table. You can override this configuration with alternate table definitions in the CTAS statement.

Examples

These CTAS examples select columns from the original_table table that this CREATE TABLE statement defines. This table contains these columns:

• col_int — Non-nullable integer with the default value 123456789
• col_bigint — Non-nullable 8-byte signed integer
• col_id — Non-nullable integer
• col_point — Non-nullable point with the default value POINT(0 0)
• col_timestamp — Non-nullable TimeKey with granularity set at 1 day
• col_varchar — Variable-length character string with a maximum length of 255 characters and Zstandard compression

The table has a Clustering Key ck using the col_bigint and col_id columns. It also has two secondary indexes: a hash index idx_01 on the col_varchar column and a spatial index on the idx_02 column.

CREATE TABLE original_table (
 	col_int INT NOT NULL DEFAULT 123456789,
 	col_bigint BIGINT NOT NULL,
 	col_id INT NOT NULL,
	col_point POINT NOT NULL DEFAULT 'POINT(0 0)',
	col_timestamp TIMESTAMP TIME KEY BUCKET (1,DAY) NOT NULL,
	col_varchar VARCHAR(255) COMPRESSION ZSTD,
	CLUSTERING KEY ck (col_bigint, col_id)
)
WITH
    INDEX idx_01 (col_varchar) USING HASH,
    INDEX idx_02 (col_point) USING SPATIAL;

CTAS Using All Columns from a Base Table

This example shows a basic CTAS statement that inherits most of its table definition from the original_table table.

CREATE TABLE basic_ctas AS (
	SELECT * FROM original_table );

The new basic_ctas table includes all the columns and data types from the original table definition. However, it does not include the segment keys, indexes, or the compression on the col_varchar column. The EXPORT TABLE SQL statement shows the differences in the basic_ctas table.

EXPORT TABLE basic_ctas;

Output

CREATE TABLE basic_ctas (
    "col_int" INT NOT NULL,
    "col_bigint" BIGINT NOT NULL,
    "col_id" INT NOT NULL,
    "col_point" POINT NOT NULL,
    "col_timestamp" TIMESTAMP NOT NULL,
    "col_varchar" VARCHAR(536870912) COMPRESSION DYNAMIC NULL
)
REDUNDANCY cde (PARITY),
REDUNDANCY column_metadata (COPY),
REDUNDANCY data (PARITY),
REDUNDANCY index (PARITY),
REDUNDANCY pdf (PARITY),
REDUNDANCY skip_lists (COPY),
REDUNDANCY stats (PARITY),
REDUNDANCY summary_stats (PARITY),
REDUNDANCY table_of_contents (COPY),
STORAGESPACE "ss0",
SEGMENTSIZE 4;

In this output, the table options for REDUNDANCY, STORAGESPACE, and SEGMENTSIZE are all default table settings.

CTAS Using a Full Table Definition

This example CTAS statement includes a more detailed table definition. The definition includes new columns for the TimeKey, Clustering Key, and secondary indexes. The example also makes various changes from the original_table schema:

• Different column default value
• Different compression scheme (dynamic compression)
• New TimeKey granularity of 1 hour
• Three columns in the Clustering Key
• Different secondary index types (NGRAM and SPATIAL)

CREATE TABLE complex_ctas (
 	col_amt INT NOT NULL,
 	col_phone BIGINT NOT NULL COMPRESSION DYNAMIC,
 	col_id INT NOT NULL,
	col_point POINT NOT NULL DEFAULT 'POINT(0 0)',
	col_timestamp TIMESTAMP TIME KEY BUCKET (1,HOUR) NOT NULL,
	col_varchar VARCHAR(255) COMPRESSION DYNAMIC,
	CLUSTERING KEY ck (col_amt, col_phone, col_id)
)
WITH
    INDEX idx_01 (col_varchar) USING NGRAM(3),
    INDEX idx_02 (col_point) USING SPATIAL
AS (
	SELECT * FROM original_table );

CTAS Using a Subset of Table Columns

This example selects only a subset of columns, col_int, col_bigint, and col_id, from the original table to insert into the new subset table. The example also specifies alternate table options for REDUNDANCY and SEGMENTSIZE.

CREATE TABLE subset (
 	col_amt INT NOT NULL,
 	col_phone BIGINT NOT NULL,
 	col_id INT NOT NULL
)
WITH
    REDUNDANCY cde (PARITY),
    SEGMENTSIZE 3
AS (
    SELECT
        col_int,
        col_bigint,
        col_id
FROM
    original_table );

CTAS Using Transformations on Columns

This example performs various transformation functions on the original columns as it selects them for the new table. These include:

• col_int_multiply column is the multiplication of the col_int values by 10.
• col_month_add column is the result of adding three months to each col_timestamp column value.
• col_year column is the extraction of the year value from each col_timestamp column value.
• col_substring column contains the first three characters from each col_varchar column value.

CREATE TABLE calc_table (
 	col_int_multiply INT NOT NULL,
 	col_month_add TIMESTAMP NOT NULL,
 	col_year INT NOT NULL,
 	col_substring VARCHAR(255)
)
AS (
    SELECT
        col_int * 10,
        ADD_MONTHS(col_timestamp, 3),
        YEAR(col_timestamp),
        SUBSTRING(col_varchar, 1, 3)
FROM
    original_table );

CTAS USING LOADERS

Specify one or more Loader Nodes for executing the CTAS SQL statement. If you do not use this option, the Ocient System uses all Loader Nodes that are live to execute the SQL statement.

This statement is useful for managing loading operations, particularly when balancing multiple loads of different sizes and resource requirements. Alternatively, this statement can also help simplify small batch loads by sourcing the data from a single Loader Node.

Syntax

Examples

Create a table named my_schema.my_ctas_table_2 with a clustering index named idx on the int_col column with the values in the int_col column in the table named my_schema.my_table. Use the Loader Node named stream-loader1 to execute this SQL statement.

CREATE TABLE my_schema.my_ctas_table_2 (
   CLUSTERING INDEX idx (int_col)
 )
 USING LOADERS "stream-loader1"
 AS (SELECT int_col FROM my_schema.my_table);

In this example, execute the same CTAS SQL statement with two Loader Nodes named stream-loader2 and stream-loader3.

CREATE TABLE my_schema.my_ctas_table_2 (
   CLUSTERING INDEX idx (int_col)
 )
 USING LOADERS "stream-loader2","stream-loader3"
 AS (SELECT int_col FROM my_schema.my_table);

DROP TABLE

DROP TABLE removes one or more existing tables in the current database, along with all associated views.

To remove a table, the logged-in user must be a system-level user or have the DELETE TABLE privileges for the table.

Syntax

DROP TABLE [ IF EXISTS ] table_name [, ...]

Examples

This example drops an existing table in the current database and schema named employees.

DROP TABLE employees;

In this example, drop two tables in the current database and schema named employees and departments.

DROP TABLE employees, departments;

When you drop multiple tables, and none of them exist in the database, the database returns an error for each missing table. Use the IF EXISTS statement to convert the error to a warning. If you execute the DROP TABLE statement and only some of the tables exist while other tables are missing, the database drops the existing tables and returns warnings for each missing table.

ALTER TABLE

ALTER TABLE RENAME

ALTER TABLE RENAME renames an existing table.

Required Privileges

To rename a table, you must have the ALTER TABLE privilege for the table.

The Ocient System requires these privileges if this statement includes a change to the schema:

• VIEW privilege on the current schema of the table
• VIEW TABLE and CREATE TABLE privileges on the target schema (if the schema already exists)
• CREATE TABLE privilege on the database (if the schema does not exist)

Syntax

ALTER TABLE [ IF EXISTS ] old_table_name RENAME TO new_table_name

Example

This example renames an existing table in the current database and schema named us.employees to mid_west_employees.

ALTER TABLE us.employees RENAME TO mid_west_employees;

This example renames an existing table in the current database named us.employees to north_america.employees.

ALTER TABLE us.employees RENAME TO north_america.employees;

ALTER TABLE RENAME COLUMN

ALTER TABLE RENAME COLUMN renames an existing column.

To rename a column, you must have the ALTER TABLE privilege for the table.

Syntax

ALTER TABLE [ IF EXISTS ] table_name RENAME COLUMN old_column_name TO new_column_name

Example

This example renames an existing column name in the table employees in the current database and schema to first_name.

ALTER TABLE employees RENAME COLUMN name TO first_name;

ALTER TABLE ADD COLUMN

ALTER TABLE ADD COLUMN adds a new column to the table.

To add a column, you must have the ALTER TABLE privilege for the table.

New columns must either be nullable or specify a default value.

For a defined list of column parameters, see Column Definition. For constraints, see Column Constraint.

Syntax

ALTER TABLE [ IF EXISTS ] table_name ADD COLUMN <column_definition>;
<column_definition> ::=
   column_name [ data_type ] [ <column_constraint> [, ...] ]
<column_constraint> ::=
   TIME KEY BUCKET(bucket_granularity [, bucket_value ] )
   | [ NOT ] NULL
   | DEFAULT literal
   | COMMENT comment
   | COMPRESSION GDC [ (compression_value), EXISTING schema_name ]
   | COMPRESSION [ compression_scheme ]

Examples

This example adds a BIGINT column to the employees table in the current database and schema with the default value of 0.

ALTER TABLE employees ADD COLUMN
    new_column BIGINT
        NOT NULL
        DEFAULT 0;

This example adds a column that is nullable.

ALTER TABLE employees ADD COLUMN
    new_column BIGINT
    NULL;

ALTER TABLE ALTER COLUMN COMPRESSION

ALTER TABLE ALTER COLUMN COMPRESSION alters an existing column in the table to change its compression scheme.

Supported compression schemes are COMPRESSION NONE, COMPRESSION DYNAMIC, and COMPRESSION ZSTD.

For details about Ocient-supported compression schemes, see Table Compression Options.

Syntax

ALTER TABLE [ IF EXISTS ] table_name ALTER COLUMN column_name
    SET COMPRESSION [ compression_scheme ];

Examples

This example alters the compression scheme for the column employee_name in the table employees in the current database and schema.

ALTER TABLE employees ALTER COLUMN employee_name
    SET COMPRESSION LZ4;

This example alters the compression scheme to Zstandard for the column employee_name in the table employees in the current database and schema.

ALTER TABLE employees ALTER COLUMN employee_name
    SET COMPRESSION ZSTD
        compression_level=5,
        dictionary_size=32768;

ALTER TABLE ALTER REDUNDANCY

ALTER TABLE ALTER REDUNDANCY alters the segment part redundancy for future segments of an existing table.

Note that altering a segment part redundancy setting only affects data loaded after applying the SQL statement.

ALTER TABLE [ IF EXISTS ] table_name ALTER REDUNDANCY segment_part (redundancy_scheme)

Example

This example alters the STATS part to COPY redundancy.

ALTER TABLE employees ALTER REDUNDANCY STATS (COPY);

ALTER TABLE DROP COLUMN

ALTER TABLE DROP COLUMN drops an existing column from the table.

You cannot remove the TimeKey column and the clustering key columns from the table.

When you remove a column, the database does not remove or free any actual data.

ALTER TABLE [ IF EXISTS ] table_name DROP COLUMN column_name [ IF EXISTS ]

Example

This example removes a column named address from the table employees.

ALTER TABLE employees DROP COLUMN address;

ALTER TABLE STREAMLOADER_PROPERTIES

ALTER TABLE STREAMLOADER_PROPERTIES resets the table streamloader properties to the provided string. The properties string must be in valid JSON format.

The database registers streamloader changes dynamically. Therefore, you do not need to restart nodes or take other actions for the changes to take effect.

Any properties not specified in the string default to the system-wide setting.

ALTER TABLE [ IF EXISTS ] table_name STREAMLOADER_PROPERTIES streamloader_json

Configuring Streamloader Properties

STREAMLOADER_PROPERTIES is a field on the table metadata that must be written as a JSON string in order to be read properly. The database can dynamically render any changes to STREAMLOADER_PROPERTIES with the ALTER TABLE SQL statement. You can set Loader Node properties for a new table as a parameter in the CREATE TABLE SQL statement. You do not need to restart the database node for the changes to take effect. 

Per-Table Streamloader Properties

Example

This example sets the Loader Node properties of the table employees to {"pageQueryExclusionDuration" : "30s"}. This means that any pages added less than 30 seconds ago will not be included in query results.

ALTER TABLE employees
    STREAMLOADER_PROPERTIES '{
        "pageQueryExclusionDuration" : "30s"
        }';

ALTER TABLE DISABLE INDEX

The ALTER TABLE DISABLE INDEX statement instructs future queries not to use the specified indexes, but existing segments and new segments continue to have the index available in case you enable the index again.

All secondary indexes except for secondary clustering key indexes can be disabled. Trying to disable other types of indexes generates an error.

You can specify the index by name or UUID.

Syntax

ALTER TABLE [ IF EXISTS ] table_name DISABLE INDEX
    { index_name_or_uuid | IN (index_name_or_uuid [,...]) }

Examples

This example disables an existing index named current_idx on the table employees.

ALTER TABLE employees DISABLE INDEX current_idx;

This example disables an existing or dropped index with the UUID 5c15d8de-36fa-4055-9bdc-3f1750aaeea0.

ALTER TABLE employees DISABLE INDEX '5c15d8de-36fa-4055-9bdc-3f1750aaeea0';

This example disables both indexes current_idx and other_idx on the table employees.

ALTER TABLE employees DISABLE INDEX IN (current_idx, other_idx);

ALTER TABLE ENABLE INDEX

The ALTER TABLE ENABLE INDEX statement reverts the operation performed by the ALTER TABLE DISABLE INDEX statement.

Syntax

ALTER TABLE [ IF EXISTS ] table_name ENABLE INDEX index_name_or_uuid

Examples

This example enables an existing index named current_idx on the table employees.

ALTER TABLE employees ENABLE INDEX current_idx;

This example enables an existing or dropped index with the UUID 5c15d8de-36fa-4055-9bdc-3f1750aaeea0.

ALTER TABLE employees ENABLE INDEX '5c15d8de-36fa-4055-9bdc-3f1750aaeea0';

This example enables both indexes current_idx and other_idx on the table employees.

ALTER TABLE employees ENABLE INDEX IN (current_idx, other_idx);

ALTER TABLE ENABLE RETENTION POLICY AGE

This ALTER TABLE SQL statement enacts a new retention policy on the specified table. A table can have only one retention policy. For details about retention policies, see Table Retention Policies.

Required Privileges

You must have the ALTER and DELETE privileges for the specified table. 

Syntax

ALTER TABLE [ IF EXISTS ] table_name ENABLE RETENTION POLICY AGE
    retention_granularity retention_value

Example

This example creates a new retention policy for my_table that deletes any rows older than 1 day.

ALTER TABLE my_table ENABLE RETENTION POLICY AGE 1 DAY;

ALTER TABLE DISABLE RETENTION POLICY

This ALTER TABLE SQL statement disables a retention policy on the specified table. For details about retention policies, see Table Retention Policies.

Required Privileges

You must have the ALTER TABLE system privilege and the ALTER privilege for the specified table. 

Syntax

ALTER TABLE [ IF EXISTS ] table_name DISABLE RETENTION POLICY

Example

This example removes the retention policy for my_table.

ALTER TABLE my_table DISABLE RETENTION POLICY;

DELETE FROM TABLE

Removes rows from the specified table.

You can use the WHERE clause to specify the rows to remove. If a DELETE SQL statement lacks the WHERE clause, then the database deletes all rows in the table.

To use this statement, you must have the DELETE privilege for the table.

For details and examples, see Remove Records from an Ocient System.

Syntax

DELETE FROM table_name [ WITH cte ] [ WHERE <filter_clause> ]

Examples

Delete Rows from the Table with Filter Criteria

This DELETE SQL statement removes all rows in the movies table that have a budget of less than 10000.

DELETE FROM movies WHERE budget < 10000;

Delete Rows from the Table Using a Common Table Expression

This example uses a common table expression using the WITH keyword to find rows representing all transactions that occurred before 2022 that are less than $100. The DELETE SQL statement receives the results from the common table expression. Then, the database executes this statement to delete the corresponding rows.

DELETE FROM transactions
    WITH old_transactions AS (
        SELECT
            transaction_id
        FROM
        	transactions
    	WHERE transaction_date < '2022-01-01'
        	AND amount < 100
	)
	WHERE transaction_id IN (
		SELECT transaction_id
		FROM old_transactions );

EXPORT TABLE

EXPORT TABLE shows the CREATE TABLE statement for an existing table in the current database.

To export a table, you must have the SELECT TABLE privilege for the table.

EXPORT TABLE table_name

Example

This example exports an existing table in the current database and schema named trades.

EXPORT TABLE trades;

Output

CREATE TABLE "admin@system"."trade_test" (
    "id" UUID NOT NULL,
    "ticker_symbol" VARCHAR(1048576) COMPRESSION GDC(2) NOT NULL,
    "t_type" VARCHAR(1048576) COMPRESSION GDC(1) NOT NULL,
    "raw_ticker_data" VARCHAR(1048576) COMPRESSION DYNAMIC NULL,
    "created_at" TIMESTAMP TIME KEY BUCKET(1, HOUR) NOT NULL,
    "array_of_tuples" TUPLE<<VARCHAR(1048576) COMPRESSION DYNAMIC,TINYINT,BIGINT,DOUBLE PRECISION,TIMESTAMP,DATE,TIME,DECIMAL(3,2),POINT,BOOLEAN,BINARY(6),BINARY(8),IP,UUID>>[] NULL,
    CLUSTERING INDEX "idx_ticker_symbol_type" ("ticker_symbol", "t_type"),
    INDEX "idx_type" ("t_type")
)
REDUNDANCY cde (PARITY),
REDUNDANCY manifest (COPY),
REDUNDANCY pdf (PARITY),
REDUNDANCY stats (PARITY),
REDUNDANCY column_metadata (COPY),
REDUNDANCY index (PARITY),
REDUNDANCY summary_stats (PARITY),
REDUNDANCY skip_lists (COPY),
REDUNDANCY data (PARITY),
STORAGESPACE "storage",
SEGMENTSIZE 4,
STREAMLOADER_PROPERTIES '{"pageQueryExclusionDuration" : "30s"}';
CREATE INDEX "new_idx" ON "admin@system"."trade_test" ("raw_ticker_data") USING HASH;

INSERT INTO TABLE

INSERT INTO inserts rows into a table in the current database using literal values, column references, function executions, computed expressions, or column default values.

This SQL statement requires the INSERT privilege for the relevant table.

Syntax

INSERT INTO table_name [ ( col1, col2 [, ...] ) ]
    [ WITH cte ] { query | [ DEFAULT VALUES | VALUES [ <rows_to_insert> ] }
<rows_to_insert> ::=
    ( row1_exp1, row1_exp2 [, ...] ),
    ( row2_exp1, row2_exp2 [, ...] )
    [, ...]

Examples

Insert Values from One Column

This example inserts the columns from system.table_b into system.table_a.

INSERT INTO system.table_a SELECT * FROM system.table_b;

Insert Values from Multiple Columns

This example inserts the column system.table_b.id_col_b into system.table_a.id_col_a and system.table_b.int_col_b into system.table_a.int_col_a.

INSERT INTO system.table_a (id_col_a, int_col_a)
    SELECT id_col_b, int_col_b FROM system.table_b;

Insert Literal Values

Create a table with product, quantity, and date of sale information with these non-nullable columns:

• product — Product identifier
• quantity — Quantity of the product sold
• sale_date — Date of sale

CREATE TABLE sales (
    product INT NOT NULL,
    quantity INT NOT NULL,
    sale_date DATE NOT NULL
);

Insert three rows of literal values that represent different sales.

INSERT INTO sales (product, quantity, sale_date)
    VALUES
        (1, 10, '2023-01-15'),
        (2, 5, '2023-01-20'),
        (1, 8, '2023-02-05');

Insert Values Using a Common Table Expression

In this example, a common table expression performs calculations on the sales table before inserting rows into the monthly_sales_summary table.

The example uses the monthly_sales_summary table created by this CREATE TABLE statement with these non-nullable columns:

• product_id — Product identifier
• month — Month part of the date
• total_quantity — Total quantity of the product

CREATE TABLE monthly_sales_summary (
    "product_id" INT NOT NULL,
    "month" DATE NOT NULL,
    "total_quantity" INT NOT NULL
);

The common table expression following the WITH keyword extracts the month from the sale date sale_date and calculates the sum of the quantity sold total_quantity of the product from the sales table before inserting this data. Then, the INSERT SQL statement specifies to insert the data into the monthly_sales_summary table.

INSERT INTO monthly_sales_summary (product_id, month, total_quantity)
WITH monthly_totals AS (
    SELECT
        product,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(quantity_sold) AS total_quantity
    FROM
        sales
    GROUP BY
        product,
        DATE_TRUNC('month', sale_date)
)
SELECT
    product,
    month,
    total_quantity
FROM
    monthly_totals;

Insert Columns Using Default Values

This code utilizes the customers table with these columns:

• customer_id — Customer identifier
• name — Customer name
• status — Customer status with the default active status
• created_at — Created date

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP
);

Use the DEFAULT VALUES keyword to insert one row of default values into the table. For columns that lack a defined default value, the operation inserts a NULL row.

INSERT INTO customers DEFAULT VALUES;

The resulting row contains all NULL values except for the status column, which has the active default value.

SELECT * FROM customers;

Output

   | customer_id | name  | status | created_at |
   | ----------- | ----- | ------ | ---------- |
   |             |       | ACTIVE |            |

Alternatively, you can insert default values by using the DEFAULT keyword as one of the row values in the INSERT statement.

INSERT INTO customers (customer_id, name, status, created_at)
    VALUES (1, 'Alice', DEFAULT, NULL);

Output

   | customer_id | name  | status | created_at |
   | ----------- | ----- | ------ | ---------- |
   |             |       | ACTIVE |            |
   | 1           | Alice | ACTIVE |            |

INSERT INTO TABLE USING LOADERS

Specify one or more Loader Nodes for executing the INSERT INTO SQL statement. If you do not use this option, the Ocient System uses all Loader Nodes that are live to execute the SQL statement.

This statement is useful for managing loading operations, particularly when balancing multiple loads of different sizes and resource requirements. Alternatively, this statement can also help simplify small batch loads by sourcing the data from a single Loader Node.

Syntax

INSERT INTO TABLE table_name USING LOADERS streamloader [, ... ]
     query

Examples

This example inserts the column system.table_b.id_col_b into system.table_a.id_col_a and system.table_b.int_col_b into system.table_a.int_col_a. Use the Loader Node named stream-loader1 to execute this SQL statement.

INSERT INTO system.table_a (id_col_a, int_col_a)
    USING LOADERS "stream-loader1"
    SELECT id_col_b, int_col_b FROM system.table_b;

In this example, execute the same SQL statement with two Loader Nodes named stream-loader2 and stream-loader3.

INSERT INTO system.table_a (id_col_a, int_col_a)
    USING LOADERS "stream-loader2","stream-loader3"
    SELECT id_col_b, int_col_b FROM system.table_b;

TRUNCATE TABLE

TRUNCATE TABLE removes some or all records from an existing table in the current database. The system deletes the truncated data, but the table and its schema remain intact in the system even if all data is deleted. If the entire table is truncated, Global Dictionary Compression tables remain in place.

To truncate a table, you must have the DELETE privilege for the table.

To remove a subset of rows from a table, you can use the DELETE FROM TABLE SQL statement.

For details and examples of using TRUNCATE, see Remove Records from an Ocient System.

Syntax

TRUNCATE TABLE table_name
TRUNCATE TABLE table_name WHERE segment_group_id = <ID>
TRUNCATE TABLE table_name WHERE segment_group_id in (<ID>, ...)

Examples

This example truncates an existing table in the current database and schema named students.

TRUNCATE TABLE students;

This example truncates an existing table in the current database named us.students.

TRUNCATE TABLE us.students;

This example truncates a single segment group from an existing table in the current database named students.

TRUNCATE TABLE students WHERE segment_group_id = 123456789;

This example truncates a number of segment groups from an existing table in the current database named us.students.

TRUNCATE TABLE us.students WHERE segment_group_id IN (1,2,3,4,5);

Related Links

Core Elements of an Ocient System

General SQL Syntax

Database Password Security Settings

Data Control Language (DCL) Statement Reference

System Catalog