GroveStreams Help Center
Grammar

TEQ Syntax	Grammar - DDL
Temporal Parameters	Date Functions
Control Functions	Joins	String Functions	Other Functions
Predicates	Numeric Functions	Constants	JSON Functions
Operators	Aggregate Functions	Table Functions	Compound Operators
Transactions	Permissions	Extensions	Maintenance
Schemas

Term Description Example

Query

SELECT Selects data from one or more tables. SELECT column1, column2, ... FROM table_name

DISTINCT Used to return only distinct (different) values. SELECT DISTINCT column1, column2, ... FROM table_name

FROM Selects data from one or more tables. Accepts system table names (system.stream, system.active_event, etc.) for TDQ queries and component template IDs for TEQ queries. Select _component_name, _name From system.stream
Select _component_name, temperature From sensor

WHERE Used to filter records by extracting only those records that fulfill a specified condition. SELECT column1, column2, ... FROM table_name WHERE condition;

GROUP BY The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of components in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

Aliases from SELECT are supported in GROUP BY. SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

SELECT year(_time) as y, COUNT(*) FROM system.stream GROUP BY y;

HAVING A HAVING clause specifies that a SELECT statement must only return rows where aggregate values meet the specified conditions. HAVING and WHERE are often confused by beginners, but they serve different purposes. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

SELECT year(_time) as y, COUNT(*) FROM system.stream GROUP BY y;

ORDER BY Used to sort the result-set in ascending or descending order. Ascending is the default.

Sort columns can be column names, column indexes, or expressions that resolve to a name or index. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s), ... ASC|DESC;

LIMIT OFFSET Use the LIMIT and OFFSET clauses to retrieve a portion of rows returned. row_count determines the number of rows that will be returned. The OFFSET clause skips the offset rows before beginning to return the rows.

Either clause may appear without the other. OFFSET N on its own (no preceding LIMIT) is supported and matches PostgreSQL semantics. SELECT column1, column2, ... FROM table_name LIMIT row_count OFFSET offset;
SELECT column1, column2, ... FROM table_name OFFSET 100;
SELECT column1, column2, ... FROM table_name LIMIT 50;

FOR UPDATE,
FOR SHARE,
FOR NO KEY UPDATE,
FOR KEY SHARE SELECT ... FROM ... [WHERE ...] FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [NOWAIT | SKIP LOCKED]

Accepted on SELECT for PostgreSQL compatibility. Parsed and validated, then ignored at execution time — GroveStreams does not currently use row-level locking, so the clause has no effect on the result set or on concurrent writers. Queries that include it return the same rows they would without it.

The optional NOWAIT and SKIP LOCKED wait modifiers are also accepted and ignored. Provided so that BI tools, ORMs, and PostgreSQL-flavored queries that emit these clauses run unchanged. SELECT _component_name FROM account WHERE balance < 0 FOR UPDATE;
SELECT * FROM job_queue WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
SELECT * FROM ledger FOR SHARE;

VALUES VALUES (expr, ...) [, (expr, ...)] ... [ORDER BY ...] [LIMIT ...] [OFFSET ...]

Top-level VALUES produces an inline row set without a FROM clause — equivalent to a UNION ALL of single-row SELECTs. Useful for ad-hoc constant tables and for BI tools that probe with VALUES (1). All rows must have the same column count; column types are unified per column using the standard coercion hierarchy.

Trailing ORDER BY, LIMIT, and OFFSET clauses are supported. VALUES (1, 'a'), (2, 'b'), (3, 'c');
VALUES (1) AS t(n);
VALUES ('x'), ('y'), ('z') ORDER BY 1 DESC LIMIT 2;

VALUES
(in FROM)

SELECT ... FROM (VALUES (expr, ...), ... ) [AS alias [(col_name, ...)]]

A VALUES clause may appear anywhere a sub-select is allowed in FROM — it materializes a constant inline relation. Pair it with an AS alias(col, ...) clause to name the columns; without the alias columns are named column1, column2, … columnN.

Edge cases:

Every row must have the same width; mismatched arities raise a parse-time error.
Column types are inferred from the first row. Subsequent rows must be type-compatible (standard coercion hierarchy applies); otherwise the query errors at plan time.
NULL is allowed in any row; the column type then comes from the first non-NULL row (or defaults to STRING if every row is NULL in that column).
The alias column list, when present, must have the same length as each row.

SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(id, label);
SELECT id FROM (VALUES (1), (2), (3)) AS t(id) WHERE id > 1;
SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t → default column names column1, column2

Data Modification (INSERT, UPDATE, DELETE)

Organization-Level Operation Settings
Organization administrators can enable or disable each operation type at the organization level via the Organization Information window (Query tab) or the REST API. These settings act as a kill switch independent of user-level RBAC permissions. Settings are split into two groups:

Data Permissions — controls operations on sample data (INSERT OR REPLACE INTO system.sample, UPDATE system.sample, DELETE FROM system.sample):

Setting	Default	Description
gsqlAllowInsert	true	Insert or replace sample data
gsqlAllowUpdate	true	Update sample data at specific timestamps
gsqlAllowDelete	false	Delete sample data

Component & Template Permissions — controls operations that create, modify, or remove components and templates:

Setting	Default	Description
gsqlAllowInsertEntity	true	Create components and their streams
gsqlAllowUpdateEntity	true	Update component stream values
gsqlAllowDeleteEntity	false	Delete components and their streams
gsqlAllowCreateTable	true	Create templates (CREATE TABLE)
gsqlAllowAlterTable	true	Modify templates (ALTER TABLE)
gsqlAllowDropTable	false	Delete templates and their linked components (DROP TABLE). Matches standard SQL — DROP TABLE always removes the table and its data.
gsqlAllowDropTableCascade	false	Vestigial as of 2026-06-01: DROP TABLE is cascading-by-default; this flag is no longer evaluated. CASCADE will be re-armed once GS tracks cross-template dependent objects.
gsqlAllowCreateOtherTools	true	Create resource entities (CREATE CYCLE, ROLLUP CALENDAR, STREAM GROUP, RUNNABLE, AGENT)
gsqlAllowAlterOtherTools	true	Modify resource entities (ALTER CYCLE, ROLLUP CALENDAR, STREAM GROUP, RUNNABLE, AGENT)
gsqlAllowDropOtherTools	false	Delete resource entities (DROP CYCLE, ROLLUP CALENDAR, STREAM GROUP, RUNNABLE, AGENT)
gsqlAllowRunOtherTools	true	Execute runnables and agents (RUN RUNNABLE, RUN AGENT)
gsqlAllowCreateMaterializedView	false	Create materialized views (CREATE MATERIALIZED VIEW)
gsqlAllowAlterMaterializedView	false	Modify materialized views (ALTER MATERIALIZED VIEW)
gsqlAllowDropMaterializedView	false	Delete materialized views (DROP MATERIALIZED VIEW)
gsqlAllowRefreshMaterializedView	false	Refresh materialized views (REFRESH MATERIALIZED VIEW)
gsqlAllowCreateView	false	Create live views (CREATE VIEW)
gsqlAllowAlterView	false	Modify live views (ALTER VIEW)
gsqlAllowDropView	false	Delete live views (DROP VIEW)

When an operation is disabled, any attempt to execute it returns an error: "GS SQL [operation] operations are disabled for this organization. Contact your org administrator."

INSERT
(system.sample table)

INSERT OR REPLACE INTO system.sample [WITH (options)]
(columns) VALUES ( { NULL | expression } ), ...
| SELECT select_criteria

Inserts or replaces sample data in the system.sample table. "OR REPLACE" is mandatory for system.sample inserts.

Required columns:

(_COMPONENT_UID and _UID) or (_COMPONENT_ID and _ID): Use either pair to identify the stream being updated. Inserts are slightly faster when UIDs are used.
_TIME, _TIME_SD, _TIME_ED: The sample time. Not required for point streams. Required for regular and interval streams. Use _TIME if the time is the start datetime for interval streams. Use _TIME_ED if the time is the end of the interval.
_SAMPLE: The value being saved for the included _TIME.

WITH options (optional — option names are case-insensitive and accept both underscore and camelCase forms, e.g. COMP_TMPL_ID or compTmplId):

COMP_TMPL_ID = 'id': Uses a component template when the insert is creating a new component/stream and IDs are used as identifiers.
FOLDER_PATH = 'path': Folder to place newly created components into. Must start with '/Components'. Defaults to '/Components'. Created if it does not exist. Also accepts FOLDER.
CNAME = 'name': Display name for auto-created components. Defaults to the _COMPONENT_ID value.
DESCRIPTION = 'text': Description for auto-created components.
TIMEZONE_ID = 'timezone': Timezone for auto-created components (e.g., 'America/New_York'). Defaults to the template's timezone.
DERIVE_MAX_STREAMS = n: Maximum number of streams to derive after insert. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional: both WITH (COMP_TMPL_ID = 'x') and WITH COMP_TMPL_ID = 'x' are valid. A component and stream will be created if they do not exist when IDs are used.

INSERT OR REPLACE INTO system.sample(_COMPONENT_UID, _UID, _TIME, _SAMPLE) VALUES('a1dbf630-72bc-3b52-b0b5-49f33c2f1d81', '2e75e358-2924-3ec2-b763-fba82abc648f', 1736488800000, 2.0 ),('a1dbf630-72bc-3b52-b0b5-49f33c2f1d81', '2e75e358-2924-3ec2-b763-fba82abc648f', 1736488801000, 3.0 );

-- Auto-create component from template with metadata:
INSERT OR REPLACE INTO system.sample WITH ( COMP_TMPL_ID = 'accountTemplate', CNAME = 'Acme Corp', FOLDER_PATH = '/Components/Clients', DESCRIPTION = 'Enterprise client account', TIMEZONE_ID = 'America/New_York') (_COMPONENT_ID, _ID, _TIME, _SAMPLE) VALUES('acme_corp', 'monthly_revenue', 1736488800000, 50000.00);

-- Bulk from CSV_TABLE:
INSERT OR REPLACE INTO system.sample WITH (COMP_TMPL_ID = 'productTemplate') (_COMPONENT_ID, _ID, _TIME, _SAMPLE) SELECT sku, 'units_sold', sale_date, quantity FROM CSV_TABLE(@HTTP_BODY) WITH (1 sku STRING, 2 sale_date LONG, 3 quantity DOUBLE);

INSERT
(Template tables)

INSERT INTO template_table [WITH (options)] [(columns)] VALUES (values)
| SELECT select_criteria

Creates new components from a template table (TEQ). "OR REPLACE" is not used for template table inserts.

Columns:

_component_id (mandatory): The component ID — the primary key for the new component.
Stream ID columns: Any stream ID defined in the template. Values are appended as samples at the current time (NOW).

PG-style column-less form: The column list is optional. When omitted, values map positionally to the template's streams in their declaration order, and the first value is also used as the row identifier (no separate _component_id column required). When the template has a PRIMARY KEY stream, the PK value supplies the row identifier. WITH options (optional, apply to all inserted components — option names are case-insensitive and accept both underscore and camelCase forms):

CNAME = 'name': Component display name. Defaults to the _component_id value.
FOLDER_PATH = 'path': Folder placement. Defaults to '/Components'. Created if it does not exist. Also accepts FOLDER.
DESCRIPTION = 'text': Sets the component description.
TIMEZONE_ID = 'timezone': Sets the component timezone (e.g., 'America/New_York'). Defaults to the template's timezone.
DERIVE_MAX_STREAMS = n: Maximum streams to derive after insert. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional. The component is created from the template and placed in the specified folder. If stream columns are included, their values are inserted as samples at NOW after the component is created.

Returns a single row with the number of rows affected.

-- Create a component (_component_name defaults to _component_id):
INSERT INTO account (_component_id) VALUES ('acct_10042');

-- Create with initial stream values:
INSERT INTO account (_component_id, balance, credit_limit) VALUES ('acct_10042', 15000.00, 50000.00);

-- Create with metadata:
INSERT INTO account WITH ( CNAME = 'Acme Corp Operating', FOLDER_PATH = '/Components/Enterprise', DESCRIPTION = 'Primary operating account', TIMEZONE_ID = 'America/New_York') (_component_id, balance) VALUES ('acct_10042', 15000.00);

-- Bulk insert:
INSERT INTO product (_component_id, price, quantity_on_hand) VALUES ('SKU-001', 29.99, 500), ('SKU-002', 49.99, 200), ('SKU-003', 14.99, 1000);

-- Insert from a query:
INSERT INTO product (_component_id, price) SELECT sku, msrp FROM CSV_TABLE('...');

-- PG-style column-less form (values map to streams in declaration order;
-- the first value is also the row identifier):
INSERT INTO product VALUES ('SKU-001', 29.99, 500);

UPDATE
(Template tables)

UPDATE template_table [WITH (options)] SET column = value [, ...] [WHERE condition]

Appends new sample values at the current time (NOW) for components matching the WHERE clause.

The SET clause specifies stream ID columns and their new values. Only stream columns defined in the template are allowed — system columns (_component_id, _component_name, _component_uid, etc.) cannot be updated.

The WHERE clause filters which components are affected. It supports the same predicates as TEQ SELECT queries (e.g., filtering by _component_id, _component_name, _component_uid, or stream values). If omitted, all components of the template are updated.

WITH options (optional, applied to all matched components — option names are case-insensitive and accept both underscore and camelCase forms):

DESCRIPTION = 'text': Sets the component description on matched components.
TIMEZONE_ID = 'timezone': Sets the component timezone (e.g., 'Asia/Tokyo').
DERIVE_MAX_STREAMS = n: Maximum streams to derive after update. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional. Returns a single row with the number of rows affected.

-- Update balance for a specific account:
UPDATE account SET balance = 12000.00 WHERE _component_id = 'acct_10042';

-- Update multiple streams:
UPDATE product SET price = 24.99, quantity_on_hand = 450 WHERE _component_name = 'Widget A';

-- Update with description and timezone:
UPDATE account WITH (DESCRIPTION = 'Flagged for review', TIMEZONE_ID = 'Asia/Tokyo') SET credit_limit = 0 WHERE balance < 0;

UPDATE
(system.sample table)

UPDATE system.sample [WITH (options)] SET _sample = value WHERE _uid = 'stream_uid' AND _time = epoch_millis

Updates a sample value at a specific timestamp. Uses INSERT OR REPLACE semantics internally.

The WHERE clause must specify the stream UID and the exact time of the sample to update.

WITH options (optional — option names are case-insensitive and accept both underscore and camelCase forms):

DERIVE_MAX_STREAMS = n: Maximum number of streams to derive after update. Default is 0 (no derivation). Maximum is 20.
DERIVE_TIMEOUT = n: Derivation timeout in milliseconds. Default is 10000 (10 seconds). Maximum is 15000 (15 seconds).

Parentheses around WITH options are optional.

UPDATE system.sample SET _sample = 52000.00
WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f'
AND _time = 1736488800000;

DELETE
(Template tables) DELETE FROM template_table WHERE condition

Deletes components (and all their streams and sample data) that match the WHERE clause.

The WHERE clause filters which components are removed. It supports the same predicates as TEQ SELECT queries. This is a cascading delete — the component, all its streams, all sample data, and all index entries are removed.

Returns a single row with the number of rows affected. -- Delete a specific component:
DELETE FROM sensor WHERE _component_name = 'Sensor_B';

-- Delete components matching a condition:
DELETE FROM sensor WHERE temperature < 0;

DELETE
(system.sample table) DELETE FROM system.sample WHERE _uid = 'stream_uid' [AND _time >= start AND _time <= end]

Deletes sample data from a stream. The WHERE clause must specify the stream UID. Optionally, include _time conditions to delete only samples within a time range.

Without _time conditions, all sample data for the stream is deleted. The stream itself (metadata) is preserved. -- Delete all sample data from a stream:
DELETE FROM system.sample WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f';

-- Delete samples in a time range:
DELETE FROM system.sample WHERE _uid = '2e75e358-2924-3ec2-b763-fba82abc648f' AND _time >= 1736488800000 AND _time <= 1736575200000;

Transactions

Transaction-control statements are accepted by GS SQL so that PostgreSQL-flavored clients, BI tools, ORMs, and connection pools that always wrap work in BEGIN ... COMMIT can run unchanged. They are parsed, validated, and acknowledged with a successful result, but they currently have no effect on durability or visibility — every statement is committed individually as it executes. Use them for tool compatibility; do not rely on them for atomic multi-statement work.

BEGIN
START TRANSACTION BEGIN [TRANSACTION | WORK]
START TRANSACTION

Marks the start of a transaction block. Accepted; no rollback boundary is established. The optional TRANSACTION and WORK keywords are both honored. BEGIN;
BEGIN TRANSACTION;
BEGIN WORK;
START TRANSACTION;

COMMIT COMMIT [TRANSACTION | WORK]

Accepted as a no-op. Since each statement is already committed when it runs, an explicit COMMIT simply returns success. COMMIT;
COMMIT TRANSACTION;
COMMIT WORK;

ROLLBACK ROLLBACK [TRANSACTION | WORK]

Raises an error. Transactions are not yet implemented, and every statement before the ROLLBACK has already been committed. Silently accepting the ROLLBACK would mislead callers about whether their writes survived, so GS SQL fails the statement explicitly. The exact error message is:

ROLLBACK is not supported: transactions are not yet implemented. Prior statements have already committed and cannot be rolled back.

This is the one transaction-control statement that does not silently no-op. ROLLBACK;
ROLLBACK TRANSACTION;
ROLLBACK WORK;

SAVEPOINT SAVEPOINT name
RELEASE [SAVEPOINT] name
ROLLBACK TO [SAVEPOINT] name

Savepoint statements are accepted and acknowledged for client compatibility. They do not create restore points; ROLLBACK TO [SAVEPOINT] is treated as an intra-transaction no-op (the surrounding BEGIN/COMMIT are already no-ops, so there is nothing to rewind). Unlike a bare ROLLBACK, ROLLBACK TO does not raise an error. SAVEPOINT s1;
RELEASE SAVEPOINT s1;
ROLLBACK TO s1;
ROLLBACK TO SAVEPOINT s1;

DEALLOCATE DEALLOCATE [PREPARE] {ALL | name}

Accepted; no-op. GS SQL has no prepared-statement session state to clear, so DEALLOCATE ALL and DEALLOCATE name both return success without touching anything. The optional PREPARE keyword is honored for PG-compat parity. DEALLOCATE ALL;
DEALLOCATE PREPARE ALL;
DEALLOCATE stmt1;

Permissions (GRANT, REVOKE, ROLES)

GroveStreams uses its own role-based access control model — roles are managed through the organization's user, group, and role settings, not through SQL grants. The PostgreSQL GRANT and REVOKE statements are parsed for client compatibility but are rejected with an error at execution time so callers are never misled into thinking permissions were changed. To actually change who can do what, edit the role assignment for the user or group.

GRANT GRANT privilege [, ...] ON object_name [, ...] TO role_name [, ...]

Raises an error. No privilege change is made. The exact error message is:

GRANT/REVOKE is not supported via SQL — manage permissions via the org RBAC UI. The statement was rejected; permissions were NOT changed.

Use the organization's role assignments to grant capabilities. GRANT SELECT ON sensor TO analyst;
GRANT ALL ON account TO admin_role;

REVOKE REVOKE privilege [, ...] ON object_name [, ...] FROM role_name [, ...]

Raises an error. No privilege change is made. The exact error message is:

GRANT/REVOKE is not supported via SQL — manage permissions via the org RBAC UI. The statement was rejected; permissions were NOT changed.

Use the organization's role assignments to revoke capabilities. REVOKE INSERT ON sensor FROM analyst;
REVOKE ALL ON account FROM guest;

CREATE ROLE / CREATE USER CREATE ROLE name [WITH options...]
CREATE USER name [WITH options...]

Accepted; no-op. GS uses its own role-based access control — users, groups, and roles are managed through the organization's user/group/role settings, not through SQL. CREATE USER is parsed identically to CREATE ROLE (PG-compat alias). Any WITH options (PASSWORD, LOGIN, SUPERUSER, INHERIT, CONNECTION LIMIT, VALID UNTIL, …) are parsed and ignored. CREATE ROLE analyst;
CREATE ROLE etl WITH LOGIN PASSWORD 'x';
CREATE USER bob WITH SUPERUSER;

DROP ROLE / DROP USER DROP ROLE [IF EXISTS] name [, ...]
DROP USER [IF EXISTS] name [, ...]

Accepted; no-op. As with CREATE ROLE, GS RBAC is managed outside of SQL, so the statement parses, validates, and returns success without removing anything. DROP ROLE analyst;
DROP ROLE IF EXISTS etl, loader;
DROP USER bob;

SET SESSION AUTHORIZATION SET SESSION AUTHORIZATION {DEFAULT | rolespec}
RESET SESSION AUTHORIZATION

Accepted; no-op. The session user is fixed at connect time by the GS authentication flow, so the PG runtime-role-switch statement parses and returns success without changing identity. Both SET and RESET forms are recognized. SET SESSION AUTHORIZATION 'analyst';
SET SESSION AUTHORIZATION DEFAULT;
RESET SESSION AUTHORIZATION;

Schemas

GS SQL organizes objects into catalogs rather than PostgreSQL schemas; the default unqualified namespace is public. The PG schema statements below are accepted as no-ops so that PG-flavored migration scripts and ORMs that issue CREATE SCHEMA at bootstrap time run unchanged.

