Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Value Expressions

A value expression computes a value: of a column, a literal, an operator, a function or a subquery. The examples here are of a table STAFF:

CREATE TABLE STAFF (ID INTEGER PRIMARY KEY, NAME VARCHAR(30), DEPT CHAR(3), SALARY DECIMAL(9,2));
INSERT INTO STAFF VALUES (1, 'Ada', 'ENG', 7200), (2, 'Grace', 'ENG', 8100),
                         (3, 'Edsger', 'OPS', 5400), (4, 'Barbara', NULL, NULL);
OK
OK

Value specifications

The simplest values are:

  • a literal, 42, 'Ada' or TRUE (Lexical Elements);
  • a column, NAME or S.NAME (Names);
  • USER, the same as CURRENT_USER, the session's authorization identifier; CURRENT_ROLE, the role the session has set (see SET ROLE), null if none; SESSION_USER and SYSTEM_USER, which are the same here, the session's user. In a trigger or a routine of a schema a role owns, CURRENT_USER is null and CURRENT_ROLE the role, while SESSION_USER stays the session's;
  • CURRENT_DATE, CURRENT_TIME(p) and CURRENT_TIMESTAMP(p), the instant the statement began, the last two WITH TIME ZONE, in the session's;
  • in a program, a parameter, or a ? of a prepared statement;
  • a scalar subquery: a query of one column, in parentheses, its value that of its one row, or null if it has none. A second row is refused.
VALUES (USER, CURRENT_USER, SESSION_USER, SYSTEM_USER);
SELECT NAME FROM STAFF WHERE SALARY = (SELECT MAX(SALARY) FROM STAFF);
SELECT NAME FROM STAFF WHERE ID = (SELECT ID FROM STAFF);
     |     |     |
-----+-----+-----+-----
 ANN | ANN | ANN | ANN
(1 row)
 NAME
--------------------------------
 Grace
(1 row, with a warning: SQLSTATE 01003)
turborel: SQLCODE -811 (SQLSTATE 21000): a <scalar subquery> or <row subquery> gives more than one row [ISO/IEC 9075:1992 §7.11 GR1]

The null value is no value: a column of no value, a set function of no rows. An operator or function of a null is null. NULL is not a value expression of its own: it is written only where its type is known, as a value inserted, a SET, a DEFAULT, or CAST(NULL AS INTEGER).

Numeric expressions

+, -, * and / on numbers, and a sign, -x. * and / bind tighter than + and -; parentheses group. An expression of exact numbers is exact, of 18 digits at most; one of an approximate number is approximate; one of a DECFLOAT is DECFLOAT(34), rounded to 34 digits. A quotient of exact numbers is at the largest scale that fits, so 10 / 4 is 2.5; a product's scale is its operands' scales summed. Division by zero is refused, and so is a result past 18 digits.

VALUES (1 + 2 * 3, -(4 - 6), 10 / 4, 2.50 * 2, 1E0 / 3);
VALUES (1 / 0);
SELECT ID, SALARY * 1.1 FROM STAFF ORDER BY ID;
                      |                      |                      |                      |
----------------------+----------------------+----------------------+----------------------+--------------------------
                    7 |                    2 |                  2.5 |                 5.00 |     3.333333333333333E-1
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22012): a divisor shall not be 0 [X3.135-1986 §5.9 GR5]
           ID |
--------------+----------------------
            1 |             7920.000
            2 |             8910.000
            3 |             5940.000
            4 |                 NULL
(4 rows)

The numeric functions:

Function Value
CHAR_LENGTH(s), CHARACTER_LENGTH(s) the characters of a character string
OCTET_LENGTH(s) the octets of a string, as UTF-8, or of a binary string
BIT_LENGTH(s) (extension) eight bits to each of those octets
POSITION(a IN b) where string a first begins in b, from 1; 0 if not in it, 1 if a is empty; of binary strings, in octets
EXTRACT(f FROM d) the field f of a datetime or interval
WIDTH_BUCKET(x, b1, b2, n) which of n buckets of equal width from b1 to b2 x falls in
LN(x), EXP(x) the natural logarithm of x, and e raised to the power x
POWER(x, y), SQRT(x) x raised to the power y, and the square root of x, POWER(x, 0.5)
FLOOR(x) the greatest whole number not above x
CEILING(x), CEIL(x) the least whole number not below x

WIDTH_BUCKET cuts the range from b1 to b2 into n buckets of equal width and gives the bucket x falls in, from 1. A value before b1 is in bucket 0, and one at b2 or past it in bucket n + 1. Each bucket holds its lower edge, so with b1 greater than b2 the buckets count down, and b2 is in bucket n + 1. The result is null if any operand is. Each operand is a number, n is exact of scale 0, and the result is of n's type. An n of 0 or below, or equal bounds, are refused (SQLSTATE 2201G), as is an n + 1 past n's type (22003). Of exact operands the bucket is found exactly.

SELECT NAME, SALARY, WIDTH_BUCKET(SALARY, 5000, 9000, 4) AS UP,
       WIDTH_BUCKET(SALARY, 9000, 5000, 4) AS DOWN
  FROM STAFF ORDER BY NAME;
VALUES (WIDTH_BUCKET(1, 0, 0, 4));
 NAME                           |      SALARY |  UP | DOWN
--------------------------------+-------------+-----+------
 Ada                            |     7200.00 |   3 |    2
 Barbara                        |        NULL | NULL | NULL
 Edsger                         |     5400.00 |   1 |    4
 Grace                          |     8100.00 |   4 |    1
(4 rows)
turborel: SQLCODE -301 (SQLSTATE 2201G): the width bucket bounds are equal [ISO/IEC 9075-2:2023 §6.31 GR17c]

LN, EXP, POWER and SQRT are DOUBLE PRECISION, or DECFLOAT(34) if an operand is DECFLOAT. FLOOR and CEILING of an exact number are exact of scale 0, found exactly; of an approximate number or a DECFLOAT they are of its type. Each is null if an operand is. POWER of 0 to the power 0 is 1, and of a negative number to a whole power is negative only of an odd power. Of a DECFLOAT, SQRT and a whole power are found to 34 digits, and LN, EXP and other powers to the 16 digits of double precision.

LN of 0 or below is refused (SQLSTATE 2201E, SQLCODE -301). So is POWER of 0 to a negative power, or of a negative number to a power not whole, and so SQRT of a negative number (2201F, -301). A result past the range of its type is refused too (22003, -301).

VALUES (LN(1), EXP(1), POWER(2, 10), POWER(-2, 3), SQRT(2));
VALUES (FLOOR(-2.5), CEILING(-2.5), FLOOR(2.5E0), CEIL(CAST(0.001 AS DECFLOAT)), SQRT(CAST(2 AS DECFLOAT)));
VALUES (LN(0));
VALUES (SQRT(-1));
                          |                          |                          |                          |
--------------------------+--------------------------+--------------------------+--------------------------+--------------------------
                      0E0 |      2.718281828459045E0 |                  1.024E3 |                     -8E0 |     1.4142135623730951E0
(1 row)
      |      |                          |                                             |
------+------+--------------------------+---------------------------------------------+---------------------------------------------
   -3 |   -2 |                      2E0 |                                           1 |         1.414213562373095048801688724209698
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2201E): the natural logarithm is of a number above 0 [ISO/IEC 9075-2:2023 §6.31 GR12b]
turborel: SQLCODE -301 (SQLSTATE 2201F): a negative base is raised only to a whole number [ISO/IEC 9075-2:2023 §6.31 GR14e]

String expressions

|| joins two strings, or two binary strings; of CHARACTER VARYING, or BINARY VARYING, if either is. Binary strings joined past 32,767 octets keep their first ones if the rest are X'00', else are refused (SQLSTATE 22001). The string functions:

