---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/postgresql/statements/create_table.md?version=v25.4
  - https://ydb.tech/docs/ru/postgresql/statements/create_table.md?version=v25.4
  - href: en/postgresql/statements/create_table.md
    type: text/markdown
    title: Markdown version
  - href: ../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/postgresql/statements/create_table.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# CREATE TABLE

<!-- markdownlint-disable blanks-around-fences -->

<!-- source: en/postgresql/_includes/alert_preview.md -->
{% note warning %}

At the moment, YDB's compatibility with PostgreSQL **is under development**, so not all PostgreSQL constructs and [functions](https://ydb.tech/docs/en/postgresql/functions.md?version=v25.4) are supported yet. PostgreSQL compatibility is available for testing in the form of a Docker container, which can be deployed by following these [instructions](https://ydb.tech/docs/en/postgresql/docker-connect.md?version=v25.4).

{% endnote %}
<!-- endsource: en/postgresql/_includes/alert_preview.md -->

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

<!-- source: en/postgresql/_includes/statements/create_table/syntax.md -->
```sql
CREATE [TEMPORARY | TEMP] TABLE <table name> (

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

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

);
```
<!-- endsource: en/postgresql/_includes/statements/create_table/syntax.md -->


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](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS).
3. **Column Name**: `<column name>` – the same naming rules apply as for table names.
4. **Data Type**: `<column data type>` – [standard PostgreSQL data types](https://www.postgresql.org/docs/14/datatype.html) are specified.
5. **Collation Rule**: `COLLATE` – [collation rules](https://www.postgresql.org/docs/current/collation.html) 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_pk_serial}

#|
|| Table `people` | Table `social_card` ||
||

<!-- source: en/postgresql/_includes/statements/create_table/create_table_people.md -->
```sql
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
);
```
<!-- endsource: en/postgresql/_includes/statements/create_table/create_table_people.md -->

|

<!-- source: en/postgresql/_includes/statements/create_table/create_table_social_card.md -->
```sql
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
);
```
<!-- endsource: en/postgresql/_includes/statements/create_table/create_table_social_card.md -->

||
|#


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_constraint_table}

<!-- source: en/postgresql/_includes/statements/create_table/create_table_people_const.md -->
```sql
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)
);
```
<!-- endsource: en/postgresql/_includes/statements/create_table/create_table_people_const.md -->

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_table_temp_table}

<!-- source: en/postgresql/_includes/statements/create_table/create_table_temp.md -->
```sql
CREATE TEMPORARY TABLE people (
    id serial PRIMARY KEY,
    name TEXT NOT NULL
);
```
<!-- endsource: en/postgresql/_includes/statements/create_table/create_table_temp.md -->

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


## Creating a table with sorting conditions {#create_table_collate}

<!-- source: en/postgresql/_includes/statements/create_table/create_table_sort_cond.md -->
```sql
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
);
```
<!-- endsource: en/postgresql/_includes/statements/create_table/create_table_sort_cond.md -->

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

<!-- source: en/postgresql/_includes/alert_locks.md -->
{% note info %}

Unlike PostgreSQL, YDB uses optimistic locking. This means that transactions check the conditions for the necessary locks at the end of their operation, not at the beginning. If the lock has been violated during the transaction's execution, such a transaction will end with a `Transaction locks invalidated` error. In this case, you can try to execute a similar transaction again.

{% endnote %}
<!-- endsource: en/postgresql/_includes/alert_locks.md -->