CREATE SCHEMA CREATE SCHEMA [IF NOT EXISTS] name [AUTHORIZATION role]

Accepted; no-op. No schema object is created; resolution continues to use catalogs and the public default namespace. CREATE SCHEMA reporting;
CREATE SCHEMA IF NOT EXISTS staging;
CREATE SCHEMA reporting AUTHORIZATION analyst;

DROP SCHEMA DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT]

Accepted; no-op. CASCADE and RESTRICT are parsed and ignored. DROP SCHEMA reporting;
DROP SCHEMA IF EXISTS staging, archive CASCADE;

ALTER SCHEMA ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO role

Accepted; no-op. ALTER SCHEMA reporting RENAME TO analytics;
ALTER SCHEMA reporting OWNER TO analyst;

Extensions

GroveStreams ships with its temporal, AI, JSON, regex, and geographic functions built in — there is no extension system to enable separately. The PostgreSQL CREATE EXTENSION / DROP EXTENSION statements are accepted as no-ops so that PG-flavored bootstrap scripts run unchanged. Looking for an earthdistance-style function? See EARTH_DISTANCE and EARTH_DISTANCE_KM, which are available without any CREATE EXTENSION call.

CREATE EXTENSION CREATE EXTENSION [IF NOT EXISTS] extension_name

Accepted; nothing is installed and nothing is changed. CREATE EXTENSION IF NOT EXISTS earthdistance;
CREATE EXTENSION pg_trgm;

DROP EXTENSION DROP EXTENSION [IF EXISTS] extension_name

Accepted; no-op. DROP EXTENSION IF EXISTS earthdistance;

Maintenance (VACUUM, TRUNCATE, COMMENT ON, LOCK)

GroveStreams manages compaction, statistics, and storage automatically — there is no manual VACUUM or ANALYZE step. Most maintenance statements (VACUUM, COMMENT ON, LOCK, CREATE/DROP/REINDEX INDEX) are accepted as silent no-ops for PostgreSQL compatibility so that scheduled-maintenance scripts and migration tools run unchanged. TRUNCATE is the exception — it raises an error instead of silently doing nothing, because callers expect rows to be gone; use DELETE FROM ... instead.

VACUUM VACUUM [FULL | FREEZE | VERBOSE | ANALYZE] [tablename]

Accepted; no-op. Storage compaction and statistics are maintained automatically. Any combination of the standard PostgreSQL options is parsed and ignored. VACUUM;
VACUUM ANALYZE sensor;
VACUUM FULL VERBOSE;

TRUNCATE TRUNCATE [TABLE] tablename [, ...] [RESTART IDENTITY | CONTINUE IDENTITY] [CASCADE | RESTRICT]

Raises an error. Parsed for PG compatibility, but rejected at execution time so callers are not misled into thinking rows were removed. The exact error message is:

TRUNCATE is not supported. Use DELETE FROM ... instead. No rows were removed.

The optional TABLE keyword, identity clauses (RESTART IDENTITY / CONTINUE IDENTITY), and cascade clauses (CASCADE / RESTRICT) are parsed for PostgreSQL compatibility but do not change the outcome — every form errors out. To remove all components in a template, use DELETE FROM tablename with no WHERE clause (still subject to gsqlAllowDeleteEntity, default false). TRUNCATE sensor;
TRUNCATE TABLE sensor, meter;
TRUNCATE TABLE sensor RESTART IDENTITY CASCADE;

COMMENT ON COMMENT ON { TABLE | COLUMN | SCHEMA } object_name IS 'text'

Accepted for PostgreSQL compatibility; no-op. To attach a description to a template or stream, edit the template definition (descriptions live on the template and stream resources, not as SQL comments). COMMENT ON TABLE sensor IS 'Field sensors';
COMMENT ON COLUMN sensor.temperature IS 'Degrees Celsius';

LOCK LOCK [TABLE] tablename [IN lockmode MODE]

Accepted for PostgreSQL compatibility; no table-level lock is taken. The optional TABLE keyword and any standard PostgreSQL lock-mode are parsed and ignored. LOCK sensor;
LOCK TABLE sensor IN ACCESS EXCLUSIVE MODE;

CREATE INDEX CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] name
ON [ONLY] tbl [USING method]
(col_expr [opclass] [, ...])
[INCLUDE (cols)] [WHERE pred] [WITH (...)]

Accepted; no-op. GS auto-indexes streams — there is no separate secondary-index object to create. The full PostgreSQL grammar (UNIQUE, CONCURRENTLY, IF NOT EXISTS, USING method, per-column operator classes, INCLUDE, partial-index WHERE, storage WITH options) is parsed and ignored. Use this for migration scripts that issue CREATE INDEX at bootstrap. CREATE INDEX idx_sensor_ts ON sensor (ts);
CREATE UNIQUE INDEX IF NOT EXISTS idx_meter_sn ON meter (serial);
CREATE INDEX CONCURRENTLY idx_event_kind ON event USING btree (kind) WHERE kind != 'noise';

DROP INDEX DROP INDEX [CONCURRENTLY] [IF EXISTS] name [, ...] [CASCADE | RESTRICT]

Accepted; no-op. CONCURRENTLY, IF EXISTS, multi-name lists, and the CASCADE/RESTRICT trailer are all parsed and ignored. DROP INDEX idx_sensor_ts;
DROP INDEX IF EXISTS idx_meter_sn, idx_event_kind;
DROP INDEX CONCURRENTLY idx_event_kind CASCADE;

REINDEX REINDEX [VERBOSE] {INDEX | TABLE | SCHEMA | DATABASE | SYSTEM} name

Accepted; no-op. The target keyword (INDEX, TABLE, SCHEMA, DATABASE, SYSTEM) and the optional VERBOSE are parsed and ignored. REINDEX TABLE sensor;
REINDEX INDEX idx_sensor_ts;
REINDEX VERBOSE DATABASE mydb;

Data Definition (DDL)

DDL statements (CREATE TABLE, ALTER TABLE, DROP TABLE, Resource DDL, View DDL, and Aggregation columns) have moved to the Grammar - DDL page.

Template DDL — CREATE TABLE, ALTER TABLE, DROP TABLE
Resource DDL — Cycles, Rollup Calendars, Stream Groups, Runnables
INSERT INTO — Component creation
Derived Columns — Expressions — auto-computed stream formulas
Derived Columns — Aggregation — stream group aggregations
Views — Materialized and live views

Common Table Expressions (CTEs)

WITH [RECURSIVE]

WITH [RECURSIVE] cte_name [(column_name [, column_name ...])] AS (subquery)
    -- SELECT or UPDATE using cte_name

The WITH clause in GS SQL allows you to define one or more named subqueries, known as Common Table Expressions (CTEs), which can be used in the main query. This is particularly useful for simplifying complex queries by breaking them down into simpler parts, or for reusing the same subquery multiple times within a single SQL statement.

Syntax:
WITH cte_name AS (subquery) SELECT ... FROM cte_name ...;

Recursive CTE Syntax:
WITH RECURSIVE cte_name [(column_name [, ...])] AS (
   anchor_select
   UNION ALL
   recursive_select -- references cte_name
)
SELECT ... FROM cte_name ...;

The RECURSIVE keyword enables a CTE to reference itself, allowing hierarchical or iterative queries. The CTE body must contain an anchor member (a SELECT that does not reference the CTE) and a recursive member (a SELECT that references the CTE), connected by UNION ALL. Execution starts with the anchor, then repeatedly executes the recursive member using only the rows produced in the previous iteration, until no new rows are generated (fixed point) or the maximum recursion depth is reached.

The default maximum recursion depth is 100 iterations. If the recursive member does not have a proper termination condition (e.g., a WHERE clause that eventually produces no rows), the query will fail with an error at the maximum depth.

Functionality:

You can define multiple CTEs by separating them with commas.
Each CTE can be used in subsequent CTEs or in the main query, but not in previous CTEs.
CTEs do not persist after the statement ends; they are temporary constructs for the duration of the query execution.
Recursive CTEs must use UNION ALL between anchor and recursive members. UNION, INTERSECT, and EXCEPT are not supported in recursive CTEs.

Limitations:

GS SQL only supports using the WITH statement within SELECT and INSERT SQL statements.
Recursive CTEs only support UNION ALL (not UNION, INTERSECT, or EXCEPT) between anchor and recursive members.

WITH filtered_names AS (SELECT _component_name, _name FROM system.stream WHERE _name LIKE '*kWh*')
SELECT _component_name, _name FROM filtered_names;

WITH filtered_names (compName, streamName) AS (SELECT _component_name, _name FROM system.stream WHERE _name LIKE '*kWh*')
SELECT compName as 'Meter Name', streamName as 'channel' FROM filtered_names;

WITH count_per_cname AS ( SELECT _component_name, COUNT(_name) AS count FROM system.stream GROUP BY _component_name )
SELECT s._component_name, s._name, c.count FROM system.stream s JOIN count_per_cname c ON s._component_name = c._component_name;

WITH filtered_streams AS ( SELECT _component_name, _name FROM system.stream WHERE _component_name = 'c1' ),
   count_per_cname AS ( SELECT _component_name, COUNT(_name) AS name_count FROM filtered_streams GROUP BY _component_name )
SELECT fs._component_name, fs._name, c.name_count FROM filtered_streams fs JOIN count_per_cname c ON fs._component_name = c._component_name ORDER BY c.name_count DESC;

WITH filtered_streams (_component_name, _name) AS ( SELECT _component_name, _name FROM system.stream WHERE _name LIKE '*kWh*' ),
   high_kWh_streams (_component_name, name_count) AS ( SELECT _component_name, COUNT(_name) AS name_count FROM filtered_streams GROUP BY _component_name HAVING COUNT(_name) > 1 )
SELECT _component_name, name_count FROM high_kWh_streams;

WITH items_to_insert (_component_id, _id, _time, _sample) AS (SELECT 'component1', 'stream1', NOW, 10)
INSERT OR REPLACE INTO system.sample (_component_id, _id, _time, _sample) SELECT _component_id, _id, _time, _sample FROM items_to_insert

WITH comp_to_copy AS (SELECT _component_id, _id, _time, _sample FROM system.stream WHERE _component_id = 'c1')
INSERT OR REPLACE INTO system.sample WITH (COMP_TMPL_ID = 'ComponentTemplate1') (_component_id, _id, _time, _sample) SELECT 'c1_copy', _id, _time, _sample FROM comp_to_copy

Recursive CTE — numeric sequence:
WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 10
)
SELECT n FROM cnt;

Recursive CTE — multiple columns:
WITH RECURSIVE powers(n, val) AS (
  SELECT 1, 2
  UNION ALL
  SELECT n + 1, val * 2 FROM powers WHERE n < 8
)
SELECT n, val FROM powers;

User-Defined Variables

SET SET @var_name = expr
SET @var_name = (subquery)

A value can be stored in a user-defined variable by one statement and be referred to later in another statement. This enables a value to be passed from one statement to another. The same variable can be reassigned a different value, using the SET statment again, within the same session. User-defined variables are session specific. User variable names are not case-sensitive. Names have a maximum length of 64 characters.

An exception will be raised if a variable is referred to and has not been declared and initialized using the SET statement.

The WITH clause in GS SQL allows you to define one or more named subqueries, known as Common Table Expressions (CTEs), which can be used in the main query. This is particularly useful for simplifying complex queries by breaking them down into simpler parts, or for reusing the same subquery multiple times within a single SQL statement. SET @XXX = 4;
Select @XXX; --> 4

SET @A1 = 'meter1';
SELECT DISTINCT _component_name FROM system.stream WHERE _component_id = @A1; -->'Meter 1';

SET @VAR1 = NULL;
Select @VAR1; --> NULL

SET @VAR1 = CAST(4 AS DOUBLE);
SELECT @VAR1; --> 4 (it's a DOUBLE)

SET @VAR1 = CAST(4 AS STRING);
SELECT @VAR1; --> '4'

SET @VAR1 = (SELECT _last_value from system.stream WHERE _component_id='meter1' AND _id='kwh');
SELECT @VAR1; --> 3.43

SET @AA = 1;
SET @BB = MAX(@AA, 0);
SET @AA = 2;
SELECT @AA + @BB; --> 3

Temporal Parameters

Range Range specifies a time range for samples or indicates to return the last number of samples. SELECT _sample( range(sd=-1h, ed=streamEd)) FROM system.stream

TimeFilterId The TimeFilterId can be assigned the ID of a GroveStreams TimeFilter and will filter returned samples based on the time filter. SELECT _sample(timeFilterId='FallSeasonWeekends') FROM system.stream

CycleId
Stat The CycleId can be assigned the ID of a GroveStreams cycle and will aggregate samples into intervals that match the cycle size. Stat, optional, indicates the statistic to use for aggregation. SELECT _sample(cycleId='day', stat='max')) FROM system.stream

GapFill The GapFill parameter is used to fill gaps in interval streams. SELECT _sample(gapFill(head='nextVal', middle='avg', tail='previousVal')) FROM system.stream

Window Parameters
(lag, lead, slide, running) Window parameters perform positional and sliding-window operations on sample data. Use these instead of SQL WINDOW/OVER() syntax for time-series analytics. SELECT _sample(lag=1, range(last=100)) FROM system.stream
SELECT _sample(slide=3, slidestat='avg', range(sd=-1d)) FROM system.stream

Joins

INNER JOIN Selects records that have matching values in both tables. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

CROSS JOIN The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join. The ON clause with a CROSS JOIN is ignored. SELECT column_name(s) FROM table1 CROSS JOIN table2

LEFT OUTER JOIN The LEFT OUTER JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match. SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;

RIGHT OUTER JOIN The RIGHT OUTER JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match. SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;

FULL OUTER JOIN The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. Rows from both sides that have no match are included with NULL values for the missing side. SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Compound Operators

EXCEPT The EXCEPT operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

Just as with the UNION operator, the same rules apply when using the EXCEPT operator. SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2

INTERSECT The INTERSECT operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

Just as with the UNION operator, the same rules apply when using the INTERSECT operator. SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements. Only distinct rows are returned.

Every SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in every SELECT statement must also be in the same order

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

UNION ALL The UNION ALL operator is the same as UNION except duplicate rows are returned. SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

Operators

+, - (unary) Makes operand positive or negative. SELECT +3, -4 FROM table

/, *, %, +, - Divide, Multiply, Modulus, Plus, Subtract SELECT (3/4*2) + 1 - 5 FROM table

<, <=, >, >=,
=, ==,
!=, <> Less than: <
Less than or equal: <=
Greater than: >
Greater than or equal: >=
Equal to: =
Equal to: ==
Not equal: !=
Not equal: <> SELECT column FROM table WHERE x==10

&, |, <<, >>, ~ Bitwise:
&: If both bits are 1, it gives 1, else it gives 0
|: If either of the bits is 1, it gives 1, else it gives 0.
<<: Shifts the bits of the number to the right and fills 0 on voids left as a result.
>>: Shifts the bits of the number to the right and fills the voids left with the sign bit.
~ (prefix on an integer): bitwise NOT — flips every 0 to 1, and every 1 to 0.

Note: when ~ appears between two string-typed operands it is the POSIX regex match operator (see the row below), not bitwise NOT. The two uses do not overlap because bitwise NOT is a prefix unary on an integer while POSIX regex match is an infix between two strings. SELECT 5 < 7 FROM table
SELECT ~7 FROM table

~,
!~ string ~ pattern — PostgreSQL-style POSIX regular-expression match. Returns TRUE when the pattern matches anywhere in the string.
string !~ pattern — negated POSIX regex match.

Case sensitive (matches PostgreSQL semantics). For case-insensitive matching, normalize both sides with LOWER(...), or use the REGEXP_MATCH function with a case-insensitive pattern (e.g. (?i)...). PostgreSQL's ~* and !~* case-insensitive variants are reserved but not yet implemented.

The pattern may be a string literal or a column expression (unlike SIMILAR TO, which requires a literal). Pattern syntax is standard POSIX extended regex. SELECT _component_name FROM system.stream WHERE _component_name ~ '^Meter-[0-9]+$'
SELECT * FROM products WHERE sku !~ '[A-Z]{3}'
SELECT * FROM events WHERE message ~ pattern_column

String Operators The following operators work on Strings: <, <=, >, >=, =, ==, !=, <>, +, ||.
|| is the standard ANSI / PostgreSQL string-concatenation operator (May 2026 release). Equivalent to CONCAT(a, b). Pre-release, || meant logical OR — saved queries were migrated automatically. SELECT 'hello' + ' ' + 'there!' FROM table
SELECT 'hello' || ' ' || 'there!' FROM table
SELECT CONCAT('hello', ' ', 'there!') FROM table

Cast Operator :: PG-style postfix cast. Equivalent to CAST(value AS type). Added May 2026 release. SELECT '42'::LONG, price::DOUBLE FROM products

EXTRACT keyword form ANSI / PG keyword form of date-part extraction. Lowers to YEAR/MONTH/DAY/etc functions. Added May 2026 release.
Supported fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, EPOCH.
Note: EXTRACT(EPOCH FROM ...) returns milliseconds in GS (PostgreSQL returns seconds — divide by 1000 for PG-compatible behavior). SELECT EXTRACT(YEAR FROM order_date) FROM orders
SELECT EXTRACT(EPOCH FROM ts) FROM events

Schema prefix stripping The public. schema prefix on table references is silently stripped (May 2026 release). PG drivers prefix every table reference with public. by default; GS resolves them to the unqualified table name. SELECT * FROM public.customers
— resolves to: SELECT * FROM customers

Predicates - An expression that evaluates to TRUE, FALSE, or UNKNOWN.

NOT,
AND,
OR NOT: Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.
AND: Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.
OR: Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.

May 2026 release: && is no longer accepted as a logical operator — use the AND keyword. || is now the standard ANSI / PostgreSQL string-concatenation operator; use the OR keyword for logical OR. Saved queries that previously used && or || as logical operators were migrated automatically. SELECT column FROM table WHERE x=10 AND (y!=2 OR y!=5) AND NOT z!=8

BETWEEN,
NOT BETWEEN The BETWEEN operator selects values within a given range. The values can be numbers or text. The BETWEEN operator is inclusive: begin and end values are included. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT column_name(s) FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;

BETWEEN SYMMETRIC,
BETWEEN ASYMMETRIC,
NOT BETWEEN SYMMETRIC

PostgreSQL-flavored BETWEEN qualifiers.

SYMMETRIC: x BETWEEN SYMMETRIC b AND c is equivalent to (x BETWEEN b AND c) OR (x BETWEEN c AND b) — the bounds are tried in both orderings, so the predicate is order-independent.

ASYMMETRIC: the default behavior. BETWEEN ASYMMETRIC is identical to a bare BETWEEN; the keyword is accepted explicitly for PG-compat and produces the same plan.

NOT BETWEEN SYMMETRIC and NOT BETWEEN ASYMMETRIC are also accepted and negate the result.

Edge cases:

x BETWEEN ASYMMETRIC 10 AND 1 returns FALSE for every x (lower bound exceeds upper).
x BETWEEN SYMMETRIC 10 AND 1 still matches 1 <= x <= 10.

SELECT * FROM meter WHERE kwh BETWEEN SYMMETRIC 10 AND 1;
SELECT * FROM meter WHERE kwh BETWEEN ASYMMETRIC 1 AND 10;
SELECT * FROM meter WHERE kwh NOT BETWEEN SYMMETRIC b AND c;

EXISTS,
NOT EXISTS The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records. SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

IN,
NOT IN The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT column_name(s) FROM table_name WHERE column_name NOT IN (value1, value2, ...);

LIKE,
NOT LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. LIKE is case-sensitive (ANSI / PostgreSQL semantics). For case-insensitive matching, use ILIKE. There are four wildcards often used in conjunction with the LIKE operator:

The star sign (*) or the percentage sign (%) represent zero, one, or multiple characters
The question mark sign (?) or the underscore sign (_) represent one, single character

Note: Prior to the May 2026 release, LIKE was case-insensitive. Saved queries are auto-rewritten to ILIKE during the upgrade so existing results are preserved.

SELECT column_name(s) FROM table_name WHERE columnN LIKE pattern;
SELECT column_name(s) FROM table_name WHERE columnN NOT LIKE pattern;
SELECT fruit FROM table_name WHERE fruit LIKE '?pple';;
SELECT fruit FROM table_name WHERE fruit LIKE '_pple';;
SELECT fruit FROM table_name WHERE fruit LIKE 'A%le';;
SELECT fruit FROM table_name WHERE fruit LIKE 'A*le';

ILIKE,
NOT ILIKE Case-insensitive variant of LIKE (PostgreSQL semantics). Same wildcards as LIKE: % / * match zero or more characters, _ / ? match exactly one character. SELECT column_name(s) FROM table_name WHERE columnN ILIKE pattern;
SELECT column_name(s) FROM table_name WHERE columnN NOT ILIKE pattern;
SELECT fruit FROM table_name WHERE fruit ILIKE 'a%LE';

GLOB,
NOT GLOB Same as the LIKE predicate. SELECT column_name(s) FROM table_name WHERE column GLOB pattern;

REGEXP,
NOT REGEXP REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. If the pattern finds a match in the expression, the function returns TRUE, else it returns FALSE. Based off of Java java.util.regex.pattern. SELECT column_name(s) FROM table_name WHERE column REGEXP pattern;
SELECT column_name(s) FROM table_name WHERE column NOT REGEXP pattern;