Function Value
SUBSTRING(s FROM start [FOR length] [USING units]) the characters of s from start, counting from 1, length of them or the rest; of a binary string, its octets
UPPER(s), LOWER(s) s in upper or lower case
TRIM([[LEADING | TRAILING | BOTH] [c] FROM] s) s without the character c, a space if not written, at its start, end or both, the default
LTRIM(s [, set]), RTRIM(s [, set]), BTRIM(s [, set]) s without any of the characters of set, a space if not written, at its start, its end or both
LPAD(s, n [, pad]), RPAD(s, n [, pad]) s brought to n characters, cut if longer, else with pad, a space if not written, repeated before it or after it; '' if n is less than 1
OVERLAY(s PLACING r FROM start [FOR length] [USING units]) s with r in place of length characters from start, or of as many as r has; of binary strings, octets
TRIM([[LEADING | TRAILING | BOTH] [o] FROM] b) of a binary string b, b without the octet o, X'00' if not written

A function of a CHARACTER VARYING string is CHARACTER VARYING, and SUBSTRING, OVERLAY and TRIM of a binary string BINARY VARYING. Of a large object each is a large object of its type, and LPAD and RPAD of one a CLOB of 4G characters. UPPER, LOWER, the trims and the pads take character strings alone. A trim is as long as its string at most. A pad is CHARACTER VARYING(32767), and gives at most 32,767 characters, so cast it to a shorter type to show or store it narrow. A trim compares characters under the collation of its string, so under CASE_INSENSITIVE TRIM('b' FROM 'Bob') is o. A trim or pad is of its string's collation, whatever that of its other strings. LTRIM, RTRIM, BTRIM, LPAD and RPAD are not reserved, so a column may be named any of them.

VALUES ('Ada' || ' ' || 'Lovelace', SUBSTRING('Lovelace' FROM 1 FOR 4), UPPER('ada'),
        POSITION('ve' IN 'Lovelace'), CHAR_LENGTH('Ada'));
VALUES ('[' || TRIM('  x  ') || ']', TRIM(LEADING '0' FROM '007'), OCTET_LENGTH('é'), BIT_LENGTH('A'));
VALUES (LTRIM('xxyAda', 'xy'), RTRIM('Ada...', '.'), '[' || BTRIM('  Ada  ') || ']', CAST(LPAD('42', 6, '0') AS CHAR(6)),
        CAST(RPAD('Ada', 8, '.-') AS CHAR(8)), CAST(LPAD('Lovelace', 4) AS CHAR(4)));
              |          |     |                      |
--------------+----------+-----+----------------------+----------------------
 Ada Lovelace | Love     | ADA |                    3 |                    3
(1 row)
         |     |                      |
---------+-----+----------------------+----------------------
 [x]     | 7   |                    2 |                    8
(1 row)
        |        |           |        |          |
--------+--------+-----------+--------+----------+------
 Ada    | Ada    | [Ada]     | 000042 | Ada.-.-. | Love
(1 row)
VALUES (POSITION(X'0B' IN X'0A0B0C'), SUBSTRING(X'0A0B0C' FROM 2), X'0A' || X'0B', OCTET_LENGTH(X'0A0B'), BIT_LENGTH(X'0A0B'));
                      |        |      |                      |
----------------------+--------+------+----------------------+----------------------
                    2 | 0B0C   | 0A0B |                    2 |                   16
(1 row)

SUBSTRING, POSITION(a IN s [USING units]) and CHAR_LENGTH(s [USING units]) count characters, or with USING OCTETS octets of the string's UTF-8. A substring in octets takes the whole characters within them, so it never splits one. USING CHARACTERS is the default spelled out, and a binary string's functions take no USING.

VALUES (CHAR_LENGTH('café' USING OCTETS), POSITION('crème' IN 'café crème' USING OCTETS), SUBSTRING('café crème' FROM 7 USING OCTETS));
                      |                      |
----------------------+----------------------+------------
                    5 |                    7 | crème
(1 row)

CONVERT and TRANSLATE, of character sets other than SQL_TEXT, are valid SQL Turborel does not process: SQLCODE -900.

Regular expressions

Four functions search a string for the matches of an XQuery regular expression, read as LIKE_REGEX reads it, with a flag after FLAG if one is written. Matches are counted from the start position after FROM, 1 if none is written, and each begins after the one before it ends.

Function Value
OCCURRENCES_REGEX(pattern [FLAG f] IN s [FROM start] [USING units]) how many matches there are; -1 if start is not a position of s
POSITION_REGEX([START | AFTER] pattern [FLAG f] IN s [FROM start] [USING units] [OCCURRENCE n] [GROUP g]) where the nth match, or its group g, begins, or with AFTER the position after it ends; 0 if there is none
SUBSTRING_REGEX(pattern [FLAG f] IN s [FROM start] [USING units] [OCCURRENCE n] [GROUP g]) what the nth match, or its group g, matched; null if there is none
TRANSLATE_REGEX(pattern [FLAG f] IN s [WITH r] [FROM start] [USING units] [OCCURRENCE n | ALL]) s with every match, or the nth, replaced by r, '' if not written

The nth match is the first if OCCURRENCE is not written, and group 0 is the whole match. In the replacement, $1 stands for what the first group matched, $0 for the whole match, and \$ and \\ for $ and \. USING OCTETS counts positions in octets of the string as UTF-8, USING CHARACTERS, the default, in characters. A function's value is null if an operand is.

SELECT ID, OCCURRENCES_REGEX('a' FLAG 'i' IN NAME), POSITION_REGEX('[aeiou]' IN NAME OCCURRENCE 2),
       SUBSTRING_REGEX('r.' IN NAME), CAST(TRANSLATE_REGEX('([aeiou])' IN NAME WITH '<$1>') AS VARCHAR(20))
  FROM STAFF ORDER BY ID;
VALUES (POSITION_REGEX(AFTER 'é' IN 'café crème' USING OCTETS), SUBSTRING_REGEX('(\w+)@(\w+)' IN 'ada@example' GROUP 2),
        CAST(TRANSLATE_REGEX('a' IN 'banana' WITH 'o' OCCURRENCE 2) AS CHAR(6)));
           ID |                      |                      |                                |
--------------+----------------------+----------------------+--------------------------------+----------------------
            1 |                    2 |                    0 | NULL                           | Ad<a>
            2 |                    1 |                    5 | ra                             | Gr<a>c<e>
            3 |                    0 |                    0 | NULL                           | Edsg<e>r
            4 |                    3 |                    5 | rb                             | B<a>rb<a>r<a>
(4 rows)
                      |             |
----------------------+-------------+--------
                    6 | example     | banona
(1 row)

SUBSTRING_REGEX is as long as its string at most, and TRANSLATE_REGEX too with no replacement, else CHARACTER VARYING(32767), so cast it to show it narrow. Both are of their string's collation. A replacement of a match of no characters is SQLSTATE 2201U, and a $ before no digit or a \ before neither $ nor \ is 2201V. The four names are not reserved, so a column may be named any of them.

Datetime and interval expressions

A datetime plus or minus an interval of its fields; an interval plus or minus another of its class; an interval times or divided by a number; the difference of two datetimes, in parentheses, given an interval qualifier: (D2 - D1) DAY. A datetime may be given in another time zone: TS AT TIME ZONE INTERVAL '+02:00' HOUR TO MINUTE, or TS AT LOCAL, the session's. Data Types shows them, and what is refused.

CASE, NULLIF and COALESCE

A searched CASE gives the result of the first WHEN whose condition is true, else of its ELSE, else null; a simple CASE compares one value with each WHEN's. A simple CASE's WHEN may list several values, separated by commas, and is true if any of them equals the value. NULLIF(a, b) is null if a equals b, else a. COALESCE(a, b, ...) is the first of its values not null. Their results are of a type that holds each result's: strings of the longest, numbers of the most digits; values of types that do not mix are refused.

SELECT ID,
       CASE WHEN SALARY > 7000 THEN 'high' WHEN SALARY IS NULL THEN 'unknown' ELSE 'low' END AS BAND,
       CASE DEPT WHEN 'ENG' THEN 'Engineering' WHEN 'OPS', 'SUP' THEN 'Operations' END AS DEPT_NAME,
       NULLIF(DEPT, 'ENG') AS NOT_ENG,
       COALESCE(DEPT, '---') AS SHOWN
  FROM STAFF ORDER BY ID;
           ID | BAND    | DEPT_NAME   | NOT_ENG | SHOWN
--------------+---------+-------------+---------+-------
            1 | high    | Engineering | NULL    | ENG
            2 | high    | Engineering | NULL    | ENG
            3 | low     | Operations  | OPS     | OPS
            4 | unknown | NULL        | NULL    | ---
