CREATE TABLE

The CREATE TABLE statement is used to create an empty table in the current database. The syntax of the command is:

CREATE [TEMPORARY | TEMP] TABLE <table name> (

<column name> <column data type> [COLLATE][PRIMARY KEY]

[CONSTRAINT  <constraint name> [PRIMARY KEY <column name>],
...]

);

When creating a table, you can specify:

1. Table Type: TEMPORARY / TEMP – a temporary table that is automatically deleted at the end of the session. If this parameter is not set (left empty), a permanent table is created. Any indexes created on a temporary table will also be deleted at the end of the session, which means that they are temporary as well. A temporary table and a permanent table with the same name are allowed, in which case a temporary table will be selected.

2. Table Name: <table name> – you can use English letters in lowercase, numbers, underscores and dollar signs ($). For example, the table name "People" will be stored as "people". For more information, see Identifiers and Key Words.

3. Column Name: <column name> – the same naming rules apply as for table names.

4. Data Type: <column data type> – standard PostgreSQL data types are specified.

5. Collation Rule: COLLATE – collation rules allow setting sorting order and character classification features in individual columns or even when performing individual operations. Sortable types include: text, varchar, and char. You can specify the locale (e.g., en_US, ru_RU) used to determine the sorting and string comparison rules in the specified columns.

6. Table's Primary Key: PRIMARY KEY – a mandatory condition when creating a table in YDB's PostgreSQL compatibility mode.

7. Table-level Constraints (there can be multiple, delimited by commas): CONSTRAINT – this type of constraint is used as an alternative syntax to column constraints, or when the same constraint conditions need to be applied to multiple columns. To specify a constraint, you need to state:

   • The keyword CONSTRAINT.
   • The constraint name <constraint name>. The rules for creating an identifier for the constraint are the same as for table names and column names.
   • The constraint. For example, a primary key constraint can be defined for a single column as PRIMARY KEY (<column name>) or for multiple columns as a composite key: PRIMARY KEY (<column name1>, <column name2>, ...).

Creating two tables with primary key autoincrement

CREATE TABLE people (
    id                 Serial PRIMARY KEY,
    name               Text,
    lastname           Text,
    age                Int,
    country            Text,
    state              Text,
    city               Text,
    birthday           Date,
    sex                Text,
    social_card_number Int
);

CREATE TABLE social_card (
    id                   Serial PRIMARY KEY,
    social_card_number   Int,
    card_holder_name     Text,
    card_holder_lastname Text,
    issue                Date,
    expiry               Date,
    issuing_authority    Text,
    category             Text
);

In this example, we used the pseudo data type Serial – it's a convenient and straightforward way to create an auto-increment that automatically increases by 1 each time a new row is added to the table.

Creating a table with constraints

CREATE TABLE people (
    id                    Serial,
    name                  Text NOT NULL,
    lastname              Text NOT NULL,
    age                   Int,
    country               Text,
    state                 Text,
    city                  Text,
    birthday              Date,
    sex                   Text NOT NULL,
    social_card_number    Int,
    CONSTRAINT pk PRIMARY KEY(id)
);

In this example, we created the "people" table with a constraint block (CONSTRAINT), where we defined a primary key (PRIMARY KEY) consisting of the "id" column. An alternative notation could look like this: PRIMARY KEY(id) without mentioning the CONSTRAINT keyword.

Creating a temporary table

CREATE TEMPORARY TABLE people (
    id serial PRIMARY KEY,
    name TEXT NOT NULL
);

The temporary table is defined using the TEMPORARY or TEMP keywords.

Creating a table with sorting conditions

CREATE TABLE people (
    id                   Serial PRIMARY KEY,
    name                 Text COLLATE "en_US",
    lastname             Text COLLATE "en_US",
    age                  Int,
    country              Text,
    state                Text,
    city                 Text,
    birthday             Date,
    sex                  Text,
    social_card_number   Int
);

In this example, the "name" and "lastname" columns use sorting with en_US localization.