# CREATE TABLE

`CREATE TABLE` creates a table that is persisted in durable storage.




> For the complete documentation index, see [llms.txt](https://preview.materialize.com/materialize/40269/llms.txt).


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

In Materialize, you can create:

- [Read-write tables](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/user-populated/index.md). With read-write
  tables, users can read ([`SELECT`]) and write to the tables ([`INSERT`],
  [`UPDATE`], [`DELETE`]).

- Read-only tables from sources that use the new syntax:
  [PostgreSQL](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/postgres/index.md),
  [MySQL](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/mysql/index.md),
  [SQL Server](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/sql-server/index.md), and
  [Kafka/Redpanda](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/kafka/index.md). Users cannot write ([`INSERT`],
  [`UPDATE`], [`DELETE`]) to these tables. These tables are populated by [data
  ingestion from a source](https://preview.materialize.com/materialize/40269/markdown-docs/ingest-data/index.md).
  You must be on **v26+** to use the new syntax.


[//]: # "TODO(morsapaes) Bring back When to use a table? once there's more
clarity around best practices."

## Syntax summary



**Read-write table:**

To create a new read-write table (i.e., users can perform
[`SELECT`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/select/index.md), [`INSERT`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/insert/index.md),
[`UPDATE`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/update/index.md), and [`DELETE`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/delete/index.md) operations):
```mzsql
CREATE [TEMP|TEMPORARY] TABLE [IF NOT EXISTS] <table_name> (
  <column_name> <column_type> [NOT NULL][DEFAULT <default_expr>]
  [, ...]
)
[WITH (
  PARTITION BY (<column_name> [, ...]) |
  RETAIN HISTORY [=] FOR <duration>
)]
;

```

For details, see [CREATE TABLE: Read-write
table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/user-populated/index.md).


**PostgreSQL source table:**

To create a read-only table from a [source](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/index.md) connected
(via native connector) to an external PostgreSQL:
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name> FROM SOURCE <source_name> (REFERENCE <upstream_table>)
[WITH (
    TEXT COLUMNS (<column_name> [, ...])
  | EXCLUDE COLUMNS (<column_name> [, ...])
  | EXCLUDE CONSTRAINTS ('<constraint_name>' [, ...])
  | EXCLUDE ALL CONSTRAINTS
  | EXCLUDE ALL NOT NULL CONSTRAINTS
  | PARTITION BY (<column_name> [, ...])
  [, ...]
)]
;

```{{< include-md
file="content/headless/create-table-from-source-snapshotting.md"
>}}


For details, see [CREATE TABLE: PostgreSQL source
table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/postgres/index.md).


**MySQL source table:**

To create a read-only table from a [source](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/index.md) connected
(via native connector) to an external MySQL database:
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name> FROM SOURCE <source_name> (REFERENCE <upstream_schema>.<upstream_table>)
[WITH (
    TEXT COLUMNS (<column_name> [, ...])
  | EXCLUDE COLUMNS (<column_name> [, ...])
  | EXCLUDE CONSTRAINTS ('<constraint_name>' [, ...])
  | EXCLUDE ALL CONSTRAINTS
  | EXCLUDE ALL NOT NULL CONSTRAINTS
  | PARTITION BY (<column_name> [, ...])
  [, ...]
)]
;

```{{< include-md
file="content/headless/create-table-from-source-snapshotting.md"
>}}


For details, see [CREATE TABLE: MySQL source table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/mysql/index.md).


**SQL Server source table:**

To create a read-only table from a [source](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-source/index.md) connected
(via native connector) to an external SQL Server database:
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name> FROM SOURCE <source_name> (REFERENCE <upstream_table>)
[WITH (
    TEXT COLUMNS (<column_name> [, ...])
  | EXCLUDE COLUMNS (<column_name> [, ...])
  | PARTITION BY (<column_name> [, ...])
  [, ...]
)]
;

```{{% include-headless "/headless/create-table-from-source-snapshotting.md"
%}}


For details, see [CREATE TABLE: SQL Server source
table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/sql-server/index.md).


**Kafka source table:**


**Format Avro:**

Materialize can decode Avro messages by integrating with a schema registry
to retrieve a schema, and automatically determine the columns and data types
to use in the table.
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name>
FROM SOURCE <src_name>
FORMAT AVRO
    USING CONFLUENT SCHEMA REGISTRY CONNECTION <csr_connection_name>
      [KEY STRATEGY <key_strategy>]
      [VALUE STRATEGY <value_strategy>]
  | USING AWS GLUE SCHEMA REGISTRY CONNECTION <glue_connection_name> (
      SCHEMA NAME = '<schema_name>'
    )
[INCLUDE
    KEY [AS <name>]
  | PARTITION [AS <name>]
  | OFFSET [AS <name>]
  | TIMESTAMP [AS <name>]
  | HEADERS [AS <name>]
  | HEADER '<key>' AS <name> [BYTES]
  [, ...]
]
[ENVELOPE
    NONE
  | DEBEZIUM
  | UPSERT [ ( VALUE DECODING ERRORS = INLINE [AS <name>] ) ]
];

```



**Format JSON:**

Materialize can decode JSON messages into a single column named `data` with
type `jsonb`. Refer to the [`jsonb` type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md) documentation for
the supported operations on this type.
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name>
FROM SOURCE <src_name>
FORMAT JSON
[INCLUDE
    PARTITION [AS <name>]
  | OFFSET [AS <name>]
  | TIMESTAMP [AS <name>]
  | HEADERS [AS <name>]
  | HEADER '<key>' AS <name> [BYTES]
  [, ...]
]
[ENVELOPE NONE];

```



**Format TEXT/BYTES:**

Materialize can parse **new-line delimited** data as plain text, or read raw
bytes without applying any formatting or decoding. Text-formatted tables have
a single column, by default named `text`. Raw byte-formatted tables have a
single column, by default named `data`.
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name>
FROM SOURCE <src_name>
FORMAT TEXT | BYTES
[INCLUDE
    PARTITION [AS <name>]
  | OFFSET [AS <name>]
  | TIMESTAMP [AS <name>]
  | HEADERS [AS <name>]
  | HEADER '<key>' AS <name> [BYTES]
  [, ...]
]
[ENVELOPE NONE];

```



**Format CSV:**

Materialize can parse CSV-formatted data. The data in CSV tables is read as
[`text`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/text/index.md).
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name>
FROM SOURCE <src_name>
FORMAT CSV WITH <n> COLUMNS [DELIMITED BY <char>]
[INCLUDE
    PARTITION [AS <name>]
  | OFFSET [AS <name>]
  | TIMESTAMP [AS <name>]
  | HEADERS [AS <name>]
  | HEADER '<key>' AS <name> [BYTES]
  [, ...]
]
[ENVELOPE NONE];

```



**Format Protobuf:**

Materialize can decode Protobuf messages by integrating with a schema
registry or parsing an inline schema to retrieve a `.proto` schema
definition. It can then automatically define the columns and data types to
use in the table.
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name>
FROM SOURCE <src_name>
FORMAT PROTOBUF USING CONFLUENT SCHEMA REGISTRY CONNECTION <csr_connection_name>
  | FORMAT PROTOBUF MESSAGE '<message_name>' USING SCHEMA '<schema_bytes>'
[INCLUDE
    KEY [AS <name>]
  | PARTITION [AS <name>]
  | OFFSET [AS <name>]
  | TIMESTAMP [AS <name>]
  | HEADERS [AS <name>]
  | HEADER '<key>' AS <name> [BYTES]
  [, ...]
]
[ENVELOPE
    NONE
  | UPSERT [ ( VALUE DECODING ERRORS = INLINE [AS <name>] ) ]
];

```



**KEY FORMAT VALUE FORMAT:**

By default, the message key is decoded using the same format as the message
value. However, you can set the key and value encodings explicitly using
`KEY FORMAT ... VALUE FORMAT`.
```mzsql
CREATE TABLE [IF NOT EXISTS] <table_name>
FROM SOURCE <src_name>
KEY FORMAT <key_format> VALUE FORMAT <value_format>
-- <key_format> and <value_format> can be:
-- AVRO USING CONFLUENT SCHEMA REGISTRY CONNECTION <conn_name>
--     [KEY STRATEGY <strategy>]
--     [VALUE STRATEGY <strategy>]
-- | AVRO USING AWS GLUE SCHEMA REGISTRY CONNECTION <glue_conn_name> (SCHEMA NAME = '<schema_name>')
-- | CSV WITH <num> COLUMNS DELIMITED BY <char>
-- | JSON | TEXT | BYTES
-- | PROTOBUF USING CONFLUENT SCHEMA REGISTRY CONNECTION <conn_name>
-- | PROTOBUF MESSAGE '<message_name>' USING SCHEMA '<schema_bytes>'
[INCLUDE
    KEY [AS <name>]
  | PARTITION [AS <name>]
  | OFFSET [AS <name>]
  | TIMESTAMP [AS <name>]
  | HEADERS [AS <name>]
  | HEADER '<key>' AS <name> [BYTES]
  [, ...]
]
[ENVELOPE
    NONE
  | DEBEZIUM
  | UPSERT [(VALUE DECODING ERRORS = INLINE [AS name])]
];

```





For details, see [CREATE TABLE: Kafka source table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/kafka/index.md).




## Related pages

- [`INSERT`]
- [`DROP TABLE`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/drop-table/index.md)

[`INSERT`]: https://preview.materialize.com/materialize/40269/markdown-docs/sql/insert/index.md
[`SELECT`]: https://preview.materialize.com/materialize/40269/markdown-docs/sql/select/index.md
[`UPDATE`]: https://preview.materialize.com/materialize/40269/markdown-docs/sql/update/index.md
[`DELETE`]: https://preview.materialize.com/materialize/40269/markdown-docs/sql/delete/index.md


## Pages in this section

- [CREATE TABLE: Kafka source table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/kafka/index.md): Create a read-only table from a Kafka/Redpanda source (new syntax).
- [CREATE TABLE: MySQL source table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/mysql/index.md): Create a read-only table from a MySQL source (new syntax).
- [CREATE TABLE: PostgreSQL source table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/postgres/index.md): Create a read-only table from a PostgreSQL source (new syntax).
- [CREATE TABLE: Read-write table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/user-populated/index.md): Create a read-write, user-populated table in Materialize.
- [CREATE TABLE: SQL Server source table](https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-table/sql-server/index.md): Create a read-only table from a SQL Server source (new syntax).