(4 rows)

A simple CASE's WHEN may also be the second part of a predicate, its first part the CASE's value: a comparison such as > 8000, BETWEEN, IN, LIKE, IS NULL, a quantified comparison or MATCH, with NOT where the predicate takes one. Values and predicates mix in one list. The CASE's value may be a row, such as (A, B), compared with rows, and a pair of datetimes is compared by OVERLAPS, every WHEN then being OVERLAPS.

SELECT ID,
       CASE SALARY WHEN > 8000 THEN 'top' WHEN BETWEEN 5000 AND 8000 THEN 'mid' WHEN IS NULL THEN 'unpaid' END AS PAY,
       CASE NAME WHEN LIKE 'A%', LIKE 'B%' THEN 'early' ELSE 'late' END AS ALPHABET
  FROM STAFF ORDER BY ID;
           ID | PAY    | ALPHABET
--------------+--------+----------
            1 | mid    | early
            2 | top    | late
            3 | mid    | late
            4 | unpaid | early
(4 rows)

GREATEST and LEAST

GREATEST(a, b, ...) is the greatest of two values or more, LEAST(a, b, ...) the least. Each value is first taken to a type that holds them all, as a CASE's results are, and the values must be comparable. If any is null, the result is null. Strings are ordered by their collation, and of two it holds equal the first is the result. GREATEST and LEAST are not reserved, so a column may be named either.

SELECT ID, GREATEST(SALARY, 6000) AS FLOOR_PAY, LEAST(SALARY, 8000) AS CAPPED, LEAST(NAME, 'Bob') AS FIRST_NAME
  FROM STAFF ORDER BY ID;
VALUES GREATEST(1, 'one');
           ID |   FLOOR_PAY |      CAPPED | FIRST_NAME
--------------+-------------+-------------+--------------------------------
            1 |     7200.00 |     7200.00 | Ada
            2 |     8100.00 |     8000.00 | Bob
            3 |     6000.00 |     5400.00 | Bob
            4 |        NULL |        NULL | Barbara
(4 rows)
turborel: SQLCODE -103 (SQLSTATE 2A000): the arguments of GREATEST are each comparable with each, not NUMERIC(1,0), CHARACTER(3) [ISO/IEC 9075-2:2023 §6.15 SR4] (at character 7)

Set functions

A set function computes one value of a set of rows: of the whole table a query reads, or of each group of its GROUP BY.

Function Value
COUNT(*) the rows
COUNT(x) the rows where x is not null
SUM(x), AVG(x) the sum and mean of x; of exact numbers, AVG of the scale 2 more; of DECFLOAT, DECFLOAT(34); of intervals too
MIN(x), MAX(x) the least and greatest x
EVERY(x) of a BOOLEAN x, TRUE if each x is TRUE, else FALSE
ARRAY_AGG(x ORDER BY ...) an array of every x, nulls among them, in the order ORDER BY gives; null of no rows
COLLECT(x) a multiset of every x, nulls among them; DISTINCT once each; null of no rows
FUSION(m) a multiset of the elements of every multiset m; null of no rows
INTERSECTION(m) a multiset of as many of each value as the multiset m of fewest has; null of no rows
ANY(x), SOME(x) of a BOOLEAN x, TRUE if an x is TRUE, else FALSE
ANY_VALUE(x) one of the values of x, the first one read
LISTAGG(x, 'sep') WITHIN GROUP (ORDER BY ...) the strings x joined by the separator in the order ORDER BY gives, CHARACTER VARYING(32767)
XMLAGG(x ORDER BY ...) of XML values x, those not null one after another in the order ORDER BY gives, a document node of them by default (XML Values); null of none
JSON_ARRAYAGG(x [ORDER BY ...]) the JSON text of an array of every x, in the order ORDER BY gives; null of no rows
JSON_OBJECTAGG(name VALUE x) the JSON text of an object of each row's member, name a character string; null of no rows
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY x) the value a fraction p of the way through the sorted x, between two of them if it falls between
PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY x) the first of the sorted x whose share of the values up to it is at least p
VAR_POP(x), VAR_SAMP(x) the variance of x, of the population and of a sample
STDDEV_POP(x), STDDEV_SAMP(x) their square roots, the standard deviations
COVAR_POP(y, x), COVAR_SAMP(y, x) the covariance of the pairs of y and x, of the population and of a sample
CORR(y, x) the size of their coefficient of correlation, from 0 to 1
REGR_SLOPE(y, x), REGR_INTERCEPT(y, x) the slope and intercept of the least-squares line of y on x
REGR_R2(y, x) that line's coefficient of determination
REGR_COUNT(y, x) the pairs in which neither y nor x is null
REGR_AVGX(y, x), REGR_AVGY(y, x) the means of x and of y
REGR_SXX(y, x), REGR_SYY(y, x), REGR_SXY(y, x) the sums of the squares of the deviations of x and of y from their means, and of their products

Each but COUNT(*) leaves out the nulls of x, and with DISTINCT counts each value of x once: COUNT(DISTINCT DEPT). Of no rows, COUNT is 0 and the others null. A query whose set function left out a null completes with a warning, SQLSTATE 01003, null value eliminated in set function: so does the scalar subquery above, as Barbara's salary is null.

SUM is refused, SQLSTATE 22003, only if its total does not fit its type, whatever order the rows are read in: past 18 digits of exact numbers, the range of approximate ones or of DECFLOAT, or an interval's leading field. So 999999999999999999, 1 and -1 sum to 999999999999999999. AVG is refused only if the mean does not fit. Its digits past its scale are dropped, not rounded, as those of every exact quotient are, so the mean of 10.00, 10.00 and 12.00 is 10.6666. A SUM of DECFLOAT values is rounded to 34 digits after each row, so of values whose total needs more its last digit may depend on that order.

SELECT AVG(X) FROM (VALUES (10.00), (10.00), (12.00)) AS V (X);

----------------------
              10.6666
(1 row)
SELECT COUNT(*), COUNT(DEPT), COUNT(DISTINCT DEPT), SUM(SALARY), AVG(SALARY), MIN(NAME), MAX(SALARY) FROM STAFF;
SELECT DEPT, COUNT(*), AVG(SALARY) FROM STAFF GROUP BY DEPT ORDER BY DEPT;
SELECT SUM(SALARY) FROM STAFF WHERE 1 = 0;
                      |                      |                      |                      |                      |                                |
----------------------+----------------------+----------------------+----------------------+----------------------+--------------------------------+-------------
                    4 |                    3 |                    2 |             20700.00 |            6900.0000 | Ada                            |     8100.00
(1 row, with a warning: SQLSTATE 01003)
 DEPT |                      |
------+----------------------+----------------------
 ENG  |                    2 |            7650.0000
 OPS  |                    1 |            5400.0000
 NULL |                    1 |                 NULL
(3 rows, with a warning: SQLSTATE 01003)

----------------------
                 NULL
(1 row)

A query of a set function and no GROUP BY or HAVING gives one row, even of no rows, so each column it selects is in a set function, or of one value in all the rows, as WHERE makes a column it compares with a literal. A set function is not in a WHERE clause, which is applied to rows one by one:

SELECT NAME, COUNT(*) FROM STAFF;
SELECT ID FROM STAFF WHERE SALARY > AVG(SALARY);
turborel: SQLCODE -103 (SQLSTATE 2A000): when the select list has a set function, every column reference must be inside one, or of a column functionally dependent on none (found STAFF.NAME) [X3.135-1986 §5.25 SR7; ISO/IEC 9075-2:2023 §6.7 SR8] (at character 7)
turborel: SQLCODE -103 (SQLSTATE 2A000): a <set function specification> is in a <where clause> only in a <having clause>'s <subquery>, of an outer reference [X3.135-1986 §5.21 SR3] (at character 36)

