Celigo Storage query SQL reference

Celigo SQL is the SQL dialect you use to query Celigo Storage files. It's a curated subset of standard SQL, PostgreSQL-compatible where syntax is otherwise ambiguous, with the exceptions documented in this article. This reference covers the supported statements, clauses, and functions, the behaviors that differ from PostgreSQL, and what Celigo rejects before a query runs.

For an introduction to querying, see About querying Celigo Storage files. To run a query end to end, see Query files in Celigo Storage.

Supported vs. unsupported SQL

Celigo SQL is a curated surface, not a passthrough to the underlying engine. Three tiers apply:

• Documented core — everything in this article. Supported and tested.
• Undocumented syntax — some SQL that isn't listed here executes anyway. It isn't supported or tested against Celigo's regression suite, and it may stop working after a platform upgrade. Don't build on it.
• Rejected — statements, functions, and references that fail validation before the query runs. See What Celigo rejects.

Statements

A request carries exactly one statement. A second statement is rejected with STORAGE_QUERY_MULTIPLE_STATEMENTS. A single trailing semicolon is fine.

No other statement type executes. Data definition (CREATE, ALTER, DROP), data manipulation (INSERT, UPDATE, DELETE), COPY in either direction, SET, PRAGMA, INSTALL, LOAD, ATTACH, EXPORT, CALL, EXPLAIN, SHOW forms that list engine state, and every other statement family are rejected with STORAGE_QUERY_NOT_SELECT. This is one of the two enforcement points that make the query API read-only; the other is the worker's read-only credentials.

Clauses and expressions

Inline mapping tables

An inline VALUES table gives you a lookup table without a file:

SELECT o.region_code, m.region_name, SUM(o.total) AS revenue
FROM '/staging/orders/' AS o
JOIN (VALUES ('NA', 'North America'), ('EU', 'Europe')) AS m(code, region_name)
  ON o.region_code = m.code
GROUP BY o.region_code, m.region_name

Window functions

Window functions attach an aggregate computed over the whole dataset to every row, without collapsing rows:

SELECT order_id, customer_id, total,
       SUM(total) OVER (PARTITION BY customer_id) AS customer_lifetime_total,
       row_number() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS recency_rank
FROM '/staging/orders/'

Row generators

generate_series and range produce rows without a file, for example to build a date spine. Three rules apply, each enforced before the query runs:

• Every argument must be a whole-number literal, so that Celigo can check the row count in advance. An expression, a column, or a parameter fails with STORAGE_QUERY_GENERATOR_ARGUMENT_NOT_LITERAL.
• Celigo multiplies the row counts of every generate_series and range call in the statement, including calls in separate UNION ALL branches, and the product must be at most 1,000,000. Exceeding that fails with STORAGE_QUERY_GENERATOR_TOO_LARGE.
• A statement that generates rows, through a generator, a recursive common table expression, or unnest of a literal list, must also read at least one storage file. A generator with no file reference fails with STORAGE_QUERY_NO_STORAGE_REFERENCE. A statement of constant expressions alone, such as SELECT 1, is allowed.

Nested data

JSON files produce nested structures, and Celigo SQL reads them directly. Column types are inferred automatically.

SELECT customer.region, item.sku, item.quantity
FROM '/staging/orders.json',
     UNNEST(items) AS t(item)
WHERE customer.region = 'EU'

In results, STRUCT values arrive as JSON objects and LIST values as arrays. A key whose values have different types across records is typed as JSON, arrives as a string containing JSON text, and is reported as a VARCHAR column. Nesting is typed at any depth unless you set maximum_depth on the JSON reader, in which case nesting below that depth is typed as JSON too. To work with a JSON-typed value as data, extract the fields you need with json_extract and cast them. See Celigo storage query read options and format behavior.

Type conversion

TRY_CAST returns NULL instead of failing when a value can't be converted. Use it on data you don't control:

SELECT order_id, TRY_CAST(total AS DECIMAL(10,2)) AS total_amount
FROM '/staging/orders/'
WHERE TRY_CAST(total AS DECIMAL(10,2)) IS NOT NULL

Behaviors that differ from PostgreSQL

These are the deviations to know before you port a query.

What Celigo rejects

Validation runs before execution. A rejected query returns a specific error naming what was rejected, and no query runs. Four checks apply:

1. Statement type — anything outside the SELECT family, and any request carrying more than one statement.
2. Functions — only allowlisted functions may appear. The allowlist is the engine's own function library minus the functions that report engine or host state, such as version(), current_setting(), and getenv(). A function outside the allowlist fails with STORAGE_QUERY_FUNCTION_NOT_ALLOWED, and the error names the function. A misspelled function name produces the same error.
3. Table references — every base-table reference must resolve to a common table expression defined in the same statement, or to a Celigo Storage file or folder path. Schema-qualified and catalog-qualified references such as information_schema.tables are rejected, as are wildcard patterns — * and ? aren't valid path characters.
4. Statement size — the SQL text is capped at 16 KB.

Rejected function families

Allowed table functions

The only table functions allowed are the file readers (read_csv, read_json, read_ndjson, read_parquet, and their _auto variants), unnest, generate_series, and range. Every other table function is rejected with STORAGE_QUERY_TABLE_FUNCTION_NOT_ALLOWED, including glob, read_text, and query. Each reader's first argument must be a literal path or a literal list of paths, and it's validated exactly like a normal file reference, so no reader can read outside your permitted folders.

Learn more

• About querying Celigo Storage files
• Query files in Celigo Storage
• Troubleshoot a Celigo Storage query job
• Celigo Storage query API reference