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

Combine results from multiple queries:

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

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

Statement	Purpose
INSERT	Add new rows to a table
UPDATE	Not supported — see the page for a working alternative
DELETE	Not supported — see the page for a working alternative

Understand and optimize query execution:

Statement	Purpose
EXPLAIN	Display query plans and execution metrics

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

Statement	Purpose
SHOW COLUMNS	List a dataset's columns, types, and nullability
SHOW CREATE VIEW	Show the query a view was created from
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 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

Statement	Purpose
SET	Assign a session or system variable

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 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
DROP MATERIALIZED VIEW	Remove a materialized view and its refresh triggers
REFRESH MATERIALIZED VIEW	Rebuild a materialized view from its defining SELECT
DROP TRIGGER	Remove one refresh trigger from a table
SHOW TRIGGERS FOR	List the refresh triggers attached to a table

There is no CREATE TRIGGER — triggers only come into existence through CREATE MATERIALIZED VIEW.

Manage workspaces - the top level of the naming hierarchy:

Statement	Purpose
ALTER WORKSPACE	Set a workspace property, such as `deletion_protection`

Workspaces themselves are created and deleted through the platform, not through SQL.

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.

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
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

Special clauses and time-based queries:

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

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

... (truncated)