The statistical set functions are of numbers. Each is DOUBLE PRECISION, or DECFLOAT(34) if an argument is DECFLOAT, but REGR_COUNT, which is exact. VAR_POP, VAR_SAMP, STDDEV_POP and STDDEV_SAMP take no DISTINCT or ALL (SQLSTATE 2A000, SQLCODE -103). Those of two arguments, y and x, leave out each row in which either is null, with the warning 01003. Of no rows REGR_COUNT is 0 and the others null; of one row VAR_SAMP, STDDEV_SAMP and COVAR_SAMP are null, the variance of a sample of one being unknown. Where every x is alike, CORR, REGR_R2, REGR_SLOPE and REGR_INTERCEPT are null, as the line would be upright; where every y is alike and the x are not, CORR is null and REGR_R2 1. CORR is the size of the coefficient, not its sign. The standard defines it as the square root of its square, so of y falling as x rises it is 1, not -1, and REGR_SLOPE gives the sign. A sum on the way past the range of the result's type is refused (22003, -301).

SELECT VAR_POP(SALARY), VAR_SAMP(SALARY), STDDEV_POP(SALARY), STDDEV_SAMP(SALARY) FROM STAFF;
SELECT DEPT, REGR_COUNT(SALARY, ID), REGR_SLOPE(SALARY, ID), VAR_SAMP(SALARY)
  FROM STAFF GROUP BY DEPT ORDER BY DEPT;
SELECT REGR_SLOPE(Y, X), REGR_INTERCEPT(Y, X), REGR_R2(Y, X), CORR(Y, X), COVAR_SAMP(Y, X)
  FROM (VALUES (2, 1), (4, 2), (5, 3), (9, 4)) AS P (Y, X);
SELECT VAR_POP(DISTINCT SALARY) FROM STAFF;
                          |                          |                          |
--------------------------+--------------------------+--------------------------+--------------------------
                   1.26E6 |                   1.89E6 |     1.1224972160321825E3 |      1.374772708486752E3
(1 row, with a warning: SQLSTATE 01003)
 DEPT |                      |                          |
------+----------------------+--------------------------+--------------------------
 ENG  |                    2 |                      9E2 |                   4.05E5
 OPS  |                    1 |                     NULL |                     NULL
 NULL |                    0 |                     NULL |                     NULL
(3 rows, with a warning: SQLSTATE 01003)
                          |                          |                          |                          |
--------------------------+--------------------------+--------------------------+--------------------------+--------------------------
                    2.2E0 |                    -5E-1 |     9.307692307692307E-1 |     9.647638212377321E-1 |     3.6666666666666665E0
(1 row)
turborel: SQLCODE -103 (SQLSTATE 2A000): VAR_POP takes no <set quantifier>: neither DISTINCT nor ALL [ISO/IEC 9075-2:2023 §10.9 SR2] (at character 15)

The mean is found by a subquery: WHERE SALARY > (SELECT AVG(SALARY) FROM STAFF). A column WHERE fixes may be selected:

SELECT DEPT, COUNT(*) AS N FROM STAFF WHERE DEPT = 'ENG';
 DEPT |                    N
------+----------------------
 ENG  |                    2
(1 row)

A set function followed by OVER and a window is a window function, which keeps every row and computes over the rows of each one's window, as Window functions shows. SUM(SALARY) OVER (PARTITION BY DEPT) is each row's department total. LISTAGG takes a window only of no ORDER BY and no frame.

LISTAGG's separator is a literal, and its WITHIN GROUP's ORDER BY says the order of the strings, each key by its collation. LISTAGG(DISTINCT x, ...) joins each value once, the first of values alike in that order. Strings joined longer than 32,767 characters are refused, SQLSTATE 22001, unless ON OVERFLOW TRUNCATE is written after the separator. Then it joins as many as fit and ends with the separator and a filler, ... if none is written. WITH COUNT adds the number of strings left out in parentheses, and WITHOUT COUNT does not.

A key of ARRAY_AGG's ORDER BY or of a WITHIN GROUP may be followed by NULLS FIRST or NULLS LAST, as a key of a query's ORDER BY may.

FILTER (WHERE condition) after a set function's closing parenthesis feeds it only the rows the condition is true of, so a row it is false or unknown of is not counted and its argument is not computed. A group no row of which passes gives COUNT 0 and the others null, and a null is left out, with warning 01003, only of a row the filter takes. Each set function has its own filter, so one query counts several subsets of the same rows. FILTER comes before OVER, and over a window each frame's set function takes the rows of the frame the filter is true of.

SELECT DEPT, COUNT(*) FILTER (WHERE SALARY > 6000) AS WELL_PAID,
    SUM(SALARY) FILTER (WHERE NAME <> 'Ada') AS NOT_ADA FROM STAFF GROUP BY DEPT ORDER BY DEPT;
SELECT ID, NAME, COUNT(*) FILTER (WHERE SALARY > 6000) OVER (ORDER BY ID) AS SO_FAR FROM STAFF ORDER BY ID;
 DEPT |            WELL_PAID |              NOT_ADA
------+----------------------+----------------------
 ENG  |                    2 |              8100.00
 OPS  |                    0 |              5400.00
 NULL |                    0 |                 NULL
(3 rows, with a warning: SQLSTATE 01003)
           ID | NAME                           |               SO_FAR
--------------+--------------------------------+----------------------
            1 | Ada                            |                    1
            2 | Grace                          |                    2
            3 | Edsger                         |                    2
            4 | Barbara                        |                    2
(4 rows)

A filter is of the rows its set function aggregates, so it holds no set function, subquery, window function or nested window function, and no outer reference. The filter of a set function of an enclosing query, whose argument is an outer reference, names no column at all. Each is refused, SQLCODE -103:

SELECT COUNT(*) FILTER (WHERE SALARY > AVG(SALARY)) FROM STAFF;
turborel: SQLCODE -103 (SQLSTATE 2A000): a <filter clause> cannot contain a set function [ISO/IEC 9075-2:2023 §6.9 SR5] (at character 7)

RANK, DENSE_RANK, PERCENT_RANK and CUME_DIST of values, followed by WITHIN GROUP and an ORDER BY of as many keys, are the hypothetical set functions. Each gives what its window function would give a row of those values, were it added to the group's rows and ordered among them by the keys. RANK is one more than the rows before it, and DENSE_RANK one more than the distinct rows before it, both of COUNT's type. PERCENT_RANK is the rows before it over the group's rows, 0 of none, and CUME_DIST the rows not after it, itself among them, over the group's rows and it, both DOUBLE PRECISION. A row whose keys are all equal to the values is a peer, not before. Of a group of no rows they are 1, 1, 0 and 1.

A null key is not left out but placed as its null ordering says, last ascending and first descending unless NULLS FIRST or LAST is written. Each value and its key are compared as of the type they have in common, character strings by the collation they have together. The values are not aggregated, so of a grouped query they name grouping columns and columns functionally dependent on them, as the select list does, and of one with no GROUP BY only a column of one value in all the rows. A ? value takes its key's type. A hypothetical set function takes FILTER but no OVER.

SELECT DEPT, RANK(7500) WITHIN GROUP (ORDER BY SALARY DESC) AS PLACE,
    CUME_DIST(7500) WITHIN GROUP (ORDER BY SALARY) AS AT_MOST FROM STAFF GROUP BY DEPT ORDER BY DEPT;
SELECT RANK(7500) WITHIN GROUP (ORDER BY SALARY) OVER () FROM STAFF;
 DEPT |                PLACE |                  AT_MOST
------+----------------------+--------------------------
 ENG  |                    2 |     6.666666666666666E-1
 OPS  |                    1 |                      1E0
 NULL |                    2 |                     5E-1
(3 rows)
turborel: SQLCODE -103 (SQLSTATE 2A000): RANK WITHIN GROUP, a hypothetical set function, is over no window [ISO/IEC 9075-2:2023 §9.23 SR8c] (at character 7)

Barbara's null salary is placed first descending, before 7500, and last ascending, after it.

PERCENTILE_CONT and PERCENTILE_DISC of a fraction p from 0 to 1, followed by WITHIN GROUP and an ORDER BY of one key, are the inverse distribution functions. They sort the key's values that are not null in the key's direction. PERCENTILE_CONT takes the value p·(N−1) places from the first of N, counted from 0. Where that falls between two values it moves from the first toward the second by what is left over, so the median of 7200 and 8100 is 7650. PERCENTILE_DISC takes the first value of a share of the values up to it, its peers among them, that is at least p, so it is always one of the values. The key is a number or an interval. Of an exact or approximate key the result is DOUBLE PRECISION, of a DECFLOAT key DECFLOAT(34), and of an interval the interval's type.

