Utility Functions

General-purpose functions for data manipulation, control flow, and type handling. The examples are self-contained. Geographic operations require backend spatial support; coordinate systems and distance units depend on the database.

Parenthetical

Used for explicit grouping of expressions, primarily to control the order of operations. Does not change the value, type, or purpose of the enclosed expression.

Inputs:

Any

Output:

Same as input

Purpose:

property

Example:

select (10 + 2) * 3 as total;

Coalesce

Returns the first non-null value from its list of arguments. All arguments must be of compatible types. Returns NULL if all arguments are NULL.

Inputs:

Any (compatible types)...

Output:

Same as input types (common compatible type)

Purpose:

property

Example:

select coalesce(null, 'Ada', 'Valued Customer') as display_name;

Cast

Converts an expression to a target type. Write expression::type or cast(expression as type).

Inputs:

AnyDataType

Output:

Specified by second argument (DataType)

Purpose:

property

Example:

select '123'::int as order_id_num; select cast('123' as int) as order_id_num;

IS_NULL

Use expression is null to test for a missing value. IS_NULL is the operation name, not a callable function.

Inputs:

Any

Output:

BOOL

Purpose:

property

Example:

select null is null as is_missing;

BOOL

Converts a value to boolean. Numeric 0 is false and 1 is true. Accepted string representations and invalid-value errors depend on the backend; arbitrary non-empty strings are not necessarily true.

Inputs:

Any

Output:

BOOL

Purpose:

property

Example:

select bool(0) as zero_is_false, bool(1) as one_is_true;

CASE

Evaluates a series of conditions and returns a result based on the first condition that evaluates to true. Syntax: `CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE default_result] END`. All result expressions must have compatible types.

Inputs:

BOOLAny...

Output:

Common compatible type of result expressions

Purpose:

property

Example:

auto score <- unnest([75, 85, 95]); select score, case when score > 90 then 'A' when score > 80 then 'B' else 'C' end as grade;

NULLIF

Returns NULL if the two arguments are equal, otherwise it returns the first argument.

Inputs:

AnyAny

Output:

Same as first input

Purpose:

property

Example:

select nullif('', '') as description_or_null;

HASH

Computes a string hash using an unquoted algorithm keyword: md5, sha1, sha256, or sha512. Algorithm availability depends on the backend.

Inputs:

STRINGmd5/sha1/sha256/sha512

Output:

STRING

Purpose:

property

Example:

select hash('ada@example.com', sha256) as hashed_email;

GEO_POINT

Creates a geographical point from longitude (x) followed by latitude (y).

Inputs:

NUMERICNUMERIC

Output:

GEOGRAPHY

Purpose:

property

Example:

select geo_point(-71.06::numeric, 42.36::numeric) as location;

Run locally with a spatial-enabled database; spatial extensions are not enabled in the shared demo.

GEO_DISTANCE

Calculates the distance between two geographical points. Units depend on the backend and coordinate system; do not assume the result is in meters.

Inputs:

GEOGRAPHYGEOGRAPHY

Output:

NUMERIC

Purpose:

property

Example:

select geo_distance(geo_point(-71.06::numeric, 42.36::numeric), geo_point(-73.99::numeric, 40.73::numeric)) as distance;

Run locally with a spatial-enabled database; spatial extensions are not enabled in the shared demo.