IS NOT NULL,
IS NULL It is not possible to test for NULL values with comparison operators, such as =, ==, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead. SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_name(s) FROM table_name WHERE column IS NOT NULL;

IS TRUE,
IS FALSE,
IS UNKNOWN,
IS NOT TRUE,
IS NOT FALSE,
IS NOT UNKNOWN

Three-valued-logic predicates. Each returns a BOOLEAN (never NULL) classifying the truth state of the operand under SQL's TRUE / FALSE / UNKNOWN logic. UNKNOWN is the truth value of a NULL boolean.

Truth table:

x	x IS TRUE	x IS FALSE	x IS UNKNOWN	x IS NOT TRUE	x IS NOT FALSE	x IS NOT UNKNOWN
TRUE	T	F	F	F	T	T
FALSE	F	T	F	T	F	T
NULL	F	F	T	T	T	F

Edge cases:

NULL IS NOT TRUE returns TRUE (it is not TRUE — it is UNKNOWN).
NULL IS NOT UNKNOWN returns FALSE (NULL is UNKNOWN).
Operand must be boolean-typed; non-boolean operands raise an error.

SELECT * FROM device WHERE online IS NOT TRUE;
SELECT * FROM device WHERE online IS NOT FALSE;
SELECT * FROM device WHERE last_check IS NOT UNKNOWN;

Date Functions

FORMATDATE FORMATDATE(timeAsEpochMillis [,pattern] [,timeZone])

Formats an epoch millis datetime into a readable string. This method maps to Java’s DateFormatter.

timeAsEpochMillis: A Long datatype that represents a date in epoch milliseconds.
pattern: Optional. The format pattern. The default pattern is "yyyy-MM-dd'T'HH:mm:ss".
timeZone: Optional. The Time Zone is used for conversion and is optional. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

List of Patterns
List of Time Zones SELECT FormatDate(_time, pattern, timeZone) FROM table
SELECT FORMATDATE(now) → ‘2021-04-17T20:27:09’
SELECT FORMATDATE(_start_date, ‘dd/MM/yyyy, hh:mm:ss’) → ‘17/04/2021, 08:28:35’
SELECT FORMATDATE(_time_ed, ‘dd/MM/yyyy, hh:mm:ss’, ‘US/Central’) → ‘17/04/2021, 08:29:15’
SELECT FORMATDATE(now, 'MMM dd, yyyy (E), h:mm a', 'US/Central') → ‘Apr 17, 2021 (Sat), 8:41 PM’

TOEPOCHMILLIS TOEPOCHMILLIS(timeAsString[,pattern] [,timeZone])

Parses a String datetime into an epoch millisecond Long. This method maps to Java’s DateFormatter.

timeAsString: A String datatype that represents a date and time.
pattern: Optional. The format pattern used or parsing. The default pattern is "yyyy-MM-dd'T'HH:mm:ss".
timeZone: Optional. The Time Zone is used for conversion and is optional. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

List of Patterns
List of Time Zones SELECT TOEPOCHMILLIS('01/02/2021, 00:00:00', 'dd/MM/yyyy, HH:mm:ss', 'US/Central') → 1612159200000
SELECT TOEPOCHMILLIS('01/02/2021, 00:00:00', 'dd/MM/yyyy, HH:mm:ss') → 1612159200000
SELECT TOEPOCHMILLIS('2021-04-01T06:00:00.000', 'yyyy-MM-dd''T''HH:mm:ss.SSS') → 1617274800000; Escape T's ' with double apostrophes
SELECT TOEPOCHMILLIS('2021-02-01T00:00:00') → 1612159200000

DATEADD

DATEADD(epochMillis, spanType, spanAmount [,timeZone])

Adds time spans to an existing epoch millis date. Returns epoch millis date as Long data type.

epochMillis: Datetime to be added to. Epoch Milliseconds Long data type.
spanType: One of the following (case sensitive):

s: Second
m: Minute
h: Hour
d: Day
w: Week
M: Month
Y: Year

spanAmount: The number of spanTypes to add or subtract.
timeZone: Optional. The Time Zone is used for calculating the new datetime. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

SELECT DATEADD(epochMillis, 'd', 2 'US/Central') → Adds 2 days to the epochMillis
SELECT DATEADD(epochMillis, 'd', -2 'US/Central') → Subtracts 2 days to the epochMillis
SELECT DATEADD(epochMillis, 'y', 1 'US/Central') → Adds 1 year to the epochMillis

DATE_ADD DATE_ADD(epochMillis, INTERVAL amount unit)

Standard SQL syntax for adding time spans. Equivalent to DATEADD. Provided for compatibility with MySQL/PostgreSQL-style queries.

epochMillis: Datetime to be added to. Epoch Milliseconds Long data type.
amount: The number of units to add.
unit: One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR (case insensitive). SELECT DATE_ADD(epochMillis, INTERVAL 2 DAY) → Adds 2 days
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR) → 1 hour from now

DATE_SUB DATE_SUB(epochMillis, INTERVAL amount unit)

Standard SQL syntax for subtracting time spans. Provided for compatibility with MySQL/PostgreSQL-style queries.

epochMillis: Datetime to be subtracted from. Epoch Milliseconds Long data type.
amount: The number of units to subtract.
unit: One of: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR (case insensitive). SELECT DATE_SUB(NOW(), INTERVAL 30 DAY) → 30 days ago
SELECT DATE_SUB(epochMillis, INTERVAL 1 YEAR) → 1 year before epochMillis
SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR) → 2 hours ago

FROM_UNIXTIME / TO_TIMESTAMP FROM_UNIXTIME(epochMillis)
TO_TIMESTAMP(epochMillis)

No-op passthrough for standard SQL compatibility. Since GroveStreams already stores all datetimes as epoch milliseconds, this function simply returns its argument unchanged. Provided so that LLM-generated, MySQL-style, or PostgreSQL-style queries using FROM_UNIXTIME() / TO_TIMESTAMP() work without modification.

epochMillis: An epoch millisecond Long value (returned as-is). SELECT FROM_UNIXTIME(1618885675191) → 1618885675191
SELECT TO_TIMESTAMP(1618885675191) → 1618885675191
SELECT FROM_UNIXTIME(NOW) → same as NOW

TO_DATE TO_DATE(dateString, format [, timeZone])

Parses a date/time string into an epoch millisecond Long using the supplied format pattern. The PostgreSQL-flavored inverse of FORMATDATE.

dateString: The date/time text to parse.
format: A Java SimpleDateFormat pattern (e.g. 'yyyy-MM-dd', 'MM/dd/yyyy HH:mm:ss').
timeZone: Optional. Timezone used to interpret the parsed value (e.g. 'US/Eastern', 'UTC'). Defaults to the organization timezone.

Returns NULL if either argument is NULL or the string fails to parse. SELECT TO_DATE('2026-05-15', 'yyyy-MM-dd') → epoch for 2026-05-15T00:00:00
SELECT TO_DATE('05/15/2026 14:30', 'MM/dd/yyyy HH:mm', 'US/Eastern') → epoch millis
SELECT TO_DATE(NULL, 'yyyy-MM-dd') → NULL

UNIX_TIMESTAMP UNIX_TIMESTAMP()

Returns the current time as epoch milliseconds. Equivalent to NOW. Provided for standard SQL compatibility so that MySQL-style queries work without modification. SELECT UNIX_TIMESTAMP() → 1618885675191

DAYOFWEEK DAYOFWEEK(epochMillis [,timeZone])

The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7).
Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