A group of no values gives null, as does a null p. A p below 0 or above 1 is refused, SQLSTATE 22003, even of a group of no rows. Like a hypothetical set function's values, p is not aggregated, and a ? p is NUMERIC(18,17). An exact p picks its places exactly, so a share equal to p is at least it. Each takes FILTER, and OVER a window of a PARTITION BY alone, whose every row has its partition's value, but not a window of an ORDER BY or a frame. Over a window p may differ from row to row.

In a subquery, an inverse distribution function or hypothetical set function whose sort keys name only columns of an enclosing query is that query's, computed over its groups, and its p or values name that query's grouping columns and columns functionally dependent on them. A key that names a column of the subquery too keeps it in the subquery, where the enclosing row's columns are values.

SELECT DEPT, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SALARY) AS MEDIAN,
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SALARY) AS MIDDLE FROM STAFF GROUP BY DEPT ORDER BY DEPT;
SELECT ID, NAME, PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY SALARY DESC) OVER (PARTITION BY DEPT) AS HIGH FROM STAFF ORDER BY ID;
SELECT PERCENTILE_CONT(1.5) WITHIN GROUP (ORDER BY SALARY) FROM STAFF;
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SALARY) OVER (ORDER BY ID) FROM STAFF;
 DEPT |                   MEDIAN |                   MIDDLE
------+--------------------------+--------------------------
 ENG  |                   7.65E3 |                    7.2E3
 OPS  |                    5.4E3 |                    5.4E3
 NULL |                     NULL |                     NULL
(3 rows, with a warning: SQLSTATE 01003)
           ID | NAME                           |                     HIGH
--------------+--------------------------------+--------------------------
            1 | Ada                            |                  7.875E3
            2 | Grace                          |                  7.875E3
            3 | Edsger                         |                    5.4E3
            4 | Barbara                        |                     NULL
(4 rows, with a warning: SQLSTATE 01003)
turborel: SQLCODE -301 (SQLSTATE 22003): the fraction of an inverse distribution function is from 0 to 1, not 1.5 [ISO/IEC 9075-2:2023 §10.9 GR10c]
turborel: SQLCODE -103 (SQLSTATE 2A000): PERCENTILE_DISC is over a window of no ordering and no frame [ISO/IEC 9075-2:2023 §9.23 SR8d i] (at character 7)

Engineering's upper quarter, sorted from the highest salary, is a quarter of the way from 8100 to 7200.

ANY_VALUE gives a column that the grouping decides but does not name, such as a department's name grouped by its number, with no need to group by it too. Which value it gives of several is not defined. Below, the four names fit, so the second query adds no filler.

SELECT DEPT, LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY SALARY DESC) AS BY_PAY,
    ANY_VALUE(NAME) AS SOMEONE FROM STAFF GROUP BY DEPT ORDER BY DEPT;
SELECT LISTAGG(NAME, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT) WITHIN GROUP (ORDER BY ID) FROM STAFF;
 DEPT | BY_PAY                                                       | SOMEONE
------+--------------------------------------------------------------+--------------------------------
 ENG  | Grace, Ada                                                   | Ada
 OPS  | Edsger                                                       | Edsger
 NULL | Barbara                                                      | Barbara
(3 rows)

--------------------------------------------------------------
 Ada, Grace, Edsger, Barbara
(1 row)

JSON_ARRAYAGG and JSON_OBJECTAGG make JSON text of a group's rows, as JSON_ARRAY and JSON_OBJECT do of their operands, and keep the nulls the other set functions leave out. JSON_ARRAYAGG takes ORDER BY within its parentheses, as ARRAY_AGG does, and without one its order is that of the rows read. Each takes FILTER and OVER, and the clauses of its constructor: NULL ON NULL or ABSENT ON NULL, ABSENT ON NULL by default of JSON_ARRAYAGG and NULL ON NULL of JSON_OBJECTAGG; of JSON_OBJECTAGG, WITH UNIQUE KEYS; FORMAT JSON after the value; and RETURNING type. A null name is SQLSTATE 22004.

SELECT DEPT, JSON_ARRAYAGG(NAME ORDER BY SALARY DESC RETURNING VARCHAR(20)) AS BY_PAY,
    JSON_OBJECTAGG(NAME VALUE SALARY RETURNING VARCHAR(40)) AS PAY FROM STAFF GROUP BY DEPT ORDER BY DEPT;
SELECT JSON_ARRAYAGG(SALARY ORDER BY ID NULL ON NULL RETURNING VARCHAR(40)) FROM STAFF;
 DEPT | BY_PAY               | PAY
------+----------------------+------------------------------------------
 ENG  | ["Grace","Ada"]      | {"Ada":7200.00,"Grace":8100.00}
 OPS  | ["Edsger"]           | {"Edsger":5400.00}
 NULL | ["Barbara"]          | {"Barbara":null}
(3 rows)

------------------------------------------
 [7200.00,8100.00,5400.00,null]
(1 row)

NEXT VALUE FOR

NEXT VALUE FOR generator takes the next value of a sequence generator CREATE SEQUENCE made, of its type. Each row takes one value, however many times the row names the generator, and no value is ever taken twice, even by a transaction rolled back.

CREATE SEQUENCE BADGE START WITH 100 INCREMENT BY 10;
SELECT NEXT VALUE FOR BADGE, NEXT VALUE FOR BADGE + 1, NAME FROM STAFF WHERE DEPT = 'ENG';
OK
                      |                      | NAME
----------------------+----------------------+--------------------------------
                  100 |                  101 | Ada
                  110 |                  111 | Grace
(2 rows)

It is written in a select list of a cursor's query, a SELECT INTO or an INSERT's query, in an INSERT's VALUES, or in an UPDATE's SET, and in no CASE, condition, set function, grouped or DISTINCT query or subquery, where a row might take a value it never shows.

Arrays and rows

An array is made of its elements, ARRAY[1, 2, 3], or of the rows of a query of one column, ARRAY(SELECT ID FROM STAFF ORDER BY ID), in the order its ORDER BY gives. ARRAY[] is an array of no elements, written where its type is known, as an INSERT's value or in a CAST. A row is made of its fields, ROW(1, 'Ada'), named F1, F2 and so on.

Expression Value
a[i] element i of the array a, from 1; null if a or i is null
CARDINALITY(a) the elements of a
ARRAY_MAX_CARDINALITY(a) the maximum cardinality of a's type
TRIM_ARRAY(a, n) a without its last n elements
a || b the elements of a, then those of b
r.f, (r).f the field f of the row r; null if r is null

An element reference past the cardinality, or TRIM_ARRAY of more elements than there are, is refused, SQLSTATE 2202E. A column's field is written after the column, T.C.F or C.F, as far as its rows nest.

SELECT ID, ARRAY[ID, ID * 10] AS PAIR, ROW(ID, NAME) AS PERSON FROM STAFF WHERE ID < 3 ORDER BY ID;
SELECT ARRAY_AGG(NAME ORDER BY SALARY DESC) AS BY_PAY FROM STAFF WHERE SALARY IS NOT NULL;
VALUES (CARDINALITY(ARRAY[1, 2, 3]), TRIM_ARRAY(ARRAY[1, 2, 3], 1), ARRAY[1] || ARRAY[2, 3], (ROW(1, 'Ada')).F2);
VALUES (ARRAY[1, 2, 3])[4];
           ID | PAIR                                                | PERSON
--------------+-----------------------------------------------------+-----------------------------------------------------
            1 | ARRAY[1, 10]                                        | ROW(1, 'Ada')
            2 | ARRAY[2, 20]                                        | ROW(2, 'Grace')
(2 rows)
 BY_PAY
--------------------------------------------------------------
 ARRAY['Grace', 'Ada', 'Edsger']
(1 row)
              |                        |                        |
--------------+------------------------+------------------------+-----
            3 | ARRAY[1, 2]            | ARRAY[1, 2, 3]         | Ada
