Skip to content
Coordinator DB
View as Markdown
View as Markdown

Combine related data ​

A query-derived table stores a snapshot of related data. It can make an application's read shape explicit, but later changes to the source tables do not propagate automatically.

sql
CREATE TABLE users(id INTEGER PRIMARY KEY, name String);
CREATE TABLE actions(user_id INTEGER, kind String);
INSERT INTO users VALUES (1, 'Ada');
INSERT INTO actions VALUES (1, 'view');

Store the selected fields ​

sql
CREATE TABLE action_details AS
SELECT users.name, actions.kind
FROM actions
JOIN users ON users.id = actions.user_id;
sql
SELECT name, kind
FROM action_details
FORMAT csv;
text
"Ada","view"

Constraints and refresh ​

CREATE TABLE AS SELECT retains projected values and native declarations but does not copy source constraints. Create an explicit destination schema first when its constraints are part of the contract, then use INSERT SELECT. Add indexes for the stored table's own queries.

Materialized views are not supported. Refreshing or extending this snapshot requires another explicit data operation.

See backfilling and CREATE TABLE.