CREATE TABLE

CREATE TABLE defines a table that is persisted in durable storage.

In Materialize, you can create:

• Read-write tables. With read-write tables, users can read (SELECT) and write to the tables (INSERT, UPDATE, DELETE).

• Private Preview. Read-only tables from PostgreSQL sources (new syntax). Users cannot be write (INSERT, UPDATE, DELETE) to these tables. These tables are populated by data ingestion from a source. {{% include-example file="examples/create_table/example_postgres_table" example="syntax-version-requirement" %}}

Tables in Materialize are similar to tables in standard relational databases: they consist of rows and columns where the columns are fixed when the table is created.

Tables can be joined with other tables, materialized views, views, and subsources; and you can create views/materialized views/indexes on tables.

Syntax

{{< tabs >}} {{< tab "Read-write table" >}}

Read-write table

{{% include-syntax file="examples/create_table_user_populated" example="syntax" %}}

{{< /tab >}} {{< tab "PostgreSQL source table" >}}

PostgreSQL source table

{{< private-preview />}}

{{< note >}} {{% include-example file="examples/create_table_postgres" example="syntax-version-requirement" %}} {{< /note >}}

{{% include-syntax file="examples/create_table_postgres" example="syntax" %}}

For an example, see Create a table (PostgreSQL source).

{{< /tab >}} {{< tab "SQL Server source table" >}}

SQL Server source table

{{< private-preview />}}

{{< note >}} {{% include-example file="examples/create_table_sql_server" example="syntax-version-requirement" %}} {{< /note >}}

{{% include-syntax file="examples/create_table_sql_server" example="syntax" %}}

{{< /tab >}}

{{< /tabs >}}

Read-write tables

Table names and column names

Names for tables and column(s) must follow the naming guidelines.

Known limitations

Tables do not currently support:

• Primary keys
• Unique constraints
• Check constraints

See also the known limitations for INSERT, UPDATE, and DELETE.

Source-populated tables

{{< private-preview />}}

{{< note >}} {{% include-example file="examples/create_table_postgres" example="syntax-version-requirement" %}} {{< /note >}}

Table names and column names

Names for tables and column(s) must follow the naming guidelines.

<a name="supported-db-source-types"></a>

Read-only tables

{{< include-md file="shared-content/create-table-from-source-readonly.md" >}}

Source-populated tables and snapshotting

{{< include-md file="shared-content/create-table-from-source-snapshotting.md"

}}

Supported data types

{{< tabs >}} {{< tab "PostgreSQL" >}}

PostgreSQL types

{{% include-from-yaml data="postgres_source_details" name="postgres-supported-types" %}}

{{% include-from-yaml data="postgres_source_details" name="postgres-unsupported-types" %}}

{{< /tab >}} {{< tab "SQL Server" >}}

SQL Server types

{{< include-md file="shared-content/sql-server-supported-types.md" >}}

{{< include-md file="shared-content/sql-server-unsupported-type-handling.md" >}}

{{< /tab >}}

{{< /tabs >}}

Handling table schema changes

The use of CREATE SOURCE with CREATE TABLE FROM SOURCE allows for the handling of the upstream DDL changes, specifically adding or dropping columns in the upstream tables, without downtime. For details, see:

• PostgreSQL: Handling upstream schema changes with zero downtime

• SQL Server: Handling upstream schema changes with zero downtime

Incompatible schema changes

{{% include-from-yaml data="postgres_source_details" name="postgres-incompatible-schema-changes" %}}

Upstream table truncation restrictions

{{% include-from-yaml data="postgres_source_details" name="postgres-truncation-restriction" %}}

Inherited tables

{{% include-from-yaml data="postgres_source_details" name="postgres-inherited-tables" %}}

{{% include-from-yaml data="postgres_source_details" name="postgres-inherited-tables-action" %}}

Privileges

The privileges required to execute this statement are:

{{% include-headless "/headless/sql-command-privileges/create-table" %}}

Examples

Create a table (User-populated)

{{% include-example file="examples/create_table_user_populated" example="create-table" %}}

Once a user-populated table is created, you can perform CRUD (Create/Read/Update/Write) operations on it.

{{% include-example file="examples/create_table_user_populated" example="write-to-table" %}}

{{% include-example file="examples/create_table_user_populated" example="read-from-table" %}}

Create a table (PostgreSQL source)

{{< private-preview />}}

{{< note >}}

{{% include-example file="examples/create_table_postgres" example="syntax-version-requirement" %}}

The example assumes you have configured your upstream PostgreSQL 11+ (i.e., enabled logical replication, created the publication for the various tables and replication user, and updated the network configuration).

For details about configuring your upstream system, see the PostgreSQL integration guides.

{{</ note >}}

{{% include-example file="examples/create_table_postgres" example="create-table" %}}

{{< include-md file="shared-content/create-table-from-source-readonly.md" >}}

{{% include-example file="examples/create_table_postgres" example="read-from-table" %}}

Related pages

• INSERT
• DROP TABLE