(1 row)
turborel: SQLCODE -301 (SQLSTATE 2202E): no element 4 is in an array of 3 elements [ISO/IEC 9075-2:2023 §6.25 GR2b]

Multisets

A multiset is made of its elements, MULTISET[1, 2, 2], or of the rows of a query of one column, MULTISET(SELECT DEPT FROM STAFF). TABLE(query) makes a multiset of a row of each of a query's rows. Neither query has an ORDER BY. MULTISET[] is a multiset of no elements, written where its type is known.

Expression Value
m MULTISET UNION [ALL | DISTINCT] n the elements of both; DISTINCT one of each
m MULTISET EXCEPT [ALL | DISTINCT] n the elements of m less as many of n; DISTINCT those n lacks, one of each
m MULTISET INTERSECT [ALL | DISTINCT] n as many of each element as the one with fewer has; DISTINCT one of each
SET(m) one element of each, nulls counting as alike
ELEMENT(m) the one element of m; null if m is null or empty
CARDINALITY(m) the elements of m

Each operator is ALL if neither is written, and null if an operand is. MULTISET INTERSECT binds tighter than UNION and EXCEPT, which are read left to right. ELEMENT of a multiset of more than one element is refused, SQLSTATE 21000. COLLECT, FUSION and INTERSECTION, in Set functions, gather multisets of a group's rows.

VALUES (MULTISET[1, 1, 2] MULTISET UNION MULTISET[2, 3], MULTISET[1, 1, 2] MULTISET UNION DISTINCT MULTISET[2, 3]);
VALUES (MULTISET[1, 1, 2] MULTISET EXCEPT MULTISET[1], MULTISET[1, 1, 2] MULTISET INTERSECT MULTISET[1, 2, 2]);
VALUES (SET(MULTISET[1, 1, 2]), ELEMENT(MULTISET['only']), CARDINALITY(MULTISET[3, 3]));
SELECT DEPT, COLLECT(NAME) AS WHO FROM STAFF WHERE DEPT IS NOT NULL GROUP BY DEPT ORDER BY DEPT;
VALUES MULTISET(SELECT DEPT FROM STAFF WHERE DEPT IS NOT NULL);
VALUES ELEMENT(MULTISET[1, 2]);
                                                              |
--------------------------------------------------------------+--------------------------------------------------------------
 MULTISET[1, 1, 2, 2, 3]                                      | MULTISET[1, 2, 3]
(1 row)
                                                              |
--------------------------------------------------------------+--------------------------------------------------------------
 MULTISET[1, 2]                                               | MULTISET[1, 2]
(1 row)
                                                              |      |
--------------------------------------------------------------+------+--------------
 MULTISET[1, 2]                                               | only |            2
(1 row)
 DEPT | WHO
------+--------------------------------------------------------------
 ENG  | MULTISET['Ada', 'Grace']
 OPS  | MULTISET['Edsger']
(2 rows)

--------------------------------------------------------------
 MULTISET['ENG', 'ENG', 'OPS']
(1 row)
turborel: SQLCODE -811 (SQLSTATE 21000): ELEMENT of a multiset of 2 elements [ISO/IEC 9075-2:2023 §6.26 SR1, §7.11 GR1a]

Structured values and references

A value of a structured type (CREATE TYPE) is made by NEW, NEW t() of its attributes' defaults or NEW t(args) by a constructor method. v.a observes an attribute and v.a(x) gives a copy with it changed, so a value is built a mutator at a time. A method is invoked of a value, v.m(args), and runs by the value's most specific type. A static method is invoked of its type, t::m(args).

A reference identifies a row of a typed table. r -> a reads an attribute of the row, r -> m(args) invokes a method of it, and DEREF(r) gives the row as a value of its type, each null if the row is gone. v.SPECIFICTYPE names a value's most specific type, and TREAT(v AS t) takes it as a value of its subtype t.

CREATE TYPE ITEM AS (LABEL VARCHAR(10), GRAMS INTEGER) NOT FINAL REF IS SYSTEM GENERATED
    METHOD KILOS () RETURNS DECIMAL(9,3) CONTAINS SQL;
CREATE METHOD KILOS () RETURNS DECIMAL(9,3) FOR ITEM RETURN SELF.GRAMS / 1000.0;
CREATE TYPE GIFT UNDER ITEM AS (WRAP VARCHAR(10)) NOT FINAL;
CREATE TABLE ITEMS OF ITEM (REF IS ITEM_ID SYSTEM GENERATED);
CREATE TABLE GIFTS OF GIFT UNDER ITEMS;
INSERT INTO ITEMS (LABEL, GRAMS) VALUES ('LAMP', 1500);
INSERT INTO GIFTS (LABEL, GRAMS, WRAP) VALUES ('BOOK', 400, 'RED');
CREATE TABLE ORDERS (N INTEGER, WHAT REF(ITEM) SCOPE ITEMS);
INSERT INTO ORDERS SELECT CASE LABEL WHEN 'LAMP' THEN 1 ELSE 2 END, ITEM_ID FROM ITEMS;
SELECT N, WHAT -> LABEL, WHAT -> KILOS() FROM ORDERS ORDER BY N;
SELECT N, (DEREF(WHAT)).SPECIFICTYPE FROM ORDERS ORDER BY N;
SELECT NEW ITEM().LABEL('CUP').GRAMS(250).KILOS() FROM (VALUES (1)) AS T (X);
OK
OK
OK
OK
OK
OK
OK
OK
OK
            N |            |
--------------+------------+-------------
            1 | LAMP       |       1.500
            2 | BOOK       |       0.400
(2 rows)
            N |
--------------+--------------------------------------------------------------
            1 | "TURBOREL"."ANN"."ITEM"
            2 | "TURBOREL"."ANN"."GIFT"
(2 rows)

-------------
       0.250
(1 row)

SQL/JSON

JSON_VALUE and JSON_QUERY take a value out of JSON text, a character string, by an SQL/JSON path expression; JSON_EXISTS tests whether the path finds anything, and JSON_TABLE presents what it finds as a table. Each of the first three is written as

JSON_VALUE(text [FORMAT JSON], 'path' [PASSING value [FORMAT JSON] AS name, ...]
           [RETURNING type] [behavior ON EMPTY] [behavior ON ERROR])
JSON_QUERY(text [FORMAT JSON], 'path' [PASSING ...] [RETURNING type [FORMAT JSON]]
           [WITHOUT [ARRAY] | WITH [CONDITIONAL | UNCONDITIONAL] [ARRAY] WRAPPER]
           [KEEP | OMIT QUOTES [ON SCALAR STRING]] [behavior ON EMPTY] [behavior ON ERROR])

The path is a character string literal, read as the statement is prepared, so a path that is none is refused then.

Function Value
JSON_VALUE the one scalar the path finds, a string, number, truth value or datetime, cast to the RETURNING type: a character string, numeric, BOOLEAN or datetime type, CHARACTER VARYING(32767) if none is written
JSON_QUERY the one item the path finds, an array or object too, written as JSON text of the RETURNING type, a character string type, CHARACTER VARYING(32767) if none is written

Either is null if the text is. A behavior ON EMPTY says what the value is if the path finds nothing, and one ON ERROR what it is in place of a data exception, as text that is no JSON, two items found, or an array for JSON_VALUE. Each is NULL if not written. JSON_VALUE takes ERROR, NULL or DEFAULT and a value expression, cast to the RETURNING type; JSON_QUERY takes ERROR, NULL, EMPTY ARRAY or EMPTY OBJECT. ERROR ON EMPTY makes an empty result an error that ON ERROR then replaces, so ERROR ON EMPTY alone is null of no item. Only a data exception, an SQLSTATE of class 22, is replaced: an error of a limit, SQLCODE -900, is raised whatever ON ERROR says.

CREATE TABLE PURCHASES (ID INTEGER PRIMARY KEY, DOC VARCHAR(200));
INSERT INTO PURCHASES VALUES (1, '{"buyer":"Ada","items":[{"sku":"A1","qty":2},{"sku":"B7","qty":1}]}'),
                             (2, '{"buyer":"Grace","items":[],"note":null}');