epochMillis: Datetime to find the Day of Week for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT DAYOFWEEK(now, 'US/Central') → 7
SELECT DAYOFWEEK(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT DAYOFWEEK(TOEPOCHMILLIS('2021-02-06T00:00:00')) → 7

DAY DAY(epochMillis [,timeZone])

The DAY() function returns the day number of the month. 1 is returned for the 1st day. Return data type is Long.

epochMillis: Datetime to find the day for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT DAY(now, 'US/Central') → 25
SELECT DAY(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 1
SELECT DAY(TOEPOCHMILLIS('2021-02-06T00:00:00')) → 6

MONTH MONTH(epochMillis [,timeZone])

The MONTH() function returns the month number of the datetime. 1 is returned for the 1st month of the year. Return data type is Long.

epochMillis: Datetime to find the month for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT MONTH(now, 'US/Central') → 4
SELECT MONTH(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT MONTH(TOEPOCHMILLIS('2021-06-06T00:00:00')) → 6

YEAR YEAR(epochMillis [,timeZone])

The YEAR() function returns the year number of the datetime. Return data type is Long.

epochMillis: Datetime to find the Day for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone. SELECT YEAR(now, 'US/Central') → 4
SELECT YEAR(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT YEAR(TOEPOCHMILLIS('2021-06-06T00:00:00')) → 6

DATEDIFF

DATEDIFF(datePart, startDate, endDate)

Returns, as a Long data type, the requested elapsed time amount. This calculation is based on milliseconds ellapsed and does not take into consideration items like daylight savings or leap years. The result will be the floor for fractional results.

Millisecond difference isn't supported below, but can be calculated by subtracting the two datetimes: endDate-startDate

datePart: The units in which DATEDIFF reports the difference between the startdate and enddate. One of the following (case sensitive):

s: Second
m: Minute
h: Hour
d: Day

startDate: Epoch milliseconds Long data type.
endDate: Epoch milliseconds Long data type.

SELECT DATEDIFF('s', now, now + 1000) → 1
SELECT DATEDIFF('h', TOEPOCHMILLIS('2021-02-01T00:00:00'), TOEPOCHMILLIS('2021-02-01T05:30:00')) → 5
SELECT DATEDIFF('d', TOEPOCHMILLIS('2021-02-01T00:00:00'), TOEPOCHMILLIS('2021-02-02T05:00:00')) → 1

DATEPART / DATE_PART

DATEPART(part, date [, timeZone])
DATE_PART(part, date [, timeZone])

Returns a specified part of a date as a Long value. DATE_PART is the PostgreSQL-flavored alias.

part: A string specifying the date part to extract. Supported values (case insensitive):

year: Year
quarter: Quarter (1-4)
month: Month (1-12)
day: Day of month (1-31)
dayofyear: Day of year (1-366)
week: ISO week of year
weekday: Day of week (Sunday=0, Monday=1, ..., Saturday=6)
hour: Hour (0-23)
minute: Minute (0-59)
second: Second (0-59)

date: Epoch milliseconds Long data type.
timeZone: Optional. The timezone to use for the calculation (e.g., 'US/Eastern', 'UTC'). Defaults to the organization timezone.

SELECT DATEPART('hour', TOEPOCHMILLIS('2021-06-15T10:30:45')) → 10
SELECT DATEPART('quarter', TOEPOCHMILLIS('2021-06-15T00:00:00')) → 2
SELECT DATEPART('day', TOEPOCHMILLIS('2021-06-15T23:30:00'), 'Asia/Tokyo') → 16

DATETRUNC / DATE_TRUNC

DATETRUNC(part, date [, timeZone])
DATE_TRUNC(part, date [, timeZone])

Truncates a date to the specified precision, returning epoch milliseconds. DATE_TRUNC is the PostgreSQL-flavored alias.

part: A string specifying the truncation precision. Supported values (case insensitive):

year: Truncate to January 1st
quarter: Truncate to 1st day of the quarter
month: Truncate to 1st day of the month
week: Truncate to Monday (ISO week start)
day: Truncate to start of day
hour: Truncate to start of hour
minute: Truncate to start of minute
second: Truncate to start of second

date: Epoch milliseconds Long data type.
timeZone: Optional. The timezone to use for the truncation (e.g., 'US/Eastern', 'UTC'). Defaults to the organization timezone. Important for day/month/year truncation in non-UTC timezones.

SELECT DATETRUNC('day', TOEPOCHMILLIS('2021-06-15T10:30:45')) → epoch for 2021-06-15T00:00:00
SELECT DATETRUNC('month', TOEPOCHMILLIS('2021-06-15T10:30:45')) → epoch for 2021-06-01T00:00:00
SELECT DATETRUNC('hour', TOEPOCHMILLIS('2021-06-15T10:30:45')) → epoch for 2021-06-15T10:00:00

MAKE_DATE / MAKE_TIME / MAKE_TIMESTAMP MAKE_DATE(year, month, day)
MAKE_TIME(hour, minute, second)
MAKE_TIMESTAMP(year, month, day, hour, minute, second)

PostgreSQL date/time constructors. Build a temporal value from integer parts. MAKE_TIMESTAMP interprets the wall-clock time in the session timezone. Returns epoch milliseconds (LONG). Out-of-range values raise an error; NULL in any argument propagates as NULL. SELECT MAKE_DATE(2024, 1, 15) → epoch for 2024-01-15T00:00:00 UTC
SELECT MAKE_TIME(12, 34, 56) → 45296000 (ms since midnight)
SELECT MAKE_TIMESTAMP(2024, 1, 15, 12, 34, 56) → epoch ms in session TZ

DATE_BIN DATE_BIN(stride, source, origin)

PostgreSQL 14+ time bucketing. Floors source down to the nearest multiple of stride from origin. All inputs are LONG (epoch ms for timestamps, ms-duration for stride). Output is epoch ms of the bucket start.

stride: bucket size in ms, or an INTERVAL literal which CASTs to ms.
source: the timestamp to bucket.
origin: bucket alignment anchor. SELECT DATE_BIN(INTERVAL '15 minutes', NOW, TIMESTAMP '2024-01-01') → bucket start
SELECT DATE_BIN(INTERVAL '1 day', t, TIMESTAMP '2024-01-01') AS day FROM t

AGE AGE(timestamp)
AGE(end, start)

Returns the duration between two timestamps as a LONG count of milliseconds. 1-arg form is anchored to NOW (returns NOW − timestamp); 2-arg form returns end − start.

Note: PostgreSQL's AGE returns an INTERVAL with calendar-aware months/days/seconds breakdown; GS returns the absolute millisecond duration. Wrap in ::INTERVAL for PG-formatted text output. SELECT AGE(TIMESTAMP '2024-01-01') → ms since 2024-01-01
SELECT AGE(end_date, start_date) → ms duration

CLOCK_TIMESTAMP CLOCK_TIMESTAMP()

Returns the true wall-clock time as a TIMESTAMP (epoch ms LONG, with TIMESTAMP semantics). Unlike NOW / TRANSACTION_TIMESTAMP / STATEMENT_TIMESTAMP — which are constant for the duration of the statement — CLOCK_TIMESTAMP returns a fresh value on each call, even within the same statement. Use it for measuring elapsed time between expression evaluations. SELECT CLOCK_TIMESTAMP() → 1735922429463
SELECT CLOCK_TIMESTAMP() - CLOCK_TIMESTAMP() → small positive ms delta

ISFINITE ISFINITE(date | timestamp | interval)

Returns TRUE for any non-NULL temporal input. GS does not represent the PG infinity sentinels ('infinity' / '-infinity') — all stored temporal values are finite — so the only way to get FALSE-equivalent here is a NULL argument, which propagates as NULL. Provided for PG/BI-tool compatibility. SELECT ISFINITE(NOW) → true
SELECT ISFINITE(TIMESTAMP '2024-01-15') → true
SELECT ISFINITE(NULL) → NULL

JUSTIFY_DAYS / JUSTIFY_HOURS / JUSTIFY_INTERVAL JUSTIFY_DAYS(interval)
JUSTIFY_HOURS(interval)
JUSTIFY_INTERVAL(interval)

PostgreSQL interval canonicalization helpers. Return INTERVAL.

JUSTIFY_DAYS: rolls every 30 days of the interval into 1 month.
JUSTIFY_HOURS: rolls every 24 hours of the interval into 1 day.
JUSTIFY_INTERVAL: applies both JUSTIFY_DAYS and JUSTIFY_HOURS, then re-runs JUSTIFY_DAYS in case JUSTIFY_HOURS overflowed into a new month. SELECT JUSTIFY_DAYS(INTERVAL '35 days') → INTERVAL '1 mon 5 days'
SELECT JUSTIFY_HOURS(INTERVAL '27 hours') → INTERVAL '1 day 3:00:00'
SELECT JUSTIFY_INTERVAL(INTERVAL '1 mon -1 hour') → INTERVAL '29 days 23:00:00'

STATEMENT_TIMESTAMP STATEMENT_TIMESTAMP()

Returns the wall-clock time at which the current statement started, as a TIMESTAMP. Constant for the duration of a single statement (every call within the statement returns the same value). Distinct from CLOCK_TIMESTAMP, which advances on every call. SELECT STATEMENT_TIMESTAMP() → fixed value within the statement

TRANSACTION_TIMESTAMP TRANSACTION_TIMESTAMP()

PG-compat alias of NOW(). Returns the start-of-statement timestamp. Constant for the duration of the statement. Provided so PG-flavored queries that call transaction_timestamp() run unchanged. SELECT TRANSACTION_TIMESTAMP() → same value as NOW()

TO_CHAR (date form) TO_CHAR(temporal, format)

Formats a temporal value as a string using PostgreSQL date/time tokens. Subset coverage focused on Power BI / Tableau export formats.

Supported tokens: YYYY, YY, MM, Mon, Month, DD, DDD, Day, Dy, HH24, HH12, HH, MI, SS, MS, US, AM, PM, TZ. Unrecognized tokens pass through verbatim. See also the numeric form in the Numeric Functions section. SELECT TO_CHAR(NOW, 'YYYY-MM-DD') → '2024-06-15'
SELECT TO_CHAR(NOW, 'Mon DD, YYYY HH24:MI:SS') → 'Jun 15, 2024 10:30:00'

String Functions

INSTR / STRPOS / POSITION INSTR(string1, string2)
STRPOS(string1, string2)
POSITION(string1, string2)

Returns the position of the first occurrence of a string in another string. 1 being the 1st position. This function performs a case-insensitive search. STRPOS and POSITION are PostgreSQL-flavored aliases — argument order matches INSTR (haystack, needle). The PG keyword form POSITION(needle IN haystack) is not supported; use the function-call form.

string1: The string to be searched.
string2: The string to search for in string1. If string2 is not found, this function returns 0 SELECT INSTR('Apple Tree', 'Tree') → 7
SELECT STRPOS('Apple Tree', 'Tree') → 7
SELECT INSTR('Apple Tree', 'apple') → 1
SELECT INSTR('Peach Tree', 'Apple')→ 0

LEFT LEFT(string1, count)

Returns the left part of a character string with the specified number of characters.

string1: The string to be trimmed.
count: The number of characters to extract. If the number exceeds the number of characters in string, it returns string SELECT LEFT('Grizzly bear', 7) → ‘Grizzly
SELECT LEFT('Grizzly bear', 100) → ‘Grizzly bear’

RIGHT RIGHT(string1, count)

Returns the right part of a character string with the specified number of characters.

string1: The string to be trimmed.
count: The number of characters to extract. If the number exceeds the number of characters in string, it returns string SELECT RIGHT('Grizzly bear', 7) → ‘Grizzly'
SELECT RIGHT('Grizzly bear', 100) → ‘Grizzly bear’

SUBSTR / SUBSTRING

SUBSTR(string1, start [,length])
SUBSTRING(string1, start [,length])

Returns a substring from a string starting at a specified position with a predefined length.

string1: The string to be used to extract the substring.
start: The start argument is a long that specifies the starting position of the returned substring. The start argument can be a positive or negative integer:

If start is a positive integer, the substr() function returns a substring starting from the beginning of the string. The first character has an index of 1.
If the start is a negative integer, the returned substring consists of the length number of characters starting from the end of the string. The last character has an index of -1.

length: Optional. The length argument determines the length of the substring. The length argument is optional. If it is omitted, it is assumed to be the maximum positive integer.

If any argument is NULL, the substr() function will return NULL.

SELECT SUBSTR('GS SQL substr', 1, 6) → ‘GS SQL'
SELECT SUBSTR('GS SQL substr', -6, 6) → ‘substr'
SELECT SUBSTR('GS SQL substr', 1, 6) → ‘GS SQL'
SELECT SUBSTR('GS SQL substr', 1, 6) → ‘substr'

LENGTH / LEN / CHAR_LENGTH / CHARACTER_LENGTH LENGTH(string1)
LEN(string1)
CHAR_LENGTH(string1)
CHARACTER_LENGTH(string1)

Return the number of characters (or length) of a specified string. CHARACTER_LENGTH is the PostgreSQL / ANSI form.

string1: The string to find the length of. SELECT LENGTH(‘Rocket’) → 6

LOWER / LCASE LOWER(string1)
LCASE(string1)

Return a string with all characters converted to lowercase.

string1: The string to lower case. SELECT LOWER(‘Rocket’) → ‘rocket’

UPPER / UCASE UPPER(string1)
UCASE(string1)

Return a string with all characters converted to uppercase.

string1: The string to upper case. SELECT UPPER(‘Rocket’) → ‘ROCKET’

TRIM TRIM(string1 [,chars])

Removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove. SELECT TRIM(‘ CAT ‘) → ‘ CAT’
SELECT TRIM(‘??CAT?’, ‘?‘) → ‘CAT’

LTRIM LTRIM(string1 [,chars])

Remove spaces or other specified characters in a set from the left end of a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove. SELECT LTRIM(‘ CAT ‘) → ‘CAT ‘
SELECT LTRIM(‘??CAT?’, ‘?‘) → ‘CAT?’

RTRIM RTRIM(string1 [,chars])

Remove all spaces or specified characters in a set from the right end of a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove. SELECT RTRIM(‘ CAT ‘) → ‘ CAT’
SELECT RTRIM(‘??CAT?’, ‘?‘) → ‘??CAT’

BTRIM BTRIM(string1 [, chars])

PostgreSQL alias of TRIM — removes leading and trailing characters from both ends of the string. If chars is omitted, ASCII spaces are stripped. The two forms BTRIM(str) and TRIM(str) are interchangeable.

string1: The string to trim.
chars: Optional. Defaults to space. Each character in the set is treated as a standalone character to strip (not a substring). SELECT BTRIM(' hello ') → 'hello'
SELECT BTRIM('xxhelloxx', 'x') → 'hello'
SELECT BTRIM('--abc--', '-') → 'abc'

STARTSWITH STARTSWITH(string1, chars)

Used for checking prefix of a String. It returns a boolean value true or false based on whether the given string begins with the specified letter or word. Case insensitive.

string1: The string to test.
chars: The characters to test for at the start of the string. SELECT STARTSWITH(‘blue sky‘, ‘blue’) → true
SELECT STARTSWITH(‘blue sky‘, ‘red’) → false

REPLACE REPLACE(string1, substring, replacement)

Replace all occurrences of a substring by another substring in a string.

string1: The string to test.
substring: The characters to be replaced.
replacement: The characters to be inserted. SELECT REPLACE(‘Up the Hill’, ‘the’, ‘a’) → ‘Up a Hill’’

REVERSE REVERSE(string1)

Returns the reverse of a string value.

string1: The string to reverse. Must be a string data type. SELECT REVERSE(‘hello’) → ‘olleh’
SELECT REVERSE(‘abc’) → ‘cba’
SELECT REVERSE(‘’) → ‘’

PARSECSV PARSECSV(string1, index [, separator [, stringDelimiter]])

Parses a delimited string as if it were a line in a CSV import file and returns the segment at the passed in index. It does not not have to end with a \n character.

string1: The string to split into substrings.
index: The index of the parsed substring to return. 1 being the 1st position.
separator: Optional. Defaults to comma. It is the character that separates string segments
stringDelimiter: Optional. Defaults to double quote. It is the character that delimits text segments. SELECT PARSECSV(‘a,b,c‘, 1) → ‘a’
SELECT PARSECSV(‘a,b,c‘, 2) → ‘b’
SELECT PARSECSV(‘1,2,3‘, 3) → ‘3’
SELECT PARSECSV(‘a,b,c‘, 4) → throws index out of bounds exception
SELECT PARSECSV(‘"Hello world",1‘, 1) → 'Hello world'
SELECT PARSECSV(‘a|b|c‘, 1, '|') → 'a'
SELECT PARSECSV(‘a|b|"Hello world"‘, 3, '|', '"') → 'Hello world'

CONCAT CONCAT(arg1, arg2, ...)

Concatenates two or more arguments into a single string. Non-string arguments are coerced to strings. NULL arguments are treated as empty strings (consistent with PostgreSQL semantics, NOT the older Oracle behavior).

argN: Any expression. At least one argument is required.

Equivalent to the + and || string-concatenation operators when their operands are strings. SELECT CONCAT('hello', ' ', 'world') → 'hello world'
SELECT CONCAT('order-', 42) → 'order-42'
SELECT CONCAT('a', NULL, 'b') → 'ab'

CONCAT_WS CONCAT_WS(separator, arg1, arg2, ...)

Concatenates the arguments with the given separator placed between each non-NULL argument. NULL arguments are skipped (no separator is emitted for them). If the separator is NULL the result is NULL.

separator: String inserted between arguments. NULL → result is NULL.
argN: Values to join. At least one is required. NULLs are skipped. SELECT CONCAT_WS(', ', 'Smith', 'John', 'PhD') → 'Smith, John, PhD'
SELECT CONCAT_WS('-', 2026, 5, 15) → '2026-5-15'
SELECT CONCAT_WS(',', 'a', NULL, 'b') → 'a,b'
SELECT CONCAT_WS(NULL, 'a', 'b') → NULL

LPAD LPAD(string1, length [, fill])

Pads string1 on the left with fill characters until it reaches length. If string1 is already longer than length, it is truncated on the right to length.

string1: The string to pad or truncate.
length: Target length (Long).
fill: Optional. Defaults to a single space. Multi-character fills cycle. SELECT LPAD('42', 5, '0') → '00042'
SELECT LPAD('hi', 6) → ' hi'
SELECT LPAD('grovestreams', 5) → 'grove'
SELECT LPAD('x', 7, 'ab') → 'ababab x' (cycles: 'ababab' + 'x' → 'abababx')

RPAD RPAD(string1, length [, fill])

Pads string1 on the right with fill characters until it reaches length. If string1 is already longer than length, it is truncated on the right to length.

string1: The string to pad or truncate.
length: Target length (Long).
fill: Optional. Defaults to a single space. Multi-character fills cycle. SELECT RPAD('42', 5, '0') → '42000'
SELECT RPAD('hi', 6) → 'hi '
SELECT RPAD('grovestreams', 5) → 'grove'

INITCAP INITCAP(string1)

Returns the string with the first letter of each word capitalized and the remaining letters lowercased. A "word" is any run of alphanumeric characters; any non-alphanumeric character (space, punctuation, etc.) is a word boundary.

string1: The string to title-case. SELECT INITCAP('hello world') → 'Hello World'
SELECT INITCAP('JANE-DOE') → 'Jane-Doe'
SELECT INITCAP('o''brien') → 'O''Brien'

SPLIT_PART SPLIT_PART(string1, delimiter, n)

Splits string1 on delimiter and returns the nth part. 1-indexed. A negative n counts from the end (-1 is the last part). Out-of-range indexes return an empty string.

string1: The string to split.
delimiter: The delimiter string (not a regex; treated literally).
n: 1-based part index. Negative values count from the end. SELECT SPLIT_PART('one,two,three', ',', 1) → 'one'
SELECT SPLIT_PART('one,two,three', ',', 3) → 'three'
SELECT SPLIT_PART('one,two,three', ',', -1) → 'three'
SELECT SPLIT_PART('one,two,three', ',', 9) → ''
SELECT SPLIT_PART('a::b::c', '::', 2) → 'b'

REPEAT REPEAT(string1, n)

Returns string1 concatenated with itself n times. A non-positive n returns an empty string. The output is capped at 1,000,000 characters to protect query memory.

string1: The string to repeat.
n: Number of repetitions (Long). SELECT REPEAT('ab', 3) → 'ababab'
SELECT REPEAT('-', 5) → '-----'
SELECT REPEAT('x', 0) → ''

MD5 MD5(string1)

Returns the MD5 hash of the input string as a 32-character lowercase hexadecimal digest. NULL input returns NULL.

string1: The string to hash.

Note: MD5 is suitable for content fingerprinting / cache keys; it is not collision-resistant and should not be used for password hashing or authentication. SELECT MD5('hello') → '5d41402abc4b2a76b9719d911017c592'
SELECT MD5('') → 'd41d8cd98f00b204e9800998ecf8427e'
SELECT MD5(NULL) → NULL

SHA256 SHA256(string1)

Returns the SHA-256 hash of the input string as a 64-character lowercase hexadecimal digest. NULL input returns NULL.

string1: The string to hash. SELECT SHA256('hello') → '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824'
SELECT SHA256(NULL) → NULL

GEN_RANDOM_UUID / UUID_GENERATE_V4 GEN_RANDOM_UUID()
UUID_GENERATE_V4()

Generates a fresh random (version 4) UUID as a 36-character lowercase string with standard 8-4-4-4-12 hyphenation. Each call produces a new value.

No arguments. SELECT GEN_RANDOM_UUID() → 'a1dbf630-72bc-4b52-b0b5-49f33c2f1d81'
SELECT UUID_GENERATE_V4() → '7c1f2b4e-8a3d-4f12-9c5e-2b1d8e3f4a52'

REGEXP_MATCH REGEXP_MATCH(string1, pattern)

Returns the first substring matching the given regular expression, or NULL if no match is found.

string1: The string to search.
pattern: A Java regular expression.

Note: GS SQL string literals process backslash escapes — double the backslash for regex shorthand classes (e.g. '\\\\d+' for \d+), or use bracket character classes (e.g. '[0-9]+') to avoid the escape. SELECT REGEXP_MATCH('order-1234-end', '[0-9]+') → '1234'
SELECT REGEXP_MATCH('no digits here', '[0-9]+') → NULL
SELECT REGEXP_MATCH('a-b-c', '[a-z]') → 'a'

REGEXP_REPLACE REGEXP_REPLACE(string1, pattern, replacement)

Returns string1 with all (non-overlapping) matches of pattern replaced by replacement. The replacement may use Java back-reference syntax ($1, $2, etc.).

string1: The string to search.
pattern: A Java regular expression.
replacement: Replacement string. Use $N to reference capture groups.

Note: see REGEXP_MATCH about backslash escape doubling in GS SQL string literals. SELECT REGEXP_REPLACE('order-1234-end', '[0-9]+', '#') → 'order-#-end'
SELECT REGEXP_REPLACE('John Smith', '(\w+) (\w+)', '$2, $1') → 'Smith, John'
SELECT REGEXP_REPLACE('a b c', '[0-9]+', '-') → 'a b c'

REGEXP_SUBSTR REGEXP_SUBSTR(str, pattern [, start [, n [, flags]]])

Returns the n-th regex match in str, scanning from 1-based character position start. Returns NULL when there is no match. flags is a PG/Oracle flags string (e.g. 'i' for case-insensitive, 'm' for multiline). Defaults: start = 1, n = 1, no flags.

str: The string to search.
pattern: A Java regular expression.
start: Optional. 1-based scan start.
n: Optional. Which match to return (1 = first).
flags: Optional. Match flags.

Note: see REGEXP_MATCH about backslash escape doubling in GS SQL string literals. SELECT REGEXP_SUBSTR('order-1234-end-9', '[0-9]+') → '1234'
SELECT REGEXP_SUBSTR('order-1234-end-9', '[0-9]+', 1, 2) → '9'
SELECT REGEXP_SUBSTR('foo bar', 'BAR', 1, 1, 'i') → 'bar'
SELECT REGEXP_SUBSTR('abc', '[0-9]+') → NULL

REGEXP_SPLIT_TO_ARRAY REGEXP_SPLIT_TO_ARRAY(str, pattern [, flags])

Splits str on every match of pattern and returns the resulting parts as a JSON-array text (e.g. '["a","b","c"]'). flags matches the PG flags syntax.

GS-specific deviation: PostgreSQL returns text[]. GS SQL does not have a first-class array type, so the JSON-array text representation is the closest fluency — downstream code can pass the result straight into JSON_QUERY, JSONB_ARRAY_LENGTH, or JSONB_EXTRACT_PATH without an extra cast.

str: The string to split.
pattern: A Java regular expression. Empty matches advance one character to avoid infinite loops.
flags: Optional. PG-style match flags. SELECT REGEXP_SPLIT_TO_ARRAY('a,b,c', ',') → '["a","b","c"]'
SELECT REGEXP_SPLIT_TO_ARRAY('a1b2c3', '[0-9]') → '["a","b","c",""]'
SELECT REGEXP_SPLIT_TO_ARRAY('foo bar baz', '\s+') → '["foo","bar","baz"]'
SELECT JSONB_ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY('a,b,c', ',')) → 3

SIMILAR TO expr SIMILAR TO 'pattern'
expr NOT SIMILAR TO 'pattern'

SQL-standard pattern match. Combines LIKE wildcards (% any chars, _ single char) with regex extras (| alternation, * / + / ? quantifiers, [...] character classes, (...) grouping). The pattern must be a string literal. For column-valued patterns use ~ (POSIX regex) directly. WHERE name SIMILAR TO 'a%(b|c)d_'
WHERE code SIMILAR TO '[A-Z]{3}-[0-9]+'

CHR / ASCII CHR(int)
ASCII(text)

CHR returns the character with the given Unicode code point (handles supplementary code points up to U+10FFFF). ASCII returns the Unicode code point of the first character. Inverses. SELECT CHR(65) → 'A'
SELECT ASCII('A') → 65
SELECT ASCII(CHR(8364)) → 8364 (€)

OCTET_LENGTH OCTET_LENGTH(text)

UTF-8 byte length of the input. Distinct from LENGTH which returns the character count. SELECT OCTET_LENGTH('hi') → 2
SELECT OCTET_LENGTH('café') → 5 (é is 2 UTF-8 bytes)

OVERLAY OVERLAY(src, substr, start [, length])

Scalar (function-call) form of the standard-SQL OVERLAY(src PLACING substr FROM start [FOR length]) syntax. Replaces length characters of src starting at 1-based position start with substr. If length is omitted, it defaults to the character length of substr (so substr exactly overwrites that many characters). Returns NULL if any argument is NULL.

src: The string being modified.
substr: The replacement string spliced in.
start: 1-based character position where the splice begins.
length: Optional. Number of characters of src to replace. SELECT OVERLAY('Txxxxas', 'hom', 2, 4) → 'Thomas'
SELECT OVERLAY('abcdef', 'XY', 3, 2) → 'abXYef'
SELECT OVERLAY('abcdef', 'XY', 3) → 'abXYef' -- length defaults to len(substr)
SELECT OVERLAY('abcdef', 'XYZ', 3, 0) → 'abXYZcdef' -- pure insertion

FORMAT FORMAT(fmtstr, args…)

PostgreSQL printf-style string formatter. Supported specifiers:
%s — string (any value rendered as text)
%I — identifier (wraps in "..." with internal " doubled)
%L — literal (wraps in '...' with internal ' doubled)
%% — literal percent.

Positional / width / precision specifiers (%1$s, %10s, etc.) deferred. SELECT FORMAT('Hello %s, age %s', 'Alice', 30) → 'Hello Alice, age 30'
SELECT FORMAT('INSERT INTO %I (col) VALUES (%L)', 't', 'O''Brien')

QUOTE_IDENT / QUOTE_LITERAL QUOTE_IDENT(text)
QUOTE_LITERAL(text)

SQL-safe escaping helpers. QUOTE_IDENT wraps the input in double quotes (with internal " doubled) so it's safe as an identifier; QUOTE_LITERAL wraps in single quotes (with internal ' doubled) so it's safe as a string literal. SELECT QUOTE_IDENT('my col') → '"my col"'
SELECT QUOTE_LITERAL('O''Brien') → '''O''''Brien'''

TRANSLATE TRANSLATE(string, from, to)

Per-character substitution. For each index i in from, replace matching chars in string with to.charAt(i). If to is shorter than the matched position, the character is removed. SELECT TRANSLATE('abcde', 'ace', 'XY') → 'XbYd'
SELECT TRANSLATE('123.456', '.', ',') → '123,456'

PG_SIZE_PRETTY / PG_SIZE_BYTES PG_SIZE_PRETTY(bytes)
PG_SIZE_BYTES(text)

Round-trip between a raw byte count and a human-readable PostgreSQL size string. Units: bytes / B / kB / MB / GB / TB / PB (case-insensitive on parse). SELECT PG_SIZE_PRETTY(1572864) → '1536 kB'
SELECT PG_SIZE_BYTES('1.5 MB') → 1572864

Numeric Functions

ROUND ROUND(number [,amount])

Returns a numeric value, rounded to the specified length or precision. Rounds half up. The function returns the same data type as the numeric data type of the number passed in.

number: The number to be rounded.
amount: Optional. The precision to which the number is to be rounded SELECT ROUND(5.1) → 5
SELECT ROUND(5.9) → 6
SELECT ROUND(5.5) → 6
SELECT ROUND(5.123456, 3) → 5.123
SELECT ROUND(5.123456, 10) → 5.123456

MAX MAX(number [,number2] [...numberN])

The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL.

number: The number to be rounded.
amount: Optional. The precision to which the number is to be rounded SELECT MAX(5.1, 5.2) → 5.2
SELECT MAX(1, 3, 2) → 3
SELECT MAX(1, NULL, 2) → NULL

MIN MIN(number [,number2] [...numberN])

The multi-argument max() function returns the argument with the minimum value, or return NULL if any argument is NULL.

number: The number to be rounded.
amount: Optional. The precision to which the number is to be rounded SELECT MIN(5.1, 5.2) → 5.1
SELECT MIN(1, 3, 2) → 1
SELECT MIN(1, NULL, 2) → NULL

FLOOR FLOOR(number)

Returns the largest integer less than or equal to the specified numeric expression. The function returns the same data type as the numeric data type of the argument.

number: The number to calculate the floor for. SELECT FLOOR(5.7) → 5

CEIL / CEILING CEIL(number)
CEILING(number)

Return the smallest integer value that is greater than or equal to a number. The function returns the same data type as the numeric data type of the argument.

number: The number to calculate the ceiling for. SELECT CEIL(5.1) → 6

TRUNC / TRUNCATE TRUNC(number [,amount])
TRUNCATE(number [,amount])

The TRUNC(n1,n2) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. The function returns the same data type as the numeric data type of the argument.

number: The number to truncate.
amount: Optional. The number of decimal places to truncate. SELECT TRUNC(5.1) → 5
SELECT TRUNC(5.7) → 5
SELECT TRUNC(5.68,1) → 5.6
SELECT TRUNC(55.68,-1) → 50

PI PI()

The double value that is closer than any other to pi, the ratio of the circumference of a circle to its diameter. Utilizes the Java Math.PI constant. SELECT PI() → 3.141592653589793

RADIANS RADIANS()

Converts a number from degrees into radians. SELECT RADIANS(45) → 0.7853981633974483

RANDOM / RAND RANDOM()
RAND()

Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0. Returned values are chosen pseudorandomly with (approximately) uniform distribution from that range. Utilizes Java Random() function. SELECT RANDOM() → 0.48882270224969493

SIGN SIGN(number)

Returns the signum function of the given numeric expression as a Long. The return value is -1 if the given numeric expression is negative; 0 if the given numeric expression is zero; and 1 if the given numeric expression is positive.

number: The number to signum. SELECT SIGN(-10) → -1
SELECT SIGN(5) → 1
SELECT SIGN(0) → 0

ABS ABS(number)

Return the absolute value of a number. The return type will be the type of the argument.

number: The number to process. SELECT ABS(-10) → 10
SELECT ABS(0) → 0
SELECT ABS(99) → 99

SQRT SQRT(number)

Returns the square root of the specified value. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT SQRT(2) → 1.4142135623730951

EXP EXP(number)

Returns the exponential value of the specified numeric expression.

The constant e (2.718281...), is the base of natural logarithms.

The exponent of a number is the constant e raised to the power of the number. For example EXP(1.0) = e^1.0 = 2.71828182845905 and EXP(10) = e^10 = 22026.4657948067.

The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT EXP(2) → 7.38905609893065

POWER / POW POWER(number, power)
POW(number, power)

Returns the value of the specified expression to the specified power. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. power: The power to which to raise the number. SELECT POWER(2.5, 4) → 39.0625

LN(number)

Returns the natural logarithm (base e) of a double, long, or big decimal value. Special cases:

If the argument is NaN or less than zero, then the result is NaN.
If the argument is positive infinity, then the result is positive infinity.
If the argument is positive zero or negative zero, then the result is negative infinity.

Returns a Double type.

number: The number to process.

SELECT LN(10) → 2.302585092994046

LOG LOG(number [,base])

Returns the logarithm of the first argument computed at the base of the second argument. Returns a Double type.

number: The number to process. base: Optional. Defaults to base 10. SELECT LOG(3, 2) → 1.5849625007211563
SELECT LOG(3) → 0.47712125471966244

DEGREES DEGREES(number)

Converts a number from radians into degrees. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT DEGREES(.33) → 18.90760723931717

EARTH_DISTANCE EARTH_DISTANCE(lat1, lon1, lat2, lon2)

Returns the great-circle distance in statute miles between two points expressed as (latitude, longitude) decimal degrees. Uses the WGS84 equatorial radius (3963.1908 miles) for spherical-earth haversine math, matching the semantics of PostgreSQL's earthdistance extension.

lat1, lon1: Starting point latitude and longitude.
lat2, lon2: End point latitude and longitude.

Each argument accepts DOUBLE, LONG, BIG_DECIMAL, LATITUDE, or LONGITUDE values (latitude and longitude are stored as decimal degrees in GS). Returns a DOUBLE. Returns NULL if any input is NULL. SELECT EARTH_DISTANCE(40.7128, -74.0060, 34.0522, -118.2437) → 2451.18 (NYC to LA, miles)
SELECT EARTH_DISTANCE(home_lat, home_lon, _latitude, _longitude) FROM vehicle
SELECT _component_name FROM site WHERE EARTH_DISTANCE(_latitude, _longitude, 41.88, -87.63) < 25

EARTH_DISTANCE_KM EARTH_DISTANCE_KM(lat1, lon1, lat2, lon2)

Same as EARTH_DISTANCE, but returns the great-circle distance in kilometers. Uses the WGS84 equatorial radius (6378.168 km).

Accepts DOUBLE, LONG, BIG_DECIMAL, LATITUDE, or LONGITUDE per argument. Returns a DOUBLE. Returns NULL if any input is NULL. SELECT EARTH_DISTANCE_KM(40.7128, -74.0060, 34.0522, -118.2437) → 3944.42 (NYC to LA, km)
SELECT _component_name, EARTH_DISTANCE_KM(_latitude, _longitude, 51.5074, -0.1278) AS km_from_london FROM site

EARTH_BEARING EARTH_BEARING(lat1, lon1, lat2, lon2)

Returns the initial compass bearing in degrees along the great-circle arc from point 1 toward point 2. The result is in the half-open range [0, 360): 0 = North, 90 = East, 180 = South, 270 = West.

"Initial" because bearing changes continuously along a great-circle path; this is the bearing measured at the starting point, which is the typical "which way is the next site from here" semantics.

lat1, lon1: Starting point latitude and longitude (decimal degrees).
lat2, lon2: End point latitude and longitude (decimal degrees).

Each argument accepts DOUBLE, LONG, BIG_DECIMAL, LATITUDE, or LONGITUDE values. Returns a DOUBLE. Returns NULL if any input is NULL. SELECT EARTH_BEARING(40.0, -75.0, 41.0, -75.0) → 0 (due North)
SELECT EARTH_BEARING(40.0, -75.0, 40.0, -74.0) → 90 (due East)
SELECT _component_name, EARTH_BEARING(_latitude, _longitude, 41.88, -87.63) AS heading_to_chicago FROM site

EARTH_MIDPOINT_LAT EARTH_MIDPOINT_LAT(lat1, lon1, lat2, lon2)

Returns the latitude of the geographic midpoint along the great-circle arc between two (lat, lon) points. Use together with EARTH_MIDPOINT_LON to get the full midpoint coordinate.

lat1, lon1: First point latitude and longitude (decimal degrees).
lat2, lon2: Second point latitude and longitude (decimal degrees).

Each argument accepts DOUBLE, LONG, BIG_DECIMAL, LATITUDE, or LONGITUDE values. Returns a DOUBLE. Returns NULL if any input is NULL. SELECT EARTH_MIDPOINT_LAT(40.0, -75.0, 42.0, -75.0) → 41.0
SELECT EARTH_MIDPOINT_LAT(a._latitude, a._longitude, b._latitude, b._longitude) FROM site a, site b

EARTH_MIDPOINT_LON EARTH_MIDPOINT_LON(lat1, lon1, lat2, lon2)

Returns the longitude of the geographic midpoint along the great-circle arc between two (lat, lon) points. The result is normalized to the range (-180, 180]. Use together with EARTH_MIDPOINT_LAT to get the full midpoint coordinate.

lat1, lon1: First point latitude and longitude (decimal degrees).
lat2, lon2: Second point latitude and longitude (decimal degrees).

Each argument accepts DOUBLE, LONG, BIG_DECIMAL, LATITUDE, or LONGITUDE values. Returns a DOUBLE. Returns NULL if any input is NULL. SELECT EARTH_MIDPOINT_LON(40.0, -75.0, 40.0, -77.0) → -76.0
SELECT EARTH_MIDPOINT_LAT(a._latitude, a._longitude, b._latitude, b._longitude) AS mlat, EARTH_MIDPOINT_LON(a._latitude, a._longitude, b._latitude, b._longitude) AS mlon FROM site a, site b

SIN SIN(number)

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT SIN(45) → 0.8509035245341184

SINH SINH(number)

Returns the hyperbolic sine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT SINH(.5) → 0.5210953054937474

ASIN ASIN(number)

Returns the arcsine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT ASIN(0.5) →0.5235987755982989

ASINH ASINH(number)

Returns the hyperbolic arcsine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. SELECT ASINH(0.5) → 0.48121182505960347

COS COS(number)

A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT COS(90) → -0.4480736161291702

COSH COSH(number)

A mathematical function that returns the hyperbolic cosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT COSH(0.5) → 1.1276259652063807

ACOS ACOS(number)

A mathematical function that returns the arccosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT ACOS(.5) → 1.0471975511

ACOSH ACOSH(number)

A mathematical function that returns the hyperbolic arccosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. SELECT ACOSH(10) → 2.993222846126381

TAN TAN(number)

Returns the trigonometric tangent of the specified angle, in radians, and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT TAN(33) →-75.31301480008509

TANH TANH(number)

Returns the hyperbolic tangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT TANH(33) →-75.31301480008509

ATAN ATAN(number)

Returns the arctangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT ATAN(33) → 1.54050257

ATANH ATANH(number)

Returns the hyperbolic arctangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. SELECT ATANH(0.5) → 0.5493061443340548

ATAN2 ATAN2(y, x)

Two-argument arctangent. Returns the angle whose tangent is y/x, with the quadrant determined by the signs of both arguments. Result range is (-π, π] radians. NULL on any NULL argument.

y: y-coordinate (numeric).
x: x-coordinate (numeric).
Returns DOUBLE. SELECT ATAN2(1, 1) → 0.7853981633974483 -- pi/4
SELECT ATAN2(1, 0) → 1.5707963267948966 -- pi/2
SELECT ATAN2(0, -1) → 3.141592653589793 -- pi
SELECT ATAN2(-1, -1) → -2.356194490192345 -- -3pi/4

CBRT CBRT(x)

Cube root. Returns DOUBLE. Defined for all real inputs (negatives produce a negative root). NULL on NULL.

x: The number to process. SELECT CBRT(27) → 3.0
SELECT CBRT(-8) → -2.0
SELECT CBRT(0) → 0.0

COT COT(x)

Cotangent of x radians. Returns DOUBLE. Equivalent to 1 / TAN(x) — returns positive or negative Infinity at multiples of π (where the sine is zero). NULL on NULL.

x: The angle in radians. SELECT COT(PI() / 4) → 1.0
SELECT COT(1) → 0.6420926159343306
SELECT COT(0) → Infinity

MOD MOD(a, b)

Returns the remainder of a divided by b. Equivalent to the % operator. The return type follows type precedence: BigDecimal > Double > Long.

a: The dividend (numeric).
b: The divisor (numeric). SELECT MOD(10, 3) → 1
SELECT MOD(10.5, 3.0) → 1.5
SELECT MOD(17, 5) → 2

GREATEST GREATEST(expr1, expr2 [, ...])

Returns the greatest (maximum) value from a list of expressions. If any argument is NULL, returns NULL. Supports numeric and string comparisons.

GREATEST requires at least two arguments. It accepts any number of additional arguments.

expr1: The first expression to compare.
expr2: The second expression to compare.
...: Optional. Additional expressions to compare. SELECT GREATEST(1, 5, 3) → 5
SELECT GREATEST('apple', 'cherry', 'banana') → 'cherry'
SELECT GREATEST(1, NULL, 3) → NULL

LEAST LEAST(expr1, expr2 [, ...])

Returns the least (minimum) value from a list of expressions. If any argument is NULL, returns NULL. Supports numeric and string comparisons.

LEAST requires at least two arguments. It accepts any number of additional arguments.

expr1: The first expression to compare.
expr2: The second expression to compare.
...: Optional. Additional expressions to compare. SELECT LEAST(5, 1, 3) → 1
SELECT LEAST('apple', 'cherry', 'banana') → 'apple'
SELECT LEAST(1, NULL, 3) → NULL

DIV DIV(y, x)

Truncated integer division. Equivalent to Java's / on long. Raises on division by zero. SELECT DIV(17, 5) → 3
SELECT DIV(-7, 2) → -3

GCD / LCM GCD(a, b)
LCM(a, b)

Greatest common divisor / least common multiple. Both non-negative. GCD(0, 0) = 0 and LCM(0, anything) = 0 per PostgreSQL. LCM raises on overflow. SELECT GCD(48, 18) → 6
SELECT LCM(4, 6) → 12

FACTORIAL FACTORIAL(n)

Returns n! for a non-negative integer n as a BIG_DECIMAL (so large results don't overflow). Matches PostgreSQL: raises an error on a negative argument.

n: Non-negative LONG.

Capped at n = 10000 to prevent out-of-memory; larger values raise. SELECT FACTORIAL(0) → 1
SELECT FACTORIAL(5) → 120
SELECT FACTORIAL(20) → 2432902008176640000
SELECT FACTORIAL(-1) → ERROR: factorial of a negative number

MIN_SCALE MIN_SCALE(numeric)

Returns the minimum scale required to represent the supplied value exactly — i.e., the scale after stripping trailing zeros. Returns LONG.

numeric: A NUMERIC / BIG_DECIMAL expression. SELECT MIN_SCALE(8.4100) → 2
SELECT MIN_SCALE(8.41) → 2
SELECT MIN_SCALE(8.0) → 0
SELECT MIN_SCALE(8) → 0

SCALE SCALE(numeric)

Returns the number of fractional digits to the right of the decimal point as a LONG. Returns 0 for integer-valued inputs. Distinct from MIN_SCALE, which strips trailing zeros first.

numeric: A NUMERIC / BIG_DECIMAL expression. SELECT SCALE(8.4100) → 4
SELECT SCALE(8.41) → 2
SELECT SCALE(8.0) → 1
SELECT SCALE(8) → 0

TRIM_SCALE TRIM_SCALE(numeric)

Returns the input with trailing zeros stripped from the scale. Output type is NUMERIC / BIG_DECIMAL. The value is unchanged mathematically; only the textual scale shrinks.

numeric: A NUMERIC / BIG_DECIMAL expression. SELECT TRIM_SCALE(8.4100) → 8.41
SELECT TRIM_SCALE(8.0) → 8
SELECT TRIM_SCALE(0.0500) → 0.05

WIDTH_BUCKET

WIDTH_BUCKET(operand, low, high, count)

Returns the 1-based histogram bucket index that operand falls into, across count equal-width buckets spanning [low, high). Returns LONG.

Edge cases:

Returns 0 if operand < low (underflow bucket).
Returns count + 1 if operand >= high (overflow bucket).
Otherwise returns floor((operand - low) / (high - low) × count) + 1.
Raises an error if count <= 0.

operand: Value to bucket.
low: Lower bound (inclusive).
high: Upper bound (exclusive).
count: Number of equal-width buckets (must be > 0).

SELECT WIDTH_BUCKET(5, 0, 10, 5) → 3
SELECT WIDTH_BUCKET(-1, 0, 10, 5) → 0 -- below low
SELECT WIDTH_BUCKET(10, 0, 10, 5) → 6 -- at high → overflow
SELECT WIDTH_BUCKET(2.5, 0, 10, 5) → 2

TO_NUMBER TO_NUMBER(text, format)

Inverse of numeric TO_CHAR — parses a number from a formatted string. Output type is BIGDECIMAL to preserve precision for currency and percent strings. Lenient fallback strips non-digit characters if the format parser doesn't match. SELECT TO_NUMBER('1,234.56', '9,999.99') → 1234.56
SELECT TO_NUMBER('$99.50', '$99.99') → 99.50

TO_CHAR (numeric form) TO_CHAR(number, format)

Formats a number using PostgreSQL numeric format tokens. Subset coverage focused on Power BI / Tableau export formats.

Supported tokens: 9 (digit, suppressed when leading-zero), 0 (digit, leading zeros shown), , (thousands group), . (decimal), $ (currency). Prefix with FM for fill-mode (suppresses padding). See also the date form in the Date Functions section. SELECT TO_CHAR(1234.56, '999,999.99') → '1,234.56'
SELECT TO_CHAR(99.5, 'FM999.00') → '99.50'

JSON Functions

ISJSON

ISJSON(jsonString [,typeConstraint])

Determine whether a given string is valid JSON.

jsonString: A String data type that will be tested as a JSON string.
typeConstraint: Optional. Defaults to VALUE. One of the following:

VALUE: Tests for a valid JSON value. This can be a JSON object, array, number, string, false, true
SCALAR: Tests for a valid JSON scalar – number, string, false, true)
OBJECT: Tests for a valid JSON object
ARRAY: Tests for a valid JSON Array

Specifying the typeConstraint argument puts a constraint on which type of JSON object is allowed. If the string is valid JSON, but not that type, false is returned.

SELECT ISJSON(1) -> throws exception - parameter is not a string
SELECT ISJSON('1') -> true
SELECT ISJSON('"1"') -> true
SELECT ISJSON('x') -> false
SELECT ISJSON('"x"') -> true per RFC 8259.
SELECT ISJSON('[]') -> true
SELECT ISJSON('{}') -> true
SELECT ISJSON(NULL) -> false
SELECT ISJSON('NULL') -> true
SELECT ISJSON(1, SCALAR) -> throws exception - parameter is not a string
SELECT ISJSON('1', SCALAR) -> true
SELECT ISJSON('"1"', SCALAR) -> true
SELECT ISJSON('1', ARRAY) -> false
SELECT ISJSON('1', OBJECT) -> false
SELECT ISJSON('{}', SCALAR) -> false
SELECT ISJSON('{}', ARRAY) -> false
SELECT ISJSON('{}', OBJECT) -> true
SELECT ISJSON('[]', ARRAY) -> true

JSON_EXISTS JSON_EXISTS(jsonString, jsonPath)

Determines whether a JSON string satisfies a given path search criterion.

jsonString: A String data type that will be tested as a JSON string.
jsonPath: A valid SQL/JSON path to test in the input. SELECT JSON_EXISTS('{"info":{"address":[{"town":"LA"},{"town":"Atlanta"}]}}', '$.info.address') -> true
SELECT JSON_EXISTS('{"info":{"address":[{"town":"LA"},{"town":"Atlanta"}]}}', '$.info.addresses') -> false
SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}', '$.a[0].b') -> true
SELECT JSON_EXISTS('{"a": true}', $.[]b" -> Throws path parsing exception
SELECT JSON_EXISTS('{\"user\":{\"name\":null}}', '$.user.name') -> false

JSON_OBJECT JSON_OBJECT([keyName, keyValue [,...keyName, keyValue]])

Builds a JSON object string from a list of zero or more key-value pairs.

Returns the JSON object as a JSON string.

Key names must be non-null non-empty strings.

String values that are detected as JSON_ARRAY or JSON_OBJECT are inserted without escaping quotes. This allows building nested JSON structures. JSON_OBJECT converts the SQL NULL value into a JSON null value when generating the keyValue of the element. It does not remove NULL elements from the resulting object. SELECT JSON_OBJECT() -> '{}'
SELECT JSON_OBJECT('a', 1, 'b', 2) -> '{"a":1,"b":2}'
SELECT JSON_OBJECT('a', 1.0, 'b', 2.0) -> '{"a":1.0,"b":2.0}'
SELECT JSON_OBJECT('a', '[]') -> '{"a":[]}'
SELECT JSON_OBJECT('a', NULL, 'b', JSON_ARRAY(1,2)) -> '{"a":null,"b":[1.0,2.0]}'
SELECT JSON_OBJECT('a',NULL,'b',JSON_ARRAY('c','xxx')) -> '{"a":null,"b":{"c":"xxx"}}'
SELECT JSON_OBJECT('a') -> throws exception. Invalid number of arguments'
SELECT JSON_OBJECT(1, 1) -> throws exception. Parameter 1 is not a string'

JSON_ARRAY JSON_ARRAY(arrayValue [,...n])

Constructs JSON array text from zero or more expressions.

Returns the JSON array as a JSON string.

String values that are detected as JSON_ARRAY or JSON_OBJECT are inserted without escaping quotes. This allows building nested JSON structures.

JSON_ARRAY converts the SQL NULL value into a JSON null value when generating the value of the element in the JSON array. It does not remove NULL elements from the resulting array. SELECT JSON_ARRAY() -> '[]'
SELECT JSON_ARRAY('') -> '[""]'
SELECT JSON_ARRAY(1, '2') -> '[1,"2"]'
SELECT JSON_ARRAY(1.0) -> '[1.0]'
SELECT JSON_ARRAY(1,NULL) -> '[1,null]'
SELECT JSON_ARRAY(JSON_ARRAY(1)) -> '[[1]]]'
SELECT JSON_ARRAY(1, JSON_OBJECT('a', 1, 'b', 'hello')) -> '[1,{"a":[1,"b":"hello"]}]'

JSON_VALUE JSON_VALUE(jsonString, jsonPath [,defaultValue])

Determine whether a given string is valid JSON.

jsonString: A String data type that will be tested as a JSON string.
jsonPath: A valid SQL/JSON path to test in the input.

defaultValue: Optional. The default value will be returned if the path does not exist. The default value can be a scalar value. String default values are not delimited before returned.

Extracts a scalar value from a JSON string. An exception is thrown if there are no search results and a defaultValue was not included. An exception is thrown if a JSON Object or JSON Array is attempting to be returned.

Use JSON_QUERY to return objects and arrays. SELECT JSON_VALUE('{"a": true}', '$.a') -> true
SELECT JSON_VALUE('{"a": "xxx"}', '$.a') -> 'xxx'
SELECT JSON_VALUE('{"a": 1}', '$.a') -> 1.0
SELECT JSON_VALUE('{"a": 1.0}', '$.a') -> 1.0
SELECT JSON_VALUE('{"a": null}', '$.a') -> null
SELECT JSON_VALUE('{"a": 'null'}', '$.a') -> 'null'
SELECT JSON_VALUE('{"a": true}', '$.b') -> Throws exception - path doesn't exist and no default value
SELECT JSON_VALUE('{"a": { "b": "hello" } }', '$.a.b') -> Throws Exception - not returning a scalar
SELECT JSON_VALUE('{"a": { "b": [] } }', '$.a.b') -> Throws Exception - not returning a scalar
With default values:
SELECT JSON_VALUE('{"a": null}', '$.a', 'xx') -> null
SELECT JSON_VALUE('{"a": null}', '$.b', true) -> true

JSON_QUERY JSON_QUERY(jsonString, jsonPath [,defaultValue])

Extracts JSON values from a JSON string. The result is always returned as a STRING or null.

jsonString: The JSON string to query.
jsonPath: A valid SQL/JSON path.

defaultValue: Optional. defaultValue must be a STRING. The default will be returned if the path does not exist. The default value is not delimited before returned.

Use JSON_VALUE to return scalar values. SELECT JSON_QUERY('{"a": true}', '$.a') -> 'true'
SELECT JSON_QUERY('{"a": 1}', '$.a') -> '1'
SELECT JSON_QUERY('{"a": 1.0}', '$.a') -> '1.0'
SELECT JSON_QUERY('{"a": null}', '$.a') -> null
SELECT JSON_QUERY('{"a": "null"}', '$.a') -> '"null"'
SELECT JSON_QUERY('{"a": true}', '$.b') -> throws exception
SELECT JSON_QUERY('{"a": { "b": "hello" } }', $.a) -> '{"b":"hello"}'
SELECT JSON_QUERY('{"a": null}', '$.a', 'xx') -> null
SELECT JSON_QUERY('{"a": null}', '$.b', 'true') -> '"true"'
SELECT JSON_QUERY('{"a": 1}', '$.b', null) -> null
SELECT JSON_QUERY(JSON_OBJECT('a', JSON_ARRAY('c','d')), '$.a[1]' -> '"d"'

JSON_MODIFY JSON_MODIFY(jsonString, jsonPath ,newValue)

Updates the value of a property in a JSON string and returns the updated JSON string. The result is always returned as a STRING.

jsonString: The JSON string to search and modify.
jsonPath: A valid SQL/JSON path.

newValue: newValue can be a scalar value (number, boolean, string). JSON_MODIFY deletes the specified key if newValue is NULL.

If the property isn't present, JSON_MODIFY tries to insert the new value on the specified path. Insertion can fail if the property can't be inserted on the path.

Nested arrays are supported.

JSON_MODIFY escapes all special characters in the new value if the type of the value is STRING. A STRING value isn't escaped if it's properly formatted JSON produced by FOR JSON, JSON_QUERY, or JSON_MODIFY. SELECT JSON_MODIFY('{"a": true}', '$.a', false) -> '{"a": false}'
SELECT JSON_MODIFY('{"a": 1}', '$.a', '1') -> '{"a": "1"}'
SELECT JSON_MODIFY('{"a": 1.0}', '$.a', 1) -> '{"a": 1.0}'
SELECT JSON_MODIFY('{"a": null}', '$.a', NULL) -> '{}' - key is deleted
SELECT JSON_MODIFY('[0]', '$[1]', 1) -> '[0,1]'
SELECT JSON_MODIFY('[0]', '$[1][0][1]', 1) -> '[0,[[null,1]]]'
SELECT JSON_MODIFY('{\"a\":{\"b\":{\"c\":[\"xxx\"]}}}', '$.a.b.c[0]', 'yyy') ->
'{\"a\":{\"b\":{\"c\":[\"yyy\"]}}}'
SELECT JSON_MODIFY('{\"name\":\"John\",\"skills\":[\"C#\",[\"SQL\",\"Java\"]]}', '$.skills[1][4].b', 'xxx') ->
'{\"name\":\"John\",\"skills\":[\"C#\",[\"SQL\",\"Java\",null,null,{\"b\":\"xxx\"}]]}'

->
->>
#>
#>> PostgreSQL-compatible JSON access operators (2026-05-30). Lowered to the JSON_QUERY / JSON_VALUE functions above, so any expression you can write with them works here too. The right-hand operand must be a literal — a string key, an integer index, or (for #>/#>>) a PG-style text-array path. Dynamic keys aren't supported.

-> Returns the value at the key/index as JSON text. Equivalent to JSON_QUERY.
->> Returns the value as a SQL scalar (string/number/boolean). Equivalent to JSON_VALUE.
#> Walks a path inside a text-array literal like '{a,b,c}'; returns the value as JSON text.
#>> Same as #> but returns the value as a SQL scalar.

Operators are left-associative; chained access like j -> 'a' -> 'b' works the same as in PG. SELECT '{"a": 1}' -> 'a' —> '1' (as JSON text)
SELECT '{"a": "hello"}' ->> 'a' —> 'hello'
SELECT '[10,20,30]' -> 1 —> '20'
SELECT '{"a":{"b":{"c":42}}}' #> '{a,b,c}' —> '42'
SELECT '{"a":{"b":"deep"}}' #>> '{a,b}' —> 'deep'
SELECT '{"a":{"b":7}}' -> 'a' -> 'b' —> '7'

JSONB_TYPEOF
JSON_TYPEOF JSONB_TYPEOF(jsonString)

Returns the top-level JSON type of the input as one of: 'object', 'array', 'string', 'number', 'boolean', 'null'. PG-compatible. SELECT JSONB_TYPEOF('{"a":1}') —> 'object'
SELECT JSONB_TYPEOF('[1,2,3]') —> 'array'
SELECT JSONB_TYPEOF('"hi"') —> 'string'
SELECT JSONB_TYPEOF('42') —> 'number'
SELECT JSONB_TYPEOF('true') —> 'boolean'
SELECT JSONB_TYPEOF('null') —> 'null'

JSONB_ARRAY_LENGTH
JSON_ARRAY_LENGTH JSONB_ARRAY_LENGTH(jsonString)

Returns the number of elements in a top-level JSON array as a LONG. Throws if the input is not a JSON array. SELECT JSONB_ARRAY_LENGTH('[1,2,3,4,5]') —> 5
SELECT JSONB_ARRAY_LENGTH('[]') —> 0

JSONB_EXTRACT_PATH
JSONB_EXTRACT_PATH_TEXT
JSON_EXTRACT_PATH
JSON_EXTRACT_PATH_TEXT JSONB_EXTRACT_PATH(jsonString, key1, key2, ...)

Variadic path access. Walks the JSON value following each key in order. String keys index into objects; integer keys index into arrays. The _TEXT variants return a SQL scalar (like JSON_VALUE); the non-_TEXT variants return JSON text (like JSON_QUERY). All keys must be literals. SELECT JSONB_EXTRACT_PATH('{"a":{"b":{"c":1}}}', 'a', 'b', 'c') —> '1'
SELECT JSONB_EXTRACT_PATH_TEXT('{"a":{"b":"hi"}}', 'a', 'b') —> 'hi'
SELECT JSONB_EXTRACT_PATH_TEXT('{"a":["x","y"]}', 'a', 1) —> 'y'

JSON_BUILD_OBJECT
JSONB_BUILD_OBJECT
JSON_BUILD_ARRAY
JSONB_BUILD_ARRAY JSON_BUILD_OBJECT([keyName, keyValue [,...]])
JSON_BUILD_ARRAY([value [,...]])

PG-compatible aliases for JSON_OBJECT and JSON_ARRAY. Semantics identical to the originals; only the spelling differs. SELECT JSON_BUILD_OBJECT('a', 1, 'b', 2) —> '{"a":1,"b":2}'
SELECT JSON_BUILD_ARRAY(1, 2, 3) —> '[1,2,3]'

TO_JSON
TO_JSONB TO_JSON(value)

Serializes a SQL value to JSON text. Numbers, booleans, and NULL emit the corresponding JSON literal; strings are JSON-encoded with quotes. Strings that already parse as a JSON object or array pass through without re-quoting. SELECT TO_JSON('hello') —> '"hello"'
SELECT TO_JSONB(42) —> '42'
SELECT TO_JSONB(true) —> 'true'
SELECT TO_JSON('[1,2,3]') —> '[1,2,3]'

JSONB_SET / JSON_MODIFY JSONB_SET(target, path, new_value [, create_if_missing])

PostgreSQL alias for the existing JSON_MODIFY runtime. Sets the value at the given path. Returns the modified JSON. SELECT JSONB_SET('{"a":1}', '$.a', '2') → '{"a":2}'
SELECT JSONB_SET('{"a":1}', '$.b', '"hi"') → '{"a":1,"b":"hi"}'

JSONB_STRIP_NULLS / JSON_STRIP_NULLS JSONB_STRIP_NULLS(json)

Recursively removes object members whose value is JSON null. Array elements that are null are kept (matches PostgreSQL). SELECT JSONB_STRIP_NULLS('{"a":1,"b":null,"c":3}') → '{"a":1,"c":3}'
SELECT JSONB_STRIP_NULLS('[1,null,3]') → '[1,null,3]'

JSONB_PRETTY JSONB_PRETTY(json)

Returns indented, human-readable JSON text. Output is plain text (not JSON itself) — matches PostgreSQL's text return type. SELECT JSONB_PRETTY('{"a":1,"b":2}')
→
{ "a" : 1, "b" : 2 }

JSONB_CONTAINS / JSON_CONTAINS / @> / <@ JSONB_CONTAINS(a, b)
JSON_CONTAINS(a, b)
a @> b
a <@ b

PostgreSQL containment — a @> b is true iff every key/value in b exists in a, recursively. Scalars: deep-equal. Objects: every key in b must exist in a with a contained value. Arrays: every element in b must contain-equal some element in a (set semantics).

a <@ b is the inverse: b contains a (equivalent to b @> a). Both operators are left-associative and lower to the same containment runtime. SELECT JSONB_CONTAINS('{"a":1,"b":2}', '{"a":1}') → true
WHERE jdata @> '{"status":"active"}'::JSONB
WHERE '{"status":"active"}'::JSONB <@ jdata -- same predicate, reversed
SELECT '{"a":1}'::JSONB <@ '{"a":1,"b":2}'::JSONB → true

Aggregate Functions

COUNT

COUNT([DISTINCT] expression)

The COUNT function is an aggregate function that returns the number of rows returned by a query as a Long data type.

The result of the COUNT function depends on the argument that you pass to it:

By default, the COUNT function uses the ALL keyword whether you specify it or not. The ALL keyword means that all items in the group are considered including the duplicate values. For example, if you have a group (1, 2, 3, 3, 4, 4) and apply the COUNT function, the result is 6
If you specify the DISTINCT keyword explicitly, only unique non-null values are considered. The COUNT function returns 4 if you apply it to the group (1,2,3,3,4,4).

The COUNT(*) function returns the number of rows in a table including the rows that contain the NULL values.

DISTINCT: Optional. Indicates whether to include duplicate values. .
expression: *, column name or an expression.

SELECT COUNT(*) FROM system.stream → 10233
SELECT COUNT(_name) FROM system.stream → 10233
SELECT _component_name, COUNT(_name) AS streamCount FROM system.stream WHERE GROUP BY _component_name, COUNT(_name) ORDER BY _component_name, streamCount → Stream Count Per Component

SUM SUM([DISTINCT] expression)

The SUM function is an aggregate function that returns the sum of all non-null.

The SUM function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the SUM function performs the calculation.

If you want to calculate the sum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the SUM function is NULL if and only if all input values are NULL. DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression. SELECT SUM(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 10233
SELECT _component_name, _name, SUM(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream Totals Per Component Stream for the last 12 hours

TOTAL TOTAL([DISTINCT] expression)

The same as the SUM function except the result of the TOTAL function is zero, not NULL, if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name, or an expression. SELECT TOTAL(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 300
SELECT _component_name, _name, TOTAL(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream Totals Per Component Stream for the last 12 hours

MIN MIN([DISTINCT] expression)

The MIN function is an aggregate function that returns the mimimum of all non-null rows.

The MIN function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the MIN function performs the comparison.

If you want to calculate the minimum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the MIN function is NULL if and only if all input values are NULL. SELECT MIN(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 0
SELECT _component_name, _name, MIN(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream mimimums per Component Stream for the last 12 hours

MAX MAX([DISTINCT] expression)

The MAX function is an aggregate function that returns the maximum of all non-null rows.

The MAX function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the MAX function performs the comparison.

If you want to calculate the maximum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the MAX function is NULL if and only if all input values are NULL. SELECT MAX(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 24
SELECT _component_name, _name, MAX(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream maximums per Component Stream for the last 12 hours

AVG AVG([DISTINCT] expression)

The AVG function is an aggregate function that returns the average of all non-null rows.

The AVG function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the AVG function performs the comparison.

If you want to calculate the average of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the AVG function is NULL if and only if all input values are NULL. SELECT AVG(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 12.5
SELECT _component_name, _name, AVG(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

VAR VAR([DISTINCT] expression)

Returns the statistical variance of all values in the specified expression when the group of numbers being evaluated are only a partial sampling of the whole population.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the VAR function is NULL if and only if all input values are NULL or the set has only one value. SELECT VAR(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, VAR(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

VARP VARP([DISTINCT] expression)

Returns the statistical variance for the population for all values in the specified expression when the group of numbers being evaluated is complete - it's the entire population of values.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the VARP function is NULL if and only if all input values are NULL or the set has only one value. SELECT VARP(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, VARP(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

STDEV STDEV([DISTINCT] expression)

STDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the STDEV function is NULL if and only if all input values are NULL or the set has only one value. SELECT STDEV(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, STDEV(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

STDEVP STDEVP([DISTINCT] expression)

STDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the STDEVP function is NULL if and only if all input values are NULL or the set has only one value. SELECT STDEVP(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, STDEVP(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

PERCENTILEINC PERCENTILEINC([DISTINCT] expression , percent)

Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the PERCENTILEINC function is NULL if and only if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression.
percentile: The percentile numeric value greater than or equal to zero and less than or equal to one. SELECT PERCENTILEINC(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, PERCENTILEINC(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

PERCENTILEEXC PERCENTILEEXC([DISTINCT] expression , percent)

The PERCENTILE.EXC function returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the PERCENTILEEXC function is NULL if and only if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression.
percentile: The percentile numeric value greater than zero and less than one. SELECT PERCENTILEEXC(_sample) FROM system.stream WHERE _component_id='c1' AND _id='rdm_dbl' → 7.07
SELECT _component_name, _name, PERCENTILEEXC(_sample(Range(sd=-12h))) FROM system.stream WHERE _id='rdm_lng' GROUP BY _component_name, _name ORDER BY _component_name, _name → Stream average per Component Stream for the last 12 hours

STRING_AGG STRING_AGG([DISTINCT] expression, delimiter [ORDER BY sort_expr [ASC | DESC] [NULLS FIRST | NULLS LAST] [, ...]])

Concatenates the values of string expressions and places delimiter values between them. The delimiter is not added at the end of the string. NULL values are skipped.

The result of the STRING_AGG function is NULL if the group is empty (no non-NULL values).

ORDER BY inside the call controls the order in which values are concatenated. Multiple sort keys are supported, each with optional ASC / DESC and explicit NULLS FIRST / NULLS LAST. Default null ordering follows PostgreSQL: ASC → NULLS LAST, DESC → NULLS FIRST. The sort applies per group, independent of any outer ORDER BY.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: Column name or an expression. Values are converted to strings before concatenation.
delimiter: A string expression used as separator between concatenated values.
ORDER BY ...: Optional. One or more sort keys evaluated within the group; each key takes optional ASC/DESC and NULLS FIRST/LAST. SELECT STRING_AGG(val, ', ') FROM JSON_TABLE('[{"val":"a"},{"val":"b"},{"val":"c"}]') WITH (val STRING) → 'a, b, c'
SELECT STRING_AGG(val, ',') FROM JSON_TABLE('[{"val":"a"},{"val":null},{"val":"c"}]') WITH (val STRING) → 'a,c'
SELECT STRING_AGG(name, ',' ORDER BY priority DESC, name ASC) FROM task → names joined high-priority-first, ties broken by name
SELECT STRING_AGG(DISTINCT name, '; ' ORDER BY name) FROM task → distinct names joined in ascending order

BOOL_OR / BOOL_AND BOOL_OR(expression)
BOOL_AND(expression)

Boolean aggregates (PostgreSQL-flavored). BOOL_OR returns true if at least one input value is true; BOOL_AND returns true only if every non-null input value is true. NULL inputs are ignored. Both return NULL when there are no non-null inputs. Also usable as window functions with an OVER (…) clause.

expression: A boolean column or expression. SELECT BOOL_OR(temperature > 100) FROM sensor → true if any reading exceeds 100
SELECT BOOL_AND(online) FROM device → true only if every device is online

JSON_AGG / JSONB_AGG JSON_AGG(expression)
JSONB_AGG(expression)

Aggregates the input values from a group into a single JSON array, in input order. NULL values are included as JSON null. JSON_AGG and JSONB_AGG are equivalent (GS unifies JSON and JSONB).

expression: Any column or expression to collect into the array. SELECT JSON_AGG(cname) FROM sensor → ["sensor-1","sensor-2","sensor-3"]
SELECT _component_name, JSON_AGG(_name) FROM system.stream GROUP BY _component_name → one array of stream names per component

JSON_OBJECT_AGG / JSONB_OBJECT_AGG JSON_OBJECT_AGG(key, value)
JSONB_OBJECT_AGG(key, value)

Aggregates key/value pairs from a group into a single JSON object. Keys are coerced to strings. JSON_OBJECT_AGG and JSONB_OBJECT_AGG are equivalent.

key: The expression used as each object key.
value: The expression used as each object value. SELECT JSON_OBJECT_AGG(cname, lastValue) FROM sensor → {"sensor-1":21.4,"sensor-2":22.0}

Window Functions

Window functions compute a value across a set of rows related to the current row, without collapsing them into a single output row like aggregates do. They are written with an OVER (…) clause:

function(args) OVER ( [PARTITION BY …] [ORDER BY … [NULLS FIRST|LAST]] [frame] )

The frame may use ROWS (row counts) or RANGE (value-based, ideal for time windows) between UNBOUNDED PRECEDING / expr PRECEDING / CURRENT ROW / expr FOLLOWING / UNBOUNDED FOLLOWING, with optional frame-EXCLUDE and named WINDOW clauses. All four ANSI / PostgreSQL EXCLUDE variants are supported: EXCLUDE CURRENT ROW, EXCLUDE GROUP (drop the current row and every peer that ties on the ORDER BY keys), EXCLUDE TIES (keep the current row, drop its peers), and EXCLUDE NO OTHERS (the default; explicit form accepted). Every aggregate function (SUM, AVG, COUNT, MIN, MAX, STDEV, VAR, BOOL_OR, …) can also be used as a window function with OVER (…).

For time-series math over pre-computed rollups, the temporal parameters (sample(running='sum', cycleId='gs.day')) are faster and rollup-aware — reach for those when you want rollup-backed speed, and for OVER (…) when you want standard SQL windowing.

ROW_NUMBER / RANK / DENSE_RANK ROW_NUMBER() OVER (…)
RANK() OVER (…)
DENSE_RANK() OVER (…)

Ranking functions. ROW_NUMBER assigns a unique sequential number per partition. RANK assigns the same rank to ties and leaves gaps; DENSE_RANK ranks ties the same but leaves no gaps. Require an ORDER BY in the OVER clause. SELECT cname, kwh, ROW_NUMBER() OVER (ORDER BY kwh DESC) AS rn FROM meter
SELECT cname, kwh, RANK() OVER (PARTITION BY region ORDER BY kwh DESC) AS r FROM meter

PERCENT_RANK / CUME_DIST / NTILE PERCENT_RANK() OVER (…)
CUME_DIST() OVER (…)
NTILE(n) OVER (…)

Distribution functions. PERCENT_RANK is the relative rank (0–1). CUME_DIST is the cumulative distribution (fraction of rows at or below the current row). NTILE(n) divides the partition into n roughly equal buckets and returns the bucket number.

n: Number of buckets (NTILE only). SELECT cname, kwh, NTILE(4) OVER (ORDER BY kwh) AS quartile FROM meter
SELECT cname, kwh, PERCENT_RANK() OVER (ORDER BY kwh) AS pr FROM meter

LAG / LEAD LAG(expression [, offset [, default]]) OVER (…)
LEAD(expression [, offset [, default]]) OVER (…)

Access a value from a row that is offset rows before (LAG) or after (LEAD) the current row within the partition. When the offset row does not exist, returns default (NULL if omitted). Useful for period-over-period deltas.

offset: Optional. Number of rows to look back/ahead (default 1).
default: Optional. Value returned when the offset falls outside the partition. SELECT time, kwh, kwh - LAG(kwh) OVER (PARTITION BY meterUid ORDER BY time) AS delta FROM meter

FIRST_VALUE / LAST_VALUE / NTH_VALUE FIRST_VALUE(expression) OVER (…)
LAST_VALUE(expression) OVER (…)
NTH_VALUE(expression, n) OVER (…)

Return the value of expression at the first, last, or n-th row of the current window frame. Note that LAST_VALUE is sensitive to the frame — with the default frame it returns the current row; use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for the partition's last value.

n: 1-based position within the frame (NTH_VALUE only). SELECT cname, FIRST_VALUE(kwh) OVER (PARTITION BY meterUid ORDER BY time) AS first_kwh FROM meter

Control Functions

IF / IIF IIF(condition, value_if_true, value_if_false)
IF(condition, value_if_true, value_if_false)

Both IF and IFF are supported and function the same.

It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false.

Returns the data type of the value_if_true or value_if_false.

condition: The condition expression.
value_if_true: Optional. Value to return if condition is true
value_if_false: Optional. Value to return if condition is false IIF(condition, value_if_true, value_if_false)
SELECT IIF( kWh > 50, cost*0.5, cost)
SELECT IF( 100 > 50,'High', 'Low') → 'High'

CASE CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END

input_expression: Optional. The condition base expression to be compared with each when_expression.
when_expression: A boolean expression.
result_expression: The result to use if the preceding when_expression is false.
else_result_expression: Optional. The result to use if no conditions were true or the base expression was null. Evaluates a list of conditions and returns one of multiple possible result expressions.

The optional input_expression that occurs in between the CASE keyword and the first WHEN keyword is called the "base" expression. There are two fundamental forms of the CASE expression: those with a base expression and those without.

In a CASE without a base expression, each WHEN expression is evaluated and the result treated as a boolean, starting with the leftmost and continuing to the right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression that evaluates to true. Or, if none of the WHEN expressions evaluate to true, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions are true, then the overall result is NULL.

A NULL result is considered untrue when evaluating WHEN terms.

In a CASE with a base expression, the base expression is evaluated just once and the result is compared against the evaluation of each WHEN expression from left to right. The result of the CASE expression is the evaluation of the THEN expression that corresponds to the first WHEN expression for which the comparison is true. Or, if none of the WHEN expressions evaluate to a value equal to the base expression, the result of evaluating the ELSE expression, if any. If there is no ELSE expression and none of the WHEN expressions produce a result equal to the base expression, the overall result is NULL.

When comparing a base expression against a WHEN expression, the same collating sequence, affinity, and NULL-handling rules apply as if the base expression and WHEN expression are respectively the left- and right-hand operands of an = operator.

If the base expression is NULL then the result of the CASE is always the result of evaluating the ELSE expression if it exists, or NULL if it does not. SELECT _component_id, _id,
CASE
WHEN _sample > 100 THEN 'High'
WHEN _sample BETWEEN 50 AND 100 THEN 'Medium'
ELSE 'Low'
END AS sample_category
FROM system.stream;

SELECT _component_id, _id, _sample,
CASE _sample
WHEN 'H' THEN 'High'
WHEN 'M' THEN 'Medium'
ELSE 'Low'
END AS sample_category
FROM system.stream;

COALESCE / IFNULL / NVL COALESCE(expr1, expr2 [, ...])
IFNULL(expr1, expr2 [, ...])
NVL(expr1, expr2 [, ...])

Returns the first non-NULL argument. If all arguments are NULL, returns NULL.

COALESCE, IFNULL, and NVL are supported and function the same.

COALESCE requires at least two arguments. It accepts any number of additional arguments.

The return data type is determined by the first non-NULL argument.

expr1: The first expression to evaluate.
expr2: The second expression to evaluate.
...: Optional. Additional expressions to evaluate. SELECT COALESCE(NULL, 'b') → 'b'
SELECT COALESCE('a', 'b') → 'a'
SELECT COALESCE(NULL, NULL, 'c') → 'c'
SELECT COALESCE(NULL, NULL) → NULL

NULLIF NULLIF(expr1, expr2)

Returns NULL if expr1 equals expr2; otherwise returns expr1.

Commonly used to prevent division-by-zero errors by converting a zero denominator to NULL, which causes the division to return NULL instead of raising an error.

The return data type is the data type of the first argument.

expr1: The expression to return if not equal to expr2.
expr2: The expression to compare against expr1. SELECT NULLIF('a', 'a') → NULL
SELECT NULLIF('a', 'b') → 'a'
SELECT 100 / NULLIF(0, 0) → NULL

ISNULL ISNULL(expr, default)

Returns expr if it is not NULL; otherwise returns default. Functionally equivalent to COALESCE(expr, default).

The return data type is determined by the first non-NULL argument.

expr: The expression to evaluate.
default: The value to return if expr is NULL. SELECT ISNULL(5, 10) → 5
SELECT ISNULL(NULL, 10) → 10
SELECT ISNULL(NULL, 'default') → 'default'

Other Functions

CAST

CAST(value AS typename([precision][,scale]))

The CAST() function converts a value (of any type) into a specified data type. Also look at the CONVERT() function.

Cast Type Names (case insensitive):

STRING (aliases: TEXT, VARCHAR)
BOOLEAN (alias: BOOL)
LONG (aliases: INT8, BIGINT, INTEGER, INT4, SMALLINT, INT2, TIMESTAMP, TIMESTAMPTZ)
DOUBLE (aliases: FLOAT8, FLOAT4, REAL)
BIGDECIMAL (aliases: NUMERIC, DECIMAL)

PostgreSQL / ANSI aliases were added in the May 2026 release. They map to the canonical type on the same line and are accepted in CAST, the postfix :: cast operator, CREATE TABLE column declarations, and JSON_TABLE / CSV_TABLE column declarations.

value: The number to be cast.
typename: The type to cast to (see list above).
precision: Optional. Precision of BigDecimal casts.
scale: Optional. Scale of BigDecimal casts.

SELECT CAST(25.65 AS LONG) → 25
SELECT CAST('25.65' AS LONG) → 25
SELECT CAST('25.65' AS DOUBLE) → 25.65
SELECT CAST('25.6534' AS BIGDECIMAL) → 25.6534
SELECT CAST('25.6534' AS BIGDECIMAL(10,2)) → 25.65
SELECT CAST(25.65 AS STRING) + 'gallons' → '25.65 gallons'
SELECT CAST(1 AS BOOLEAN) → true
SELECT CAST(2 AS BOOLEAN) → true
SELECT CAST(0 AS BOOLEAN) → false
SELECT CAST('off' AS BOOLEAN) → false
SELECT CAST('no' AS BOOLEAN) → false
SELECT CAST('abc' AS BOOLEAN) → true

CONVERT

CONVERT(typename, expression)

Converts a value to a specified data type. Similar to CAST but uses function syntax. Also look at the CAST expression.

Type Names (case insensitive):

STRING
BOOLEAN
LONG
DOUBLE
BIGDECIMAL

typename: A string specifying the target type (see list above).
expression: The value to convert.

SELECT CONVERT('LONG', '25.65') → 25
SELECT CONVERT('STRING', 42) → '42'
SELECT CONVERT('DOUBLE', '3.14') → 3.14
SELECT CONVERT('BOOLEAN', 1) → true

PG_TYPEOF PG_TYPEOF(expression)

Returns the PostgreSQL-style data type name of the supplied expression as a string. Provided for PostgreSQL / BI-tool compatibility — some tools call pg_typeof during result-set introspection.

expression: Any expression. Only the static type is inspected; the value is not evaluated for its side effects.

Returned names use the PG canonical spellings: bigint (LONG), double precision (DOUBLE), text (STRING), boolean (BOOLEAN), numeric (BIGDECIMAL), unknown when the type cannot be determined. SELECT PG_TYPEOF(42) → 'bigint'
SELECT PG_TYPEOF(3.14) → 'double precision'
SELECT PG_TYPEOF('hello') → 'text'
SELECT PG_TYPEOF(true) → 'boolean'

CURRENT_DATABASE / CURRENT_SCHEMA / CURRENT_SCHEMAS CURRENT_DATABASE()
CURRENT_SCHEMA()
CURRENT_SCHEMAS(include_implicit)

Session context functions provided for PostgreSQL / BI-tool compatibility. CURRENT_DATABASE() returns the connected database (the GroveStreams organization). CURRENT_SCHEMA() returns the default schema, always public. CURRENT_SCHEMAS() returns the schema search path. SELECT CURRENT_SCHEMA() → 'public'

CURRENT_USER / SESSION_USER CURRENT_USER
SESSION_USER

Return the user associated with the current connection. Both the bare-keyword form and the parenthesized function form are accepted. SELECT CURRENT_USER → the connected user

FORMAT_TYPE FORMAT_TYPE(type_oid, typemod)

Returns the PostgreSQL display name for a type OID and modifier (for example a length or precision/scale), the form that tools such as DBeaver and dbt emit during introspection. Returns NULL for an unknown or NULL OID.

type_oid: A PostgreSQL type OID.
typemod: Optional. The type modifier, or -1 / NULL for none. SELECT FORMAT_TYPE(1043, 68) → 'character varying(64)'
SELECT FORMAT_TYPE(1700, 655366) → 'numeric(10,2)'
SELECT FORMAT_TYPE(23, -1) → 'integer'

AGE AGE(timestamp)
AGE(timestamp_end, timestamp_start)

Returns the elapsed time between two timestamps. The one-argument form measures from the given timestamp to the current time; the two-argument form returns timestamp_end minus timestamp_start.

timestamp: The timestamp to measure from.
timestamp_start: Optional. The earlier bound of the interval. SELECT AGE(startDate, endDate) FROM …

PG_ENCODING_TO_CHAR PG_ENCODING_TO_CHAR(encoding_id)

Returns the character-set name for a PostgreSQL encoding id — another introspection helper used by PostgreSQL clients.

encoding_id: A PostgreSQL encoding number. SELECT PG_ENCODING_TO_CHAR(6) → 'UTF8'

PostgreSQL catalog functions PostgreSQL clients, drivers, and BI tools (psql, DBeaver, dbt, Power BI, …) issue catalog and introspection functions while inspecting a database. GS SQL accepts these so those tools connect and operate without errors. They are recognized for compatibility and return safe placeholder values (NULL, 0, or the current session value) rather than internal storage details:

PG_GET_VIEWDEF, PG_GET_INDEXDEF, PG_GET_CONSTRAINTDEF, PG_GET_PARTKEYDEF, PG_GET_RULEDEF, PG_GET_TRIGGERDEF, PG_GET_EXPR, PG_GET_FUNCTION_ARGUMENTS, PG_GET_FUNCTION_RESULT, PG_GET_FUNCTION_SQLBODY, PG_GET_USERBYID, PG_RELATION_SIZE, PG_TOTAL_RELATION_SIZE, PG_TABLE_SIZE, PG_DATABASE_SIZE, PG_TABLE_IS_VISIBLE, PG_IS_IN_RECOVERY, PG_BACKEND_PID, PG_POSTMASTER_START_TIME, PG_STAT_GET_NUMSCANS, PG_TABLESPACE_LOCATION, TXID_CURRENT. SELECT PG_IS_IN_RECOVERY() → false

CURRENT_SETTING CURRENT_SETTING(name [, missing_ok])

Reads a session GUC (Grand Unified Configuration parameter) and returns its current value as STRING. Recognized names include timezone, search_path, application_name, server_version, client_encoding, bytea_output, IntervalStyle, lc_collate, lc_ctype, plus the standard PostgreSQL settings probed by BI/ORM tools at connect time.

name: GUC name (case-insensitive for PG's standard names).
missing_ok: Optional BOOLEAN. If TRUE and the name is unknown, returns NULL. If FALSE or omitted, an unknown name raises an error. SELECT CURRENT_SETTING('timezone') → 'UTC'
SELECT CURRENT_SETTING('server_version') → '14.0 (GroveStreams)'
SELECT CURRENT_SETTING('client_encoding') → 'UTF8'
SELECT CURRENT_SETTING('nonsuch', TRUE) → NULL
SELECT CURRENT_SETTING('nonsuch') → ERROR: unrecognized configuration parameter

SET_CONFIG SET_CONFIG(name, value, is_local)

Writes a session GUC and returns the new value as STRING. Today the write is accepted but not persisted across sessions — the value is acknowledged and returned so PG-flavored ORM/driver bootstrap code runs unchanged. is_local is accepted for PG-compat parity.

name: GUC name.
value: New value (STRING).
is_local: BOOLEAN. TRUE = transaction-scoped (no-op here since no transactions). SELECT SET_CONFIG('search_path', 'public', false) → 'public'
SELECT SET_CONFIG('application_name', 'etl', true) → 'etl'

HAS_TABLE_PRIVILEGE HAS_TABLE_PRIVILEGE([user,] table, privilege)

Returns TRUE iff the named table exists and is accessible to the user. GS uses its own RBAC model, so this PG-compat probe always succeeds for tables that resolve. privilege is parsed but does not gate the answer; it is one of SELECT, INSERT, UPDATE, DELETE, REFERENCES (case-insensitive, comma-separated allowed).

user: Optional. Defaults to the connected user.
table: Table name or OID-shaped identifier.
privilege: STRING. SELECT HAS_TABLE_PRIVILEGE('sensor', 'SELECT') → true
SELECT HAS_TABLE_PRIVILEGE(CURRENT_USER, 'meter', 'INSERT, UPDATE') → true
SELECT HAS_TABLE_PRIVILEGE('nonsuch', 'SELECT') → false

HAS_COLUMN_PRIVILEGE HAS_COLUMN_PRIVILEGE([user,] table, column, privilege)

Returns TRUE iff the named table exists and contains the named column. As with HAS_TABLE_PRIVILEGE, the privilege string is parsed but not enforced here — access checks happen at execution time through GS RBAC. SELECT HAS_COLUMN_PRIVILEGE('sensor', 'temperature', 'SELECT') → true
SELECT HAS_COLUMN_PRIVILEGE('sensor', 'nonsuch', 'SELECT') → false

HAS_ANY_COLUMN_PRIVILEGE HAS_ANY_COLUMN_PRIVILEGE([user,] table, privilege)

Returns TRUE iff the table exists and has at least one column. Used by BI tools that probe for "can I read anything at all from this table?" before issuing a query. SELECT HAS_ANY_COLUMN_PRIVILEGE('sensor', 'SELECT') → true

HAS_DATABASE_PRIVILEGE HAS_DATABASE_PRIVILEGE([user,] db, privilege)

Returns TRUE iff db equals the current database (the connected organization). All other names return FALSE. SELECT HAS_DATABASE_PRIVILEGE(CURRENT_DATABASE(), 'CONNECT') → true
SELECT HAS_DATABASE_PRIVILEGE('postgres', 'CONNECT') → false

HAS_SCHEMA_PRIVILEGE HAS_SCHEMA_PRIVILEGE([user,] schema, privilege)

Returns TRUE for the schema names that GS SQL recognizes: public, pg_catalog, information_schema, plus the catalog names registered in the connected organization. Returns FALSE for unknown names. SELECT HAS_SCHEMA_PRIVILEGE('public', 'USAGE') → true
SELECT HAS_SCHEMA_PRIVILEGE('pg_catalog', 'USAGE') → true
SELECT HAS_SCHEMA_PRIVILEGE('nonsuch', 'USAGE') → false

TO_REGCLASS / TO_REGTYPE / TO_REGROLE TO_REGCLASS(text) → regclass
TO_REGTYPE(text) → regtype
TO_REGROLE(text) → regrole

Name-to-OID-shaped-identifier resolvers, the PG-flavored introspection helpers BI tools use when probing the catalog. Each returns NULL when the name does not resolve.

TO_REGCLASS: returns a regclass for a known table name; NULL otherwise.
TO_REGTYPE: returns a regtype for a known type name (canonical, alias, or PG type name); NULL otherwise.
TO_REGROLE: returns a regrole equivalent for known users; NULL otherwise. SELECT TO_REGCLASS('sensor') → 'sensor'
SELECT TO_REGCLASS('nonsuch') → NULL
SELECT TO_REGTYPE('bigint') → 'bigint'
SELECT TO_REGTYPE('text') → 'text'
SELECT TO_REGROLE(CURRENT_USER) → connected user
SELECT TO_REGROLE('nonsuch') → NULL

PG_FUNCTION_IS_VISIBLE / PG_TYPE_IS_VISIBLE PG_FUNCTION_IS_VISIBLE(oid)
PG_TYPE_IS_VISIBLE(oid)

PG search-path visibility probes. Both always return TRUE: GS SQL has no search_path-scoped visibility, so every recognized function and type is visible in the bare-name namespace. SELECT PG_FUNCTION_IS_VISIBLE(0) → true
SELECT PG_TYPE_IS_VISIBLE(0) → true

OBJ_DESCRIPTION / COL_DESCRIPTION / SHOBJ_DESCRIPTION OBJ_DESCRIPTION(oid [, catalog])
COL_DESCRIPTION(oid, column)
SHOBJ_DESCRIPTION(oid, catalog)

Returns the comment text from pg_catalog.pg_description. STRING / NULL on unknown OID.

OBJ_DESCRIPTION: object comment.
COL_DESCRIPTION: column comment for an object/column pair.
SHOBJ_DESCRIPTION: shared-object comment. Always NULL today — GS has no shared-object comments to expose. SELECT OBJ_DESCRIPTION(0) → NULL
SELECT COL_DESCRIPTION(0, 1) → NULL
SELECT SHOBJ_DESCRIPTION(0, 'pg_database') → NULL

Catalog typmod / precision columns

PG-introspection columns that BI tools read to learn a column's declared precision now emit real values for the types where GS tracks precision — instead of the placeholder -1 they previously returned.

Affected columns:

pg_catalog.pg_attribute.atttypmod
information_schema.columns.character_maximum_length
information_schema.columns.datetime_precision
information_schema.columns.interval_precision

Per-type behavior:

VARCHAR(n): character_maximum_length = n; atttypmod = n + 4 (PG encoding).
TIMESTAMP / TIMESTAMPTZ / TIME: datetime_precision emits the declared fractional-seconds precision; atttypmod encodes the same value.
INTERVAL: interval_precision emits the declared fractional-seconds precision.
NUMERIC(p, s): atttypmod still emits -1 (per-stream precision/scale fields not yet implemented). This is the one PG-divergence left in the typmod surface.
All other types continue to emit -1 for atttypmod and NULL for the information_schema precision columns.

SELECT atttypmod FROM pg_catalog.pg_attribute WHERE attname = 'name' → 54 -- VARCHAR(50): n + 4
SELECT character_maximum_length FROM information_schema.columns WHERE column_name = 'name' → 50
SELECT datetime_precision FROM information_schema.columns WHERE column_name = 'event_ts' → 6 -- TIMESTAMP

VERSION VERSION()

Returns the GS SQL version banner as STRING. The exact string matches the PostgreSQL-compatibility advertisement used by drivers and BI tools at connect time. SELECT VERSION() → 'PostgreSQL 14.0 (GroveStreams)' (illustrative)

PG_CLIENT_ENCODING PG_CLIENT_ENCODING()

Returns the current client encoding as STRING. Always 'UTF8'. SELECT PG_CLIENT_ENCODING() → 'UTF8'

Table Functions

JSON_TABLE

JSON_TABLE( jsonSource { sourceType, jsonPath } ) [ <with_clause> ]
<with_clause> ::= WITH ( { colName type [ column_json_path ] } [ ,...n ] )

JSON_TABLE is a table-valued function that parses JSON text and returns objects and properties from the jsonSource as rows and columns. In other words, JSON_TABLE provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns. Since JSON_TABLE returns a set of rows, you can use JSON_TABLE in the FROM clause of a GS SQL statement.

jsonSource: The JSON source can be either a string or a URL pointing to a component stream file that contains JSON. This JSON can be either an object or an array. URLs will be URL Decoded by the SQL engine. URL Examples:

http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/{epochMillis time}/{fileName}?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/generator1/stream/amperes/feed/file/1735922429463/jsonTester.txt?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/{fileName} <-- Returns the latest file for the stream in the current organization

sourceType: Optional. Default is STRING. Options are STRING or URL. Only URLs to component file streams within the organization are allowed (at least for today)
jsonPath: Optional. A valid JSON path string. jsonSource can contain nested sub-objects. If you want to parse a sub-object from within jsonSource, you can specify a jsonPath parameter for the JSON sub-object.

By default, the JSON_TABLE table-valued function returns a {key:value} pair found in jsonSource as three columns: the key name as STRING, the value as STRING, and the type as LONG. As an alternative, you can explicitly specify the schema of the result set that JSON_TABLE returns by providing with_clause.

with_clause: Optional. with_clause contains a list of columns with their types for JSON_TABLE to return. By default, JSON_TABLE matches keys in jsonSource with the column names in with_clause (in this case, matches keys implies that it is case sensitive). If a column name does not match a key name, you can provide an optional column_json_path, which is a JSON Path string that references a key within the jsonSource.

with_clause colName types (case insensitive):

STRING
BOOLEAN
LONG
DOUBLE
BIGDECIMAL

JSON_TABLE responds four different ways depending on whether a JSON object or a JSON array is passed in and whether a with_clause is included.

Example 1: JSON Object Without with_clause:

One row for each property. key as STRING, value as STRING and type as LONG.

SELECT key,
       value,
       type
FROM JSON_TABLE('
{
  "name": "Bob",
  "age": 32,
  "height": 1.82,
  "married": true,
  "city": null,
  "cars": ["Ford","Tesla"],
  "job":{"name":"IT","Start Date":1735403955000}
}
')

Results:

key	value	type
name	Bob	1
age	32	2
height	1.82	2
married	true	3
city	null	0
cars	["Ford","Tesla"]	4
job	{"name":"IT","Start Date":1735403955000}	5

Example 2: JSON Object With with_clause:

One row. Each property is a column.

SELECT name,
       age,
       married,
       city
FROM JSON_TABLE('
{
  "name": "Bob",
  "age": 32,
  "height": 1.82,
  "married": true,
  "city": null,
  "cars": ["Ford","Tesla"],
  "job":{"name":"IT","Start Date":1735403955000}
}
') WITH (name    STRING,
         age     LONG,
         married BOOLEAN,
         city    STRING)

Results:

name	age	married	city
Bob	32	true	null

Example 3: JSON Array Without with_clause:

One row. Each property is a column. key as STRING, value as STRING and type as LONG.

Results:

key	value	type
0	Ford	1
1	Tesla	1

Example 4: JSON Array With with_clause:

Each array item is a row. Each property is a column.

SELECT name,
       age
FROM JSON_TABLE('[
   {"name":"Bob","age":1},{"name":"Joe","age":22},{"name":"Tim","age":15},
   {"name":"Tom","age":24},{"name":"Carl","age":56},{"name":"Igor","age":42}]' )
WITH (name STRING ,
      age LONG) AS workers

Results:

name	age
Bob	1
Joe	22
Tim	15
Tom	24
Carl	56
Igor	42

Example 5: JSON Object With with_clause:

Combining nested objects while using column_json_path.

SELECT name,
       age,
       car2,
       jobName
FROM JSON_TABLE('
{
  "name": "Bob",
  "age": 32,
  "height": 1.82,
  "married": true,
  "city": null,
  "cars": ["Ford","Tesla"],
  "job":{"name":"IT","Start Date":1735403955000}
}
') WITH (name     STRING '$.name',
         age      LONG   '$.age',
         car2     STRING '$.cars[1]',
         jobName  STRING   '$.job.name' )

Results:

name	age	car2	jobName
Bob	32	Tesla	IT

Example 6: jsonSource as a file stream file:

One row. Each property is a column. Source is a GroveStreams organization file stream file. The file contains the JSON from Example 2.

SELECT name,
       age,
       married,
       city
FROM JSON_TABLE('
https://grovestreams.com/api/comp/fcomp/stream/jsonfile/feed/file/1735922429463/jsonTester.txt
',
   SOURCETYPE = URL
) WITH (name    STRING,
         age     LONG,
         married BOOLEAN,
         city    STRING)

Results:

name	age	married	city
Bob	32	true	null

CSV_TABLE

CSV_TABLE( csvSource, { 'firstrow', 'separator', 'quotechar', 'escapechar', 'strictquotes', 'ignoreleadingwhitespace, maxerrors' }
<with_clause> ::= WITH ( { colNum colName type } [ ,...n ] )

CSV_TABLE is a table-valued function that parses comma-separated values (CSV) text, returning the data from csvSource as rows and columns. Essentially, CSV_TABLE offers a rowset view of a CSV document. You can explicitly define which columns to include in the rowset and how to populate them. Since CSV_TABLE returns a set of rows, it can be used in the FROM clause of a GS SQL statement.

CSV_TABLE parses multiple lines, where each line ends with "\n".

csvSource: The CSV expression string, or a URL of a file stream within the organization, is parsed into columns and rows. URLs will be URL-decoded by the SQL engine. URL examples:

http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/{epochMillis time}/{fileName}?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/generator1/stream/amperes/feed/file/1735922429463/csvTester.txt?org=00000000-0000-0000-0000-000000000001
http://grovestreams.com/api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/1735922429463/{fileName}
api/comp/{compId}/stream/{streamId}/feed/file/{fileName} <-- Returns the latest file for the stream in the current organization

sourceType: Optional. Default is STRING. Options are STRING or URL. Only URLs to component file streams within the organization are allowed (at least for today)
firstrow: Optional. Default is 1. The row to start parsing from. 1 is the first row. Allows header rows to be ignored.
separator: Optional. Default is ',' (comma). The delimiter to use for separating entries.
quotechar: Optional. Default is '"' (double quote). The character to use for quoted elements).
escapechar: Optional. Default is '\' (backslash). The character to use for escaping a separator or quote.
strictquotes: Optional. Default is FALSE. When TRUE, characters outside quotes are ignored.
ignoreleadingwhitespace: Optional. Default is TRUE. If TRUE, leading whitespace before a quoted field is ignored.
maxerrors: Optional. Default is 0. Specifies the maximum number of syntax errors or non-conforming parsed rows which can occur before CSV_TABLE throws an exception. Each non-conforming parsed row segment exception is included in the count for calls without WITH statements.

By default, the CSV_TABLE table-valued function returns three columns: row, column, and value (types LONG, LONG, STRING, respectively). Alternatively, you can explicitly define the result set's schema by using the with_clause.

with_clause: Optional. The with_clause specifies a list of column numbers, names, and value types that CSV_TABLE will return. CSV_TABLE matches columns in csvSource with the colNum from with_clause (where the first column is 1). Values are converted from strings to the specified type.

with_clause column types (case insensitive):

STRING
BOOLEAN
LONG
DOUBLE
BIGDECIMAL

CSV_TABLE behaves in two distinct ways depending on the presence of a with_clause:

Example 1: Without with_clause:

One row for each line's delimited values in the CSV string. row and column are LONG type. value is STRING type. New lines (\n) exist at the end of each csvSource line. They're not visible here.

SELECT row,
column,
value
FROM CSV_TABLE(
'"2021-04-01T05:00:00.000",24
"2021-04-01T06:00:00.000",23
"2021-04-01T07:00:00.000",22')

Results:

row	column	value
1	1	2021-04-01T05:00:00.000
1	2	24
2	1	2021-04-01T06:00:00.000
2	2	23
3	1	2021-04-01T07:00:00.000
3	2	22

Example 2: With with_clause:

All possible options are being used. Four header lines are ignored with 'FIRSTROW' set to five. New lines (\n) exist at the end of each csvSource line. They're not visible here.

SELECT time,
       pressure
FROM CSV_TABLE(
'|"Component 1"
|"component1"
|"Pressure"
|"pressure"
xx"2021-04-01T05:00:00.000"yy|"24"
"2021-04-01T06:00:00.000" |  "23"
"2021-04-01T07:00:00.000" | "22"',
   SOURCETYPE = STRING,
   FIRSTROW = 5,
   QUOTECHAR = '"',
   SEPARATOR = '\|',
   ESCAPECHAR = '\\',
   STRICTQUOTES = TRUE,
   IGNORELEADINGWHITESPACE = TRUE,
   MAXERRORS = 1
) WITH (
   1 time STRING,
   2 pressure LONG)
WHERE (
  pressure > 22)

Results:

time	pressure
2021-04-01T05:00:00.000	24
2021-04-01T06:00:00.000	23

Example 3: csvSource is a a file stream

Same data as Example 1. URL is shortened and returns the latest file for the stream.

SELECT row,
       column,
       value
FROM CSV_TABLE(
'api/comp/fcomp/stream/csvfile/feed/file/csvTester.csv',
   SOURCETYPE = URL
)

Results:

row	column	value
1	1	2021-04-01T05:00:00.000
1	2	24
2	1	2021-04-01T06:00:00.000
2	2	23
3	1	2021-04-01T07:00:00.000
3	2	22

EXTERNAL_QUERY

EXTERNAL_QUERY( connectionId, remoteSql )

EXTERNAL_QUERY is a table-valued function that executes SQL on a remote JDBC database and returns the result as a table. The remote SQL is written in the remote database's native dialect (MySQL, PostgreSQL, SQL Server, Oracle, etc.), not GS SQL.

Since EXTERNAL_QUERY returns a set of rows, it can be used in the FROM clause of a GS SQL statement, including JOINs with other GS tables, views, template tables, JSON_TABLE, CSV_TABLE, or other EXTERNAL_QUERY calls.

connectionId: A string literal specifying the ID of a JDBC connection defined in Connectors. This is the user-facing ID (e.g., 'mysql_employees'), not the internal UID.
remoteSql: A string literal containing the SQL to execute on the remote database. This SQL is passed directly to the remote database — use the remote database's syntax, functions, and table names.

Cross-database queries: The connection's Default Database sets the unqualified table context. To query tables in other databases on the same server, use qualified names in your remote SQL (e.g., 'other_db.table_name' for MySQL, 'other_db.dbo.table_name' for SQL Server).

Column types: Column names and types are automatically discovered from the remote database's result set metadata. JDBC types are mapped to GS SQL types: VARCHAR/CHAR → STRING, INTEGER/BIGINT → LONG, DOUBLE/FLOAT → DOUBLE, DECIMAL/NUMERIC → BIGDECIMAL, BOOLEAN → BOOLEAN, TIMESTAMP/DATE/TIME → LONG (epoch milliseconds).

Security: The user must have Tools rights on the JDBC connector to execute EXTERNAL_QUERY against it.

Example 1: Simple query

SELECT *
FROM EXTERNAL_QUERY('mysql_employees', 'SELECT dept_no, dept_name FROM departments') d

Example 2: Aggregation on remote side

SELECT d.dept_name, d.cnt
FROM EXTERNAL_QUERY('mysql_employees',
'SELECT dept_name, count(*) as cnt FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no GROUP BY dept_name') d

Example 3: Join remote data with local GS template table

SELECT eq.emp_name, s._component_name, s.temperature
FROM EXTERNAL_QUERY('mysql_conn', 'SELECT emp_name, sensor_id FROM employees WHERE dept = 5') eq
JOIN sensor s ON s._component_id = eq.sensor_id

Example 4: Cross-database query

SELECT *
FROM EXTERNAL_QUERY('mysql_conn', 'SELECT * FROM sales_db.orders WHERE order_date > ''2025-01-01''') o

Literals - Constants

NOW The current datetime in Long epoch milliseconds. The NOW literal is set at the start of the SQL process and used by all steps of the query so that the NOW time is the same for all operations.

Both NOW (no parentheses) and NOW() (with parentheses) are accepted for standard SQL compatibility. SELECT NOW → 1618885675191
SELECT NOW() → 1618885675191
SELECT FormatDate(NOW) → '2021-04-19T21:28:37'

CURRENT_TIMESTAMP Same as NOW. See above. SELECT CURRENT_TIMESTAMP → 1618885675191
SELECT FormatDate(CURRENT_TIMESTAMP) → '2021-04-19T21:28:37'

CURRENT_DATE Returns Now formatted as a string using the format "yyyy-MM-dd". SELECT CURRENT_DATE → '2021-04-19'

CURRENT_TIME Returns Now formatted as a string using the format "HH:mm:ss". SELECT CURRENT_TIME → '21:28:37'

INTERVAL literal

INTERVAL 'string' [field_qualifier]

An INTERVAL literal is the keyword INTERVAL followed by a single-quoted duration string and an optional ANSI field qualifier. The string is parsed by the GS interval parser, which accepts every PostgreSQL-supported format (PG-verbose, ISO-8601, HH:MM:SS, signed numbers, etc.) regardless of the qualifier — the qualifier is hint-only and does not gate, truncate, or reshape the parse. It is accepted for PG / standard-SQL compatibility.

Supported field qualifiers (parse-and-swallow):

Single fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
Field ranges: YEAR TO MONTH, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, MINUTE TO SECOND

Edge cases:

INTERVAL '1-2' YEAR TO MONTH and INTERVAL '1 year 2 months' produce the same value.
INTERVAL '1 02:03:04' DAY TO SECOND and INTERVAL '1 day 2 hours 3 minutes 4 seconds' produce the same value.
A mismatched qualifier (e.g. INTERVAL '1 year' DAY) does not raise — the qualifier is ignored and the string is parsed as written.
Only one trailing qualifier is allowed. Other PG forms (precision after SECOND, e.g. SECOND(3)) are not yet supported.

SELECT INTERVAL '1-2' YEAR TO MONTH;
SELECT INTERVAL '1 02:03:04' DAY TO SECOND;
SELECT INTERVAL '15 minutes' MINUTE;
SELECT INTERVAL '1 year 2 months 3 days'; -- no qualifier, parses identically

TEQ™ Syntax

Temporal Entity Query (TEQ) allows you to use component template IDs as table names and stream IDs as column names. This is the recommended query style for organizations that use component templates. See the TEQ Guide for full documentation and examples.

Template Table: Use a component template ID in the FROM clause. The engine matches the name (case-insensitive) against your organization's templates.

SELECT _component_name, temperature FROM sensor	Template ID as table name; stream IDs as columns
SELECT s._component_name, s.temperature FROM sensor s	With table alias

Per-Column Temporal Parameters: In TEQ, temporal parameters are applied to individual stream columns by name, rather than to the _sample column.

SELECT _component_name, kwh(range(last=100)) FROM meter	Per-column temporal parameters
SELECT _component_name, kwh(range(sd=-1d)), voltage FROM meter	Mixed temporal and _last_value columns
SELECT _component_name, kwh(cycleId='day', stat='avg') FROM meter	Cycle aggregation on a stream column
SELECT _component_name, kwh(lag=1, range(last=100)) FROM meter	Previous value (window parameter)
SELECT _component_name, kwh(running='sum', range(currentCycle='month')) FROM meter	Running total (window parameter)

Component System Columns: Always available in template table queries: _component_name (String), _component_uid (String), _component_id (String), _component_created_date (Long), _folder_path (String).

FK JOINs: Join template tables using foreign key streams.

SELECT m._component_name, c._component_name FROM meter m JOIN customer c ON m.customerUid = c._component_uid

FK JOIN between template tables

Temporal Parameters

The Sample result column, within a Select statement, allows for optional parameters to filter and aggregate the list of samples returned. Each parameter may contain other parameters such as the Range () parameter.

Sample Range Parameter:

Example: Select _Sample( Range(sd=-1d, ed=now) ) From system.stream

The range parameter is used to filter samples by time. A Range has a start datetime (inclusive) and end datetime (exclusive) and is represented by sd and ed parameters.

There are four ways to specify a Range:

1. No range specified: The stream's first and last sample's datetime are used. All samples will be returned.
2. sd and ed parameters are used
3. currentCycle or previousCycle are used
4. last is used and returns the last number of samples from ed (exclusive)

sd and ed can be set by using the following:

ISO8601 formatted string → sd='2021-03-01T00:00:00-05:00
Epoch Millis Long number → sd=1617253200000
now: The current time → ed=now
streamSd: The stream's earliest sample's datetime → sd=streamSd
streamEd: The stream's latest sample's datetime → ed=streamEd
currentCycle: The ID of a cycle used. The current datetime will be used to determine the cycle boundaries for sd and ed → currentCycle='day'
previousCycle: The ID of a cycle to be used. The current datetime will be used to determine the current cycle boundaries and then the previous cycle will be used for sd and ed → previousCycle='day'
if sd is missing, it is assigned to streamSd
if ed is missing, it is assigned to streamEd
if sd is used with a modifier, the modifier is applied to the ed's datetime.

Case-sensitive datetime Modifiers can be used to add or subtract time spans while setting the sd or ed parameters:

s: Second
m: Minute
h: Hour
d: Day
w: Week
M: Month
Y: Year

Range Examples:

SELECT Sample FROM system.stream	Return all samples
SELECT _Sample( Range(sd='2021-03-01T00:00:00-05:00', ed='2021-04-01T00:00:00-05:00') ) FROM system.stream	Return fixed range using ISO8601
SELECT _Sample( Range(sd=1614578400000, ed=1617253200000) ) FROM system.stream	Return fixed range using epoch millis
SELECT _Sample( Range(sd=-1d, ed=now) ) FROM system.stream	Return last day from current datetime
SELECT _Sample( Range(sd=now, ed=now+1y) ) FROM system.stream	Return from now to one year into the future
SELECT _Sample( Range(sd=-1d, ed=streamEd) ) FROM system.stream	Return last day from stream's last sample datetime
SELECT _Sample( Range(sd=-1hr ed=streamEd-1hr) ) FROM system.stream	Return last hour from stream's last sample datetime
SELECT _Sample( Range(currentCycle='month') ) FROM system.stream	Return current month
SELECT _Sample( Range(previousCycle='month') ) FROM system.stream	Return previous month
SELECT _Sample( Range(last=100, ed=streamEd) ) FROM system.stream	Return the last 100 samples
SELECT _Sample( Range(last=100, ed=now-1w) ) FROM system.stream	Return the last 100 samples from one week ago

Sample TimeFilterId Parameter:

Example: Select _Sample( TimeFilterId='weekdayOnly' ) From system.stream

The TimeFilterId property will apply the GroveStreams Time Filter, with the assigned ID, used to filter samples based on the Time Filter definition and sample times.

Time filtering will be applied to intervals after they are rolled-up (aggregated) when the CycleId parameter is present.

TimeFilter Examples:

SELECT _Sample(TimeFilterId='weekends') FROM system.stream	Return all samples with Saturday or Sunday times.
SELECT _Sample(TimeFilterId='weekends', Range(sd=-4w)) FROM system.stream	TimeFilterId with a Range. Return all samples with Saturday or Sunday times over the last 4 weeks.

Sample CycleId and Stat Parameters:

Example: Select _Sample( cycleId='day', stat='max' ) as maximum, FormatDate(_Sample( cycleId='day', stat='MAXOCCURRENCE' )) as 'maximum time' From system.stream

The CycleId is the ID of a GroveStreams cycle defined in your Organization. Samples returned will be aggregated into Intervals to match the cycle using the default rollup method in the stream definition or by using the statistic passed in.

Most of the statistics below are calculated for each cycle in a stream's rollup calendar as samples arrive an are saved. The timezone of the cycle or the component is used to determine each cycle range. The query engine will identify if the requested stat has been precalculated and return that result quickly. The query engine will calculate cycle statistics if they have not already been calculated and saved. Configure stream rollup calendars to improve the retrieval of interval statistics.

Stat options (case insensitive):

FIRST
LAST
MIN
MAX
AVG
SUM
MINOCCURRENCE - Epoch Millis datetime of the minimum
MAXOCCURRENCE - Epoch Millis datetime of the maximum
GAPCOUNT
NONGAPCOUNT
INTVLCOUNT
MILLISECCOUNT
NONGAPMILLISECCOUNT
STDDEVP
STDDEVA
SAMPLECOUNT
SUMSIN
SUMCOS

CycleId and Stat Examples:

SELECT _Sample(cycleId='month') FROM system.stream	Return all samples aggregated into month intervals using the default stream rollup method
SELECT _Sample(cycleId='month', stat='sum') FROM system.stream	Return all samples aggregated into month intervals. Samples are aggregated using SUM.
SELECT _Sample(cycleId='month', stat='sum', Range(currentCycle='year')) FROM system.stream	Return 12 month intervals for the current year. Samples are aggregated into each month using SUM.
Select _Sample( cycleId='day', stat='max' ) as maximum, FormatDate(_Sample( cycleId='day', stat='MAXOCCURRENCE' )) as 'maximum time' From system.stream	Return 12 month intervals each with the maximum sample value for each month and the datetime it occurred.

Sample GapFill Parameter:

Example: Select _Sample( cycleId='day', GapFill(head=0 , middle='avg', tail='previousval'), Range(currentCycle='year') ) from system.stream Where _component_id='mainSubstation' and _id='kw'

The GapFill parameter allows gaps, NULL values, to be replaced for interval streams. GapFilling takes place after all other temporal parameter logic has been applied and prior to other SQL logic such as WHERE filtering and aggregation functions. GapFilling respects the TimeFilter parameter when it is included. TimeFiltered intervals will remain as gaps (NULLS) during gap filling.

Gap filling will take place after intervals are rolled-up (aggregated) when the CycleId parameter is present.

GapFilling will only use values within the time range selected for calculating gap replacement values. For example, it will not attempt to find a 'previousVal' from the store prior to the time range being processed by the SQL statement.

GapFill options (case insensitive):

HEAD : How to fill gaps at the start of the request time range :

NEXTVAL: Use the first non-gap value encountered after gap sequence. Must be in single quotes: head='NEXTVAL'
Value: A value to assign head gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)

MIDDLE : How to fill gaps at the middle of the request time range:

PREVIOUSVAL: Use the non-gap value encountered prior to the gap sequence. Must be in single quotes: head='PREVIOUSVAL'
NEXTVAL: Use the first non-gap value encountered after gap sequence. Must be in single quotes: head='NEXTVAL'
MIN: Fill with minimum of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='MIN'
MAX: Fill with maximum of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='MAX'
AVG: Fill with average of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='AVG'
SPLINE: Fill with Apache's SplineInterpolator for cubic spline creation. Interpolates missing values within the valid time range of PREVIOUSVAL and NEXTVAL. Needs at least 4 valid data points for interpolation. Must be in single quotes: middle='SPLINE'
Value: A value to assign middle gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)

TAIL : How to fill gaps at the end of the request time range:

PREVIOUSVAL: Use the non-gap value encountered prior to the gap sequence. Must be in single quotes: tail='PREVIOUSVAL'
Value: A value to assign tail gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)

Process order is HEAD, TAIL, MIDDLE If all three options are used. Use head=value to fill an entire result set with a value if a requested result is all gaps.

GapFill Examples:

SELECT _Sample( GapFill(middle=0.0) ) FROM system.stream WHERE _id='xx'	Return all interval samples with gaps set to zero.
SELECT _Sample( GapFill(middle='off') ) FROM system.stream WHERE _id='xx'	Return all interval samples with gaps set to the string 'off'.
SELECT _Sample( cycleId='hour', stat='sum', timeFilterId='weekendAfternoon', GapFill(head='nextval', middle='avg', tail=0), Range(currentCycle='year') ) FROM system.stream WHERE _id='xx'	Return all hourly interval samples with weekend afternoon hour gaps filled for the current year.

Sample Window Parameters:

Example: Select _Sample( lag=1, Range(last=100) ) From system.stream Where _component_id='mainSubstation' and _id='kw'

Window parameters perform positional and sliding-window operations on sample data, providing the same capabilities as standard SQL window functions (LAG(), LEAD(), sliding aggregates, running totals) but as temporal parameters. This approach is faster than SQL WINDOW/OVER() syntax because it operates directly on the sample data arrays before tuple emission — and where rollups are available, reads the pre-computed rollup tier instead of the raw samples.

Standard SQL OVER (…) is also available as of v3 (2026-05-28) — full PG grammar including ranking, offset, positional, statistical (STDDEV, VARIANCE), boolean aggregates, ROWS and value-based RANGE BETWEEN frames with arbitrary-expression offsets, named WINDOW clauses, EXCLUDE clauses, and aggregate FILTER (WHERE). Pick by intent: use temporal parameters when you want rollup-backed speed and time-series-aware semantics; use OVER (…) when you want raw-row PG-compatible behavior (BI tools).

Window parameters are applied after GapFill (if present) and before WHERE filtering and aggregation functions.

Window Parameters (case insensitive):

Positional Parameters:

lag=N : Returns the sample value N positions before the current one, skipping time-filtered positions. N must be a positive integer. Out-of-bounds positions return NULL.
lead=N : Returns the sample value N positions after the current one, skipping time-filtered positions. N must be a positive integer. Out-of-bounds positions return NULL.
lagdefault=val : Optional default value when lag goes out of bounds (instead of NULL). Numbers, strings (in quotes), or booleans.
leaddefault=val : Optional default value when lead goes out of bounds (instead of NULL). Numbers, strings (in quotes), or booleans.

Aggregate Window Parameters:

slide=N : Sliding window of N positions. N must be an integer greater than 1. Must be used with slidestat. Computes the aggregate over a trailing window of N samples ending at the current position.
slidestat='fn' : Required when slide is used. The aggregate function for the sliding window. Supported values (in single quotes): 'sum', 'avg', 'min', 'max', 'first', 'last', 'count'.
running='fn' : Cumulative (running) aggregate from the first sample to each position. Supported values (in single quotes): 'sum', 'avg', 'min', 'max', 'first', 'last', 'count'.

Rules:

lag and lead are mutually exclusive — cannot be used on the same column.
slide and running are mutually exclusive — cannot be used on the same column.
Positional parameters (lag/lead) cannot be combined with aggregate parameters (slide/running) on the same column.
slide and running require numeric sample types (DOUBLE, LONG, INTEGER, etc.). They do not support STRING types.
lag and lead work with any sample type including STRING.

Time Filter Behavior:
Window parameters respect both the stream's reference time filter and the query-time TimeFilterId parameter. Samples that are excluded by either time filter are skipped by window functions:

lag and lead skip filtered positions when counting N. For example, lag=1 returns the previous non-filtered sample, not simply the adjacent position.
slide and running exclude filtered positions from their aggregate computations (sums, averages, etc.).
Filtered positions themselves produce NULL output.
When both a stream reference time filter and a TimeFilterId parameter are present, a sample must pass both filters to be included.

Window Parameter Examples:

SELECT _Sample( lag=1, Range(last=100) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Previous sample value (lag by 1). First position is NULL.
SELECT _Sample( lead=1, Range(last=100) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Next sample value (lead by 1). Last position is NULL.
SELECT _Sample( lag=1, lagdefault=0, Range(last=100) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Previous sample with default of 0 instead of NULL at the boundary.
SELECT _Sample( slide=3, slidestat='avg', Range(sd=-1d) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	3-sample sliding average.
SELECT _Sample( slide=5, slidestat='max', Range(sd=-1d) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	5-sample sliding maximum.
SELECT _Sample( running='sum', Range(currentCycle='month') ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Running (cumulative) sum from start to each position.
SELECT _Sample( running='avg', Range(currentCycle='month') ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Running average from start to each position.
SELECT _sample - _Sample( lag=1, Range(last=100) ) AS 'delta' FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Difference between current and previous sample (delta calculation).
SELECT _Sample( lag=1, timeFilterId='businessHours', Range(sd=-1d) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	Previous sample, skipping samples outside business hours. Filtered positions return NULL.
SELECT _Sample( slide=3, slidestat='avg', timeFilterId='weekdays', Range(sd=-1M) ) FROM system.stream WHERE _component_id='gen1' AND _id='kw'	3-sample sliding average over weekday samples only.

TEQ Examples: Window parameters work on TEQ stream columns too:

SELECT _component_name, kwh(lag=1, range(last=100)) FROM meter	Previous kWh value per component.
SELECT _component_name, temperature(slide=5, slidestat='avg', range(sd=-1d)) FROM sensor	5-sample sliding average temperature.
SELECT _component_name, kwh(running='sum', range(currentCycle='month')) FROM meter	Cumulative kWh total for the current month.