Utility functions ​

Utility functions inspect SQL values, format sizes and expose connection information. Some names on this page are table functions: use them in FROM when they produce several rows.

Connection counters refer to the observing SQL connection. Functions that inspect types distinguish the native type descriptor from the legacy runtime value class.

Example: Inspecting a row ​

SELECT num_nonnulls(1, NULL, ''), num_nulls(1, NULL, ''), pg_typeof('hello')
FORMAT csv;

2,1,"String"

num_nonnulls ​

Count non-NULL arguments.

Syntax ​

num_nonnulls(value,...)

Arguments ​

One or more arguments. Only the outer SQL NULL value is counted as missing.

Returned value ​

INTEGER number of non-NULL arguments.

Example ​

SELECT num_nonnulls(1,NULL,2) FORMAT csv;

2

num_nulls ​

Count SQL NULL arguments.

Syntax ​

num_nulls(value,...)

Arguments ​

One or more arguments. Native JSON null and collection members are ordinary non-NULL argument values.

Returned value ​

INTEGER number of NULL arguments.

Example ​

SELECT num_nulls(1,NULL,2) FORMAT csv;

1

pg_size_bytes ​

Parse a human-readable byte size.

Syntax ​

pg_size_bytes(text)

Arguments ​

At most 512 bytes. Decimal/scientific number plus optional case-insensitive bytes/B/kB/MB/GB/TB/PB. Units use powers of 1024. Round half away from zero; signed 64-bit overflow errors. SQL NULL propagates.

Returned value ​

INTEGER bytes.

Example ​

SELECT pg_size_bytes('1.5 MB') FORMAT csv;

1572864

pg_size_pretty ​

Format an exact byte count with binary size units.

Syntax ​

pg_size_pretty(bytes)

Arguments ​

Exact bounded numeric input. Uses PostgreSQL 10 KiB/half-unit thresholds and truncation before half rounding; native precision/scale limits apply. SQL NULL propagates.

Returned value ​

TEXT size with bytes/kB/MB/GB/TB/PB.

Example ​

SELECT pg_size_pretty(1048576) FORMAT csv;

"1024 kB"

pg_typeof ​

Describe the bound native type, including typed NULL expressions.

Syntax ​

pg_typeof(value)

Arguments ​

One argument. Bound column/CAST declarations take priority; otherwise infer the present native value. Returns CoordinatorDB type names as text, not PostgreSQL regtype OIDs.

Returned value ​

Canonical native declaration, or unknown for an untyped NULL.

Example ​

SELECT pg_typeof(CAST(NULL AS UInt8)) FORMAT csv;

"UInt8"

generate_series ​

Generate inclusive numeric or calendar values as a FROM table with one column.

Syntax ​

FROM generate_series(start, stop[, step[, timezone]])

Arguments ​

Numeric step defaults to 1. Timestamp bounds require a native interval or quantity-unit step. Zero step errors; NULL input and unreachable direction produce no rows. FROM-only; joins use table-function rules.

Returned value ​

One generated value per row in column generate_series, in series order.

Example ​

SELECT * FROM generate_series(1,3) FORMAT csv;

1
2
3

generate_subscripts ​

Return one-based indexes of a rectangular native array dimension.

Syntax ​

FROM generate_subscripts(array,dimension[,reverse])

Arguments ​

Positive requested dimension; absent/nonpositive dimension gives no rows; reverse Bool defaults false; ragged arrays error; NULL gives no rows.

Returned value ​

One generate_subscripts INTEGER column.

Example ​

SELECT * FROM generate_subscripts(array(10,20),1) FORMAT csv;

1
2

to_number ​

Parse a bounded numeric input mask without rounding through floating point.

Syntax ​

to_number(text,template)

Arguments ​

One to 76 digit positions; 9/0, D/., G/,, S/MI/PL/SG/PR, L/$, V, FM/B, TH and quoted literals. Locale-independent punctuation; templates may contain at most 4096 bytes.

Returned value ​

Exact native Decimal128/Decimal256 up to 76 digits; SQL NULL propagates.

Example ​

SELECT CAST(to_number('12,454.8-','99G999D9S') AS TEXT) FORMAT csv;

"-12454.8"

unnest ​

Expand nested native arrays into scalar rows in depth-first order.

Syntax ​

FROM unnest(array)

Arguments ​

One native Array; nested arrays flatten; NULL members emit NULL rows; NULL input and empty arrays emit no rows.

Returned value ​

One value per row in column unnest; FROM-only; multiple-array and SELECT-list forms are unsupported.

Example ​

SELECT * FROM unnest(array(1,NULL,2)) FORMAT csv;

1
\N
2

Version and connection counters ​

version() returns the CoordinatorDB server build version and is available before USE. sqlite_version() reports a compatibility version, not the CoordinatorDB build. changes() reports the most recent statement's change count, total_changes() the connection total, and last_insert_rowid() the latest inserted row identifier. Internal catalog and log maintenance preserves these counters.

SELECT version() FORMAT csv;

"0.1.0"

likelihood, likely and unlikely ​

These functions return their first argument unchanged. likelihood(value,probability) requires a floating-point SQL literal in the range 0 through 1; expressions, integer literals and unary signs are rejected. Parentheses around the literal are accepted. likely(value) and unlikely(value) take one argument.

SELECT likelihood(7,0.5),likely(7),unlikely(NULL) FORMAT csv;

7,7,\N

sqlite_source_id ​

sqlite_source_id() returns a fixed compatibility source identifier. It does not identify the current CoordinatorDB build. Use version() for the server version.

SELECT typeof(sqlite_source_id()),length(sqlite_source_id())>0 FORMAT csv;

"text",1