Statements

This section covers SQL statements and clauses supported by Opteryx. Click on any topic below for detailed syntax, examples, and usage notes.

Query Clauses

The following clauses are used to construct SQL queries for retrieving and transforming data:

Clause	Purpose
SELECT	Specify columns and expressions to retrieve
WHERE	Filter rows based on conditions
GROUP BY	Group rows by one or more columns for aggregation
HAVING	Filter groups after aggregation
ORDER BY	Sort results by one or more columns
LIMIT / OFFSET	Paginate results
WITH (CTE)	Define named subqueries (Common Table Expressions)
DISTINCT	Remove duplicate rows from results

Set Operations

Combine results from multiple queries:

Operation	Purpose
UNION / INTERSECT / EXCEPT	Combine, intersect, or find differences between result sets

Data Modification

Statements for adding and changing data, and row-level statements Opteryx does not yet support:

Statement	Purpose
INSERT	Add new rows to a table
MERGE	Apply a set of changes — update, insert and delete — in one atomic statement
UPDATE	Not supported — see the page for a working alternative
DELETE	Not supported — see the page for a working alternative

Query Analysis

Understand and optimize query execution:

Statement	Purpose
EXPLAIN	Display query plans and execution metrics

Introspection

Inspect schemas, definitions, session state, and dataset metadata:

Statement	Purpose
SHOW COLUMNS	List a dataset's columns, types, and nullability
SHOW CREATE	Show the DDL that creates a table, view, materialized view, task or trigger
SHOW MANIFEST FOR	Inspect a dataset's file-level manifest and per-file statistics
SHOW SNAPSHOTS FOR	List a table's commit history, newest first
SHOW ALL SNAPSHOTS FOR	The same history plus the snapshots that have expired but not yet been purged (owner)
SHOW TRIGGERS FOR	List the refresh triggers attached to a table
SHOW VARIABLES	List session and system variables
SHOW USER	Show the current connection's identity
SHOW GRANTS	List the access policies the current connection holds

Access Control

Administer who holds what on a workspace, collection, or dataset:

Statement	Purpose
GRANT	Grant a reader, writer or owner role on an object to a user
REVOKE	Revoke a granted role from a user
SHOW GRANTS ON	List the grants held on an object

For the current session's own grants, see SHOW GRANTS above.

Session State

Statement	Purpose
SET	Assign a session or system variable

View Management

Create and manage views:

Statement	Purpose
CREATE VIEW	Create a new named view
ALTER VIEW	Modify an existing view definition
DROP VIEW	Remove a view

Materialized Views & Triggers

Materialized views store a query's result as a physical table and refresh it automatically when a source table changes:

Statement	Purpose
CREATE MATERIALIZED VIEW	Materialize a query as a self-refreshing table
ALTER MATERIALIZED VIEW	Change a view's refresh owner, or suspend and resume its refresh
DROP MATERIALIZED VIEW	Remove a materialized view and its refresh triggers
REFRESH MATERIALIZED VIEW	Rebuild a materialized view from its defining SELECT
SHOW TRIGGERS FOR	List the triggers attached to a table

A materialized view's refresh triggers are created and maintained by CREATE MATERIALIZED VIEW itself — they are not authored by hand.

Tasks & Triggers

A task is a statement the platform runs for you, on demand, on a table's commits, on a clock, or on an application signal. Where a materialized view rebuilds one SELECT in full, a task runs any statement — typically appending only what changed, which suits tables too large to rebuild:

Statement	Purpose
CREATE TASK	Define a statement the platform can run, optionally fired by a table
ALTER TASK	Redefine what a task runs, without touching its trigger
EXECUTE	Run a task now, supplying its parameters
DROP TASK	Remove a task
CREATE TRIGGER	Fire a task on a table's commits, a clock schedule, or a signal
ALTER TRIGGER	Suspend or resume a trigger, transfer its owner, or set its firing floor
DROP TRIGGER	Remove a trigger
SHOW CREATE	Show a trigger's definition

Workspace Management

Manage workspaces - the top level of the naming hierarchy:

Statement	Purpose
ALTER WORKSPACE	Set a workspace property, such as `deletion_protection`
DROP WORKSPACE	Permanently delete a workspace and everything in it

Workspaces are created through the platform, not through SQL.

Secrets

Store credentials in a workspace, encrypted and write-only, to read private buckets with READ_*:

Statement	Purpose
CREATE SECRET	Store a credential, or replace one with `CREATE OR REPLACE`
DROP SECRET	Remove a stored credential
SHOW SECRETS	List a workspace's secrets, never their values

How secrets are scoped, protected and used: Secret Management.

Collection Management

Manage collections - the layer between a workspace and its tables/views:

Statement	Purpose
CREATE COLLECTION	Create a collection
DROP COLLECTION	Remove an empty collection

Creating a collection is optional — one comes into existence anyway with the first table or view placed in it.

Table Management

Manage tables, table properties, and statistics:

Statement	Purpose
CREATE TABLE	Create a table, or materialize a query as a new or replaced table
ALTER TABLE	Add, drop, rename or widen a column; set a table's clustering columns; rename/move a table; create or drop a snapshot tag
DROP TABLE	Remove a table
TRUNCATE TABLE	Remove all rows from a table
ANALYZE TABLE	Collect statistics for query optimization
DROP STATISTICS	Discard statistics collected by `ANALYZE TABLE`
COMMENT	Add descriptive comments to tables and views

Forks

Create a dataset from another one without copying its data:

Statement	Purpose
CREATE TABLE ... CLONE	Fork a dataset — the new one borrows the original's files
ALTER TABLE ... RESYNC	Bring a fork back up to date with its source
ALTER TABLE ... DETACH	Copy what a fork borrowed, and end the relationship

A fork is an ordinary dataset: query it, write to it, time-travel it, drop it. Writes land in its own storage and never touch the files it borrowed, and the source will not expire the snapshot a fork rests on. See Sample Data for ready-made datasets worth forking.

Advanced Features

Special clauses and time-based queries:

Feature	Purpose
AT (Time Travel)	Query data as it existed at a specific point in time

JOIN Operations

For detailed information on joining tables, see the Joins reference page.

... (truncated)