Operations with tables

CREATE TABLE — Create a new table
INSERT — Add data to a table
DROP TABLE — Deleting a table
SHOW TABLES — Display a list of all tables
DESCRIBE TABLE — Display information about 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>]
        [GENERATED BY DEFAULT AS IDENTITY] [GENERATED ALWAYS AS IDENTITY],
    ...
    )
    [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 (see section Data types)

NOT NULL — this column does not accept the value NULL

DEFAULT <default_expr> — default constant or constant expression

GENERATED BY DEFAULT AS IDENTITY — this column is an identity column, that is, the default values for it are automatically generated from an implicit sequence. If an INSERT command is executed for a table with an identity column and a value for that column is not explicitly specified, the value generated by the implicit sequence is inserted into that column.
See example
Create a table my_table with two columns. Set the id column as an identity column (autoincrement). Let’s insert numbers from 5 to 10 into the other column using the function generate_series. As a result, the values will be automatically inserted into the identification column.

CREATE TABLE my_table ( id INT GENERATED BY DEFAULT AS IDENTITY, numbers INT, ); INSERT INTO my_table (numbers) SELECT unnest(generate_series(5,10)); SELECT * FROM my_table;

+----+---------+ | id | numbers | +----+---------+ | 0 | 5 | +----+---------+ | 1 | 6 | +----+---------+ | 2 | 7 | +----+---------+ | 3 | 8 | +----+---------+ | 4 | 9 | +----+---------+ | 5 | 10 | +----+---------+

<select_expr> — SELECT expression, the result of execution of which 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> — a sort column used to compact the table data. The data in the table is stored with arbitrary sorting, ordering is not guaranteed.

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.

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

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 appropriate columns. 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 |
+---------+---------+

Deleting a table

DROP TABLE [IF EXISTS] <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.

Display a list of all tables

SHOW TABLES;

Outputs a list of all existing tables available to the user.

Display information about the table

DESC[RIBE] TABLE <table_name>;

Displays all information about the table — column names, their data types, and other properties.