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.

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

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>
)]
;

CREATE TABLE [IF NOT EXISTS] <table_name> FROM SOURCE <source_name> (REFERENCE <upstream_table>)
[WITH (
    TEXT COLUMNS (<column_name> [, ...])
  | EXCLUDE COLUMNS (<column_name> [, ...])
  [, ...]
)]
;

SELECT refs.*
FROM mz_internal.mz_source_references refs, mz_sources s
WHERE s.name = '<source_name>' -- substitute with your source name
AND refs.source_id = s.id;

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.

PostgreSQL source tables

Table names and column names

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

Read-only tables

Source-populated tables and snapshotting

Creating the tables from sources starts the snapshotting process. Snapshotting syncs the currently available data into Materialize. Because the initial snapshot is persisted in the storage layer atomically (i.e., at the same ingestion timestamp), you are not able to query the table until snapshotting is complete.

Supported data types

Materialize natively supports the following PostgreSQL types (including the array type for each of the types):

• bool
• bpchar
• bytea
• char
• date
• daterange
• float4
• float8
• int2
• int2vector
• int4
• int4range
• int8
• int8range
• interval
• json
• jsonb
• numeric
• numrange
• oid
• text
• time
• timestamp
• timestamptz
• tsrange
• tstzrange
• uuid
• varchar

Replicating tables that contain unsupported data types is possible via the TEXT COLUMNS option. The specified columns will be treated as text; i.e., will not have the expected PostgreSQL type features. For example:

• enum: When decoded as text, the implicit ordering of the original PostgreSQL enum type is not preserved; instead, Materialize will sort values as text.

• money: When decoded as text, resulting text value cannot be cast back to numeric, since PostgreSQL adds typical currency formatting to the output.

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, without downtime.

Incompatible schema changes

All other schema changes to upstream tables (such as changing types) will set the corresponding subsource into an error state, which prevents you from reading from the source.

To handle incompatible schema changes, use DROP SOURCE and ALTER SOURCE...ADD SUBSOURCE to first drop the affected subsource, and then add the table back to the source. When you add the subsource, it will have the updated schema from the corresponding upstream table.

Upstream table truncation restrictions

Upstream tables replicated into Materialize should not be truncated. If an upstream table is truncated while replicated, the whole source becomes inaccessible and will not produce any data until it is recreated. Instead of truncating, you can use an unqualified DELETE to remove all rows from the table:

DELETE FROM t;

Inherited tables

When using PostgreSQL table inheritance, PostgreSQL serves data from SELECTs as if the inheriting tables’ data is also present in the inherited table. However, both PostgreSQL’s logical replication and COPY only present data written to the tables themselves, i.e. the inheriting data is not treated as part of the inherited table.

PostgreSQL sources use logical replication and COPY to ingest table data, so inheriting tables’ data will only be ingested as part of the inheriting table, i.e. in Materialize, the data will not be returned when serving SELECTs from the inherited table.

You can mimic PostgreSQL’s SELECT behavior with inherited tables by creating a materialized view that unions data from the inherited and inheriting tables (using UNION ALL). However, if new tables inherit from the table, data from the inheriting tables will not be available in the view. You will need to add the inheriting tables via ADD SUBSOURCE and create a new view (materialized or non-) that unions the new table.

Privileges

The privileges required to execute this statement are:

• CREATE privileges on the containing schema.
• USAGE privileges on all types used in the table definition.
• USAGE privileges on the schemas that all types in the statement are contained in.

Examples

Create a table (User-populated)

The following example uses CREATE TABLE to create a new read-write table mytable with two columns a (of type int) and b (of type text and not nullable):

CREATE TABLE mytable (a int, b text NOT NULL);

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

The following example uses INSERT to write two rows to the table:

INSERT INTO mytable VALUES
(1, 'hello'),
(2, 'goodbye')
;

The following example uses SELECT to read all rows from the table:

SELECT * FROM mytable;

The results should display the two rows inserted:

| a | b       |
| - | ------- |
| 1 | hello   |
| 2 | goodbye |

Create a table (PostgreSQL source)

To create new read-only tables from a source table, use the CREATE TABLE ... FROM SOURCE ... (REFERENCE <upstream_table>) statement. The following example creates read-only tables items and orders from the PostgreSQL source’s public.items and public.orders tables (the schema is public).

/* This example assumes:
  - In the upstream PostgreSQL, you have defined:
    - replication user and password with the appropriate access.
    - a publication named `mz_source` for the `items` and `orders` tables.
  - In Materialize:
    - You have created a secret for the PostgreSQL password.
    - You have defined the connection to the upstream PostgreSQL.
    - You have used the connection to create a source.

   For example (substitute with your configuration):
      CREATE SECRET pgpass AS '<replication user password>'; -- substitute
      CREATE CONNECTION pg_connection TO POSTGRES (
        HOST '<hostname>',          -- substitute
        DATABASE <db>,              -- substitute
        USER <replication user>,    -- substitute
        PASSWORD SECRET pgpass
        -- [, <network security configuration> ]
      );

      CREATE SOURCE pg_source
      FROM POSTGRES CONNECTION pg_connection (
        PUBLICATION 'mz_source'       -- substitute
      );
*/

CREATE TABLE items
FROM SOURCE pg_source(REFERENCE public.items)
;
CREATE TABLE orders
FROM SOURCE pg_source(REFERENCE public.orders)
;

Creating the tables from sources starts the snapshotting process. Snapshotting syncs the currently available data into Materialize. Because the initial snapshot is persisted in the storage layer atomically (i.e., at the same ingestion timestamp), you are not able to query the table until snapshotting is complete.

Once the snapshotting process completes and the table is in the running state, you can query the table:

SELECT * FROM items;

Related pages

• INSERT
• DROP TABLE