SELECT ID, JSON_VALUE(DOC, 'lax $.buyer' RETURNING VARCHAR(10)), JSON_VALUE(DOC, 'lax $.items.size()' RETURNING INTEGER),
       JSON_VALUE(DOC, 'strict $.items[0].qty' RETURNING INTEGER DEFAULT 0 ON ERROR)
  FROM PURCHASES ORDER BY ID;
OK
OK
           ID |            |              |
--------------+------------+--------------+--------------
            1 | Ada        |            2 |            2
            2 | Grace      |            0 |            0
(2 rows)

JSON_QUERY without a wrapper finds one item, and WITH ARRAY WRAPPER puts all it finds in an array. WITH CONDITIONAL ARRAY WRAPPER does so unless the path finds one array or object, so a lone string or number is wrapped. OMIT QUOTES gives a string found alone as it is, where KEEP QUOTES, the default, writes it as JSON, in quotation marks. A wrapper WITH and a QUOTES clause are not written together.

SELECT ID, JSON_QUERY(DOC, 'lax $.items[*].sku' RETURNING VARCHAR(20) WITH ARRAY WRAPPER),
       JSON_QUERY(DOC, 'lax $.items[0]' RETURNING VARCHAR(30) EMPTY OBJECT ON EMPTY),
       JSON_QUERY(DOC, 'lax $.buyer' RETURNING VARCHAR(10) OMIT QUOTES)
  FROM PURCHASES ORDER BY ID;
           ID |                      |                                |
--------------+----------------------+--------------------------------+------------
            1 | ["A1","B7"]          | {"sku":"A1","qty":2}           | Ada
            2 | NULL                 | {}                             | Grace
(2 rows)

Returned types and behaviors

JSON_VALUE RETURNING a type casts the scalar found as CAST does. It casts a string to a time or timestamp, with a time zone or without, as its literal is written, a number to an exact or approximate type, cut to its scale, and a string to CHAR(n), padded, or to a CLOB. A cast that fails, as of a string that is no time, is a data exception ON ERROR replaces.

The value expression of a DEFAULT is evaluated only for a row that takes it. A DEFAULT ON EMPTY is evaluated only if the path finds nothing, and a DEFAULT ON ERROR only in place of an exception. So a DEFAULT that could fail, as a division, does so only where it is used. NEXT VALUE FOR in a DEFAULT is the exception, as it draws once of each row wherever it is written, and the operator's DEFAULTs are then evaluated of every row. A DEFAULT ON EMPTY whose cast to the RETURNING type fails is an exception that ON ERROR replaces in turn; a DEFAULT ON ERROR whose cast fails raises it.

The text JSON_QUERY writes is not cut to its RETURNING type. Text longer than the type is SQLSTATE 22001, SQLCODE -301, whatever ON ERROR says, since the standard raises it directly rather than as the operator's own exception.

VALUES (JSON_VALUE('"10:20:30+02:00"', 'lax $' RETURNING TIME WITH TIME ZONE), JSON_VALUE('1.25', 'lax $' RETURNING NUMERIC(4,1)),
        JSON_VALUE('"ab"', 'lax $' RETURNING CHAR(4)) || '|');
SELECT ID, JSON_VALUE(DOC, 'lax $.items[0].qty' RETURNING INTEGER DEFAULT 1 / 0 ON EMPTY) FROM PURCHASES WHERE ID = 1;
VALUES JSON_VALUE('[]', 'lax $' RETURNING INTEGER DEFAULT 1 / 0 ON ERROR);
VALUES JSON_QUERY('[1,2,3]', 'lax $' RETURNING VARCHAR(3) NULL ON ERROR);
                |        |
----------------+--------+-------
 10:20:30+02:00 |    1.2 | ab  |
(1 row)
           ID |
--------------+--------------
            1 |            2
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22012): a divisor shall not be 0 [X3.135-1986 §5.9 GR5]
turborel: SQLCODE -301 (SQLSTATE 22001): the JSON text of 7 characters or octets is longer than CHARACTER VARYING(3) [ISO/IEC 9075-2:2023 §9.43 GR2a iii]

The path language

The SQL/JSON Path Language gives the language in full: its grammar, every method and the SQLSTATE of each error. In short, a path begins with its mode, lax or strict, then $, the text, and its accessors: .name or ."name" a member of an object, .* every member's value, [n], [n to m] and [last] elements of an array, counted from 0, [*] every element, and the item methods. .type() names an item's kind and .size() counts an array's elements; .double(), .ceiling(), .floor() and .abs() compute; .bigint(), .integer(), .decimal(p, s), .number(), .boolean(), .string(), .date(), .time(p), .time_tz(p), .timestamp(p) and .timestamp_tz(p) cast an item as CAST does, the precisions optional; .datetime() reads a string as the date, time or timestamp it is, and .datetime("template") as the template reads it, as in CAST's FORMAT: a TIMESTAMP if the template has fields of the date and the time, a DATE or a TIME if of one, WITH TIME ZONE if it has TZH, a string it does not read being SQLSTATE 22031; and .keyvalue() gives an object's members, each as an object of its key, its value and an id. A number may be written in hexadecimal, 0x1F. A filter, ? (predicate), keeps the items its predicate is true of, @ being the item tested: ==, !=, <>, <, <=, > and >= compare, exists (path), starts with and like_regex test, and &&, ||, ! and (...) is unknown combine. +, -, *, / and % compute with numbers. Unlike SQL, the language is case-sensitive: $.Buyer is not $.buyer, and true, null and the methods are lower case.

The casting methods take what CAST takes, and an item of another kind is an exception ON ERROR replaces, as .date() of a string that is no date:

VALUES (JSON_VALUE('{"n":"12.345"}', 'lax $.n.decimal(4,2)' RETURNING DECIMAL(4,2)), JSON_VALUE('"2024-02-29"', 'lax $.date()' RETURNING DATE),
        JSON_VALUE('{}', 'lax 0x1F' RETURNING INTEGER));
VALUES JSON_VALUE('"x"', 'lax $.date()' ERROR ON ERROR);
        |            |
--------+------------+--------------
  12.34 | 2024-02-29 |           31
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22018): 'x' is no DATE literal's string [ISO/IEC 9075:1992 §6.10 GR9a ii, GR10a ii, GR11a ii]

Lax mode takes an array for its elements where an object or a single item is wanted, and a single item for an array, and a member or element that is not there for nothing. Strict mode takes them as errors, which ON ERROR then replaces.

VALUES (JSON_VALUE('{"a":[1,2]}', 'lax $.a[*]' RETURNING INTEGER), JSON_VALUE('{"a":[1,2]}', 'lax $.a[last]' RETURNING INTEGER),
        JSON_VALUE('{"a":[1,2]}', 'lax $.a.type()' RETURNING VARCHAR(10)), JSON_VALUE('[{"a":3}]', 'lax $.a' RETURNING INTEGER),
        JSON_VALUE('[{"a":3}]', 'strict $.a' RETURNING INTEGER));
VALUES JSON_VALUE('[1,2]', 'lax $[*]' RETURNING INTEGER ERROR ON ERROR);
VALUES JSON_VALUE('{}', 'strict $.a' RETURNING INTEGER ERROR ON ERROR);
VALUES JSON_VALUE('{}', 'lax $.a' RETURNING INTEGER ERROR ON EMPTY);
              |              |            |              |
--------------+--------------+------------+--------------+--------------
         NULL |            2 | array      |            3 |         NULL
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22034): the SQL/JSON path expression gives more than one SQL/JSON item [ISO/IEC 9075-2:2023 §9.48 GR4a]
turborel: SQLCODE -301 (SQLSTATE 2203A): an SQL/JSON item is not an object with a member of the key a [ISO/IEC 9075-2:2023 §9.46 GR11g ii 1 B II]

--------------
         NULL
(1 row)
VALUES (JSON_VALUE('"29.02.2024"', 'lax $.datetime("DD.MM.YYYY")' RETURNING DATE),
        JSON_VALUE('"9:05 P.M."', 'lax $.datetime("HH12:MI A.M.").type()' RETURNING VARCHAR(30)));
            |
------------+--------------------------------
 2024-02-29 | time without time zone
(1 row)

