ALTER SOURCE

ALTER SOURCE changes certain characteristics of a source, such as its size.

Syntax

ALTER SOURCE IF EXISTS name alter_source_add_clause alter_source_drop_clause alter_source_set_clause

alter_source_add_clause

ADD SUBSOURCE TABLE table_name AS subsrc_name , with_options

alter_source_drop_clause

DROP SUBSOURCE TABLE subsrc_name , RESTRICT CASCADE

alter_source_set_clause

SET ( SIZE value )

with_options

WITH ( field = val , )
Field Use
name The identifier of the source you want to alter.
ADD SUBSOURCE PostgreSQL sources only: Add the identified tables from the upstream database (table_name) to the named source, with the option of choosing the name for the subsource in Materialize (subsrc_name). Supports additional options.
DROP SUBSOURCE PostgreSQL sources only: Drop the identified subsources from the source. Specifying CASCADE also drops all objects that depend on the subsource. RESTRICT (default) will not drop the subsource if it has any dependencies.
value The new value for the source size. Accepts values: 3xsmall, 2xsmall, xsmall, small, medium, large, xlarge.

ADD SUBSOURCE with_options

Field Value Description
TEXT COLUMNS A list of names Decode data as text for specific columns that contain PostgreSQL types that are unsupported in Materialize.

Context

Adding PostgreSQL subsources/tables

When adding subsources to a PostgreSQL source, Materialize opens a temporary replication slot to snapshot the new subsources' current states. After completing the snapshot, the table will be kept up-to-date, just as all other tables in the publication.

Note that using a combination of dropping and adding subsources lets you change the schema the PostgreSQL sources ingest.

Dropping PostgreSQL subsources/tables

Dropping a subsource prevents Materialize from ingesting any data from it, in addition to dropping any state that Materialize previously had for the table (such as its contents).

If a subsource encounters a deterministic error, such as an incompatible schema change (e.g. dropping an ingested column), you can drop the subsource. If you want to ingest it with its new schema, you can then add it as a new subsource.

You cannot drop the “progress subsource”.

Examples

Adding subsources

ALTER SOURCE pg_src ADD SUBSOURCE tbl_a, tbl_b AS b WITH (TEXT COLUMNS [tbl_a.col]);

Dropping subsources

ALTER SOURCE pg_src DROP SUBSOURCE tbl_a, b CASCADE;

Privileges

The privileges required to execute this statement are:

  • Ownership of the source being altered.

See also

Back to top ↑