PASSING gives the path variables: PASSING value AS name is $name in the path, a string, number, truth value or datetime as it is, and with FORMAT JSON a string's JSON text, parsed. The name is the identifier's value, as SQL reads one: a regular identifier is upper case, so PASSING 2 AS n is $N, and a delimited one is as written, so PASSING 'B' AS "p" is $p. A variable the path names and PASSING does not is an error as the statement is prepared.

SELECT ID, JSON_VALUE(DOC, 'lax $.items[*] ? (@.qty >= $N).sku' PASSING 2 AS N RETURNING VARCHAR(5)),
       JSON_VALUE(DOC, 'lax $.items[*] ? (@.sku starts with $p).qty' PASSING 'B' AS "p" RETURNING INTEGER)
  FROM PURCHASES ORDER BY ID;
VALUES JSON_VALUE('{}', 'lax $n' PASSING 2 AS n);
           ID |       |
--------------+-------+--------------
            1 | A1    |            1
            2 | NULL  |         NULL
(2 rows)
turborel: SQLCODE -103 (SQLSTATE 2A000): the named variable $n of the SQL/JSON path expression is passed no value [ISO/IEC 9075-2:2023 §9.46 SR4] (at character 24)

A cast that cuts a string to the RETURNING type completes with the warning 01004, as CAST does. A binary string's JSON text, FORMAT JSON ENCODING, is valid SQL turborel does not process: SQLCODE -900. Appendix A lists the SQLSTATEs of SQL/JSON, 22032 to 2203G, and Limits how deep JSON text and a path may nest. The functions' names and the words of their clauses are not reserved, so a column may be named any of them.

JSON_OBJECT and JSON_ARRAY

JSON_OBJECT and JSON_ARRAY make JSON text of their operands:

JSON_OBJECT([[KEY] name VALUE value [FORMAT JSON] | name : value [FORMAT JSON], ...
            [NULL ON NULL | ABSENT ON NULL] [WITH UNIQUE [KEYS] | WITHOUT UNIQUE [KEYS]]]
            [RETURNING type [FORMAT JSON]])
JSON_ARRAY([value [FORMAT JSON], ... [NULL ON NULL | ABSENT ON NULL]] [RETURNING type [FORMAT JSON]])
JSON_ARRAY(query [FORMAT JSON] [NULL ON NULL | ABSENT ON NULL] [RETURNING type [FORMAT JSON]])

A name is a character string, and a null one is SQLSTATE 22004. A value that is a string, a number or a truth value is kept, one of another type, as a date, written as a string, and a value FORMAT JSON is the JSON text it holds, parsed. A value that is itself a constructor or a JSON_QUERY is FORMAT JSON of itself, so constructors nest. NULL ON NULL keeps a null value as JSON's null, ABSENT ON NULL leaves it out; JSON_OBJECT keeps it by default, JSON_ARRAY leaves it out. WITH UNIQUE KEYS makes two members of one name SQLSTATE 22030. JSON_ARRAY of a query is an array of its one column's values, in the order its ORDER BY gives, [] of no rows; a lone subquery in parentheses is so too. Each is CHARACTER VARYING(32767) without RETURNING, and text longer than its type is SQLSTATE 22001.

SELECT JSON_OBJECT('id' VALUE ID, 'buyer' : JSON_VALUE(DOC, 'lax $.buyer'), 'note' VALUE JSON_QUERY(DOC, 'lax $.note')
                   ABSENT ON NULL RETURNING VARCHAR(40))
  FROM PURCHASES ORDER BY ID;
VALUES (JSON_ARRAY(1, 'two', CAST(NULL AS INTEGER), JSON_ARRAY(3, 4) RETURNING VARCHAR(20)),
        JSON_ARRAY(SELECT ID FROM PURCHASES ORDER BY ID DESC RETURNING VARCHAR(10)));
VALUES JSON_OBJECT('a' VALUE 1, 'a' VALUE 2 WITH UNIQUE KEYS);

------------------------------------------
 {"id":1,"buyer":"Ada"}
 {"id":2,"buyer":"Grace"}
(2 rows)
                      |
----------------------+------------
 [1,"two",[3,4]]      | [2,1]
(1 row)
turborel: SQLCODE -301 (SQLSTATE 22030): two members of a JSON object WITH UNIQUE KEYS have the key "a" [ISO/IEC 9075-2:2023 §6.34 GR2d ii 2]

Grace's note is JSON's null, which JSON_QUERY gives as the text null and JSON_OBJECT, of it FORMAT JSON, leaves out ABSENT ON NULL. In JSON_ARRAY of a query whose FROM clause ends in a table of no correlation name, FORMAT is read as that table's correlation name: write JSON_ARRAY(SELECT J FROM T AS T FORMAT JSON), or the query in parentheses. The standard counts JSON_VALUE, JSON_QUERY and JSON_EXISTS as possibly non-deterministic, so a CHECK constraint or a generated column holds none of them, SQLCODE -103; the constructors and IS JSON may be there.

XML values

XMLPARSE makes an XML value of a string, and XMLSERIALIZE writes one as a string. XMLDOCUMENT makes a document node of an XML value, XMLCAST casts SQL values to XML and back, and XMLELEMENT, XMLFOREST, XMLCONCAT, XMLCOMMENT, XMLPI and XMLTEXT make XML of SQL values, and XMLAGG of a group's. XML Values gives their syntax.

VALUES (XMLSERIALIZE(XMLPARSE(CONTENT 'tea <b>and</b> cups' PRESERVE WHITESPACE) AS VARCHAR(40)));

------------------------------------------
 tea <b>and</b> cups
(1 row)

Conditions as values

A condition, such as SALARY > 6000 or DEPT IS NULL OR SALARY < 6000, is a value of type BOOLEAN wherever a value is written: in a select list, a VALUES row, an UPDATE's SET, a CASE result, a CAST operand, a set function's argument, or in parentheses. Its value is TRUE, FALSE, or null when it is unknown. In a comparison, a condition of its own is written in parentheses, (A = 1) = (B = 2).

CAST

CAST(value AS type) gives a value in another type, or a domain's: Data Types shows which types CAST takes to which.

CAST(value AS type FORMAT 'template') writes a datetime as a character string the template describes, or reads such a string back as a datetime. The template is a string literal of these fields, in upper case, and the delimiters - . / , ' ; : and space.

Field Means
YYYY, YYY, YY, Y the year's last digits; read, the current year's first digits before them
RRRR, RR read as YYYY and YY
MM, DD, DDD the month, the day of the month, the day of the year
HH24, HH12 or HH, A.M. or P.M. the hour on a 24-hour clock, or on a 12-hour one beside A.M. or P.M.
MI, SS, SSSSS the minute, the second, the second of the day
FF1 to FF9 so many digits of the second's fraction, truncated
TZH, TZM the time zone's hour, signed, and minute
VALUES (CAST(DATE '2024-03-05' AS VARCHAR(10) FORMAT 'DD/MM/YYYY'), CAST(TIMESTAMP '2024-03-05 14:07:09' AS VARCHAR(10) FORMAT 'HH12:MI P.M.'),
        CAST('5/3/2024' AS DATE FORMAT 'DD/MM/YYYY'), CAST('2024 065 +05:30' AS TIMESTAMP(0) WITH TIME ZONE FORMAT 'YYYY DDD TZH:TZM'));
            |            |            |
------------+------------+------------+---------------------------
 05/03/2024 | 02:07 P.M. | 2024-03-05 | 2024-03-05 00:00:00+05:30
(1 row)

A field between delimiters may be written with fewer digits, so '5/3/2024' reads as above, and one beside another field takes all its digits. A field not written is read as the current year or month, day 1, or 0. The string is read whole, its spaces kept, and a string the template does not describe, or that writes no date or time, is SQLSTATE 22007. A datetime without time zone is written and read in the session's time zone. A template that is not valid for the types, or a string type shorter than the template's least length, is refused as the statement is prepared (SQLCODE -103).

The standard: ISO/IEC 9075:1992 §6.2–§6.15 and §7.11; X3.135-1986 §5.6–§5.9; ISO/IEC 9075-2:2023 §6.29, §6.46 and §10.9 for truth values, §6.31–§6.33 for binary strings, §6.13 and §9.50–§9.52 for FORMAT, §6.14 and §9.35 for NEXT VALUE FOR.