Expressions are used in a variety of contexts, such as in the target list of the select command, as new column values in insertRow or update, or in search Conditions in a number of commands.

The expression syntax allows the calculation of values from primitive expression using arithmetic, logical, and other operations.

A HasParameter constraint is used to indicate a value that is supplied externally to a SQL statement. manipulateParams, queryParams and traversePrepared support specifying data values separately from the SQL command string, in which case params are used to refer to the out-of-line data values.

param takes a Nat using type application and for basic types, infers a TypeExpression.

>>> let expr = param @1 :: Expression sch rels grp '[ 'Null 'PGint4] ('Null 'PGint4)
>>> printSQL expr
($1 :: int4)

analagous to Nothing

>>> printSQL null_
NULL

analagous to Just

>>> printSQL $ notNull true
TRUE

return the leftmost value which is not NULL

>>> printSQL $ coalesce [null_, true] false
COALESCE(NULL, TRUE, FALSE)

analagous to fromMaybe using COALESCE

>>> printSQL $ fromNull true null_
COALESCE(NULL, TRUE)

>>> printSQL $ null_ & isNull
NULL IS NULL

>>> printSQL $ null_ & isNotNull
NULL IS NOT NULL

analagous to maybe using IS NULL

>>> printSQL $ matchNull true not_ null_
CASE WHEN NULL IS NULL THEN TRUE ELSE (NOT NULL) END

right inverse to fromNull, if its arguments are equal then nullIf gives NULL.

>>> :set -XTypeApplications -XDataKinds
>>> let expr = nullIf false (param @1) :: Expression schema rels grp '[ 'NotNull 'PGbool] ('Null 'PGbool)
>>> printSQL expr
NULL IF (FALSE, ($1 :: bool))

>>> printSQL $ array [null_, false, true]
ARRAY[NULL, FALSE, TRUE]

>>> printSQL $ array [null_, false, true] & index 2
(ARRAY[NULL, FALSE, TRUE])[2]

A row constructor is an expression that builds a row value (also called a composite value) using values for its member fields.

>>> :{
type Complex = 'PGcomposite
  '[ "real"      ::: 'NotNull 'PGfloat8
   , "imaginary" ::: 'NotNull 'PGfloat8 ]
:}

>>> let i = row (0 `as` #real :* 1 `as` #imaginary) :: Expression '[] '[] 'Ungrouped '[] ('NotNull Complex)
>>> printSQL i
ROW(0, 1)

>>> :{
type Complex = 'PGcomposite
  '[ "real"      ::: 'NotNull 'PGfloat8
   , "imaginary" ::: 'NotNull 'PGfloat8 ]
:}

>>> let i = row (0 `as` #real :* 1 `as` #imaginary) :: Expression '["complex" ::: 'Typedef Complex] '[] 'Ungrouped '[] ('NotNull Complex)
>>> printSQL $ i & field #complex #imaginary
(ROW(0, 1)::"complex")."imaginary"

>>> printSQL $ unsafeBinaryOp "OR" true false
(TRUE OR FALSE)

>>> printSQL $ unsafeUnaryOp "NOT" true
(NOT TRUE)

>>> printSQL $ unsafeFunction "f" true
f(TRUE)

Helper for defining variadic functions.

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGfloat4)
  expression = atan2_ pi 2
in printSQL expression
:}
atan2(pi(), 2)

>>> printSQL $ true & cast int4
(TRUE :: int4)

integer division, truncates the result

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGint2)
  expression = 5 `quot_` 2
in printSQL expression
:}
(5 / 2)

remainder upon integer division

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGint2)
  expression = 5 `rem_` 2
in printSQL expression
:}
(5 % 2)

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGfloat4)
  expression = trunc pi
in printSQL expression
:}
trunc(pi())

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGfloat4)
  expression = round_ pi
in printSQL expression
:}
round(pi())

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGfloat4)
  expression = ceiling_ pi
in printSQL expression
:}
ceiling(pi())

>>> let expr = greatest currentTimestamp [param @1] :: Expression sch rels grp '[ 'NotNull 'PGtimestamptz] ('NotNull 'PGtimestamptz)
>>> printSQL expr
GREATEST(CURRENT_TIMESTAMP, ($1 :: timestamp with time zone))

>>> printSQL $ least currentTimestamp [null_]
LEAST(CURRENT_TIMESTAMP, NULL)

>>> printSQL true
TRUE

>>> printSQL false
FALSE

>>> printSQL $ not_ true
(NOT TRUE)

>>> printSQL $ true .&& false
(TRUE AND FALSE)

>>> printSQL $ true .|| false
(TRUE OR FALSE)

A Condition is an Expression, which can evaluate to true, false or null_. This is because SQL uses a three valued logic.

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGint2)
  expression = caseWhenThenElse [(true, 1), (false, 2)] 3
in printSQL expression
:}
CASE WHEN TRUE THEN 1 WHEN FALSE THEN 2 ELSE 3 END

>>> :{
let
  expression :: Expression schema from grouping params (nullity 'PGint2)
  expression = ifThenElse true 1 0
in printSQL expression
:}
CASE WHEN TRUE THEN 1 ELSE 0 END

Comparison operations like .==, ./=, .>, .>=, .< and .<= will produce NULLs if one of their arguments is NULL.

>>> printSQL $ true .== null_
(TRUE = NULL)

>>> printSQL $ true ./= null_
(TRUE <> NULL)

>>> printSQL $ true .>= null_
(TRUE >= NULL)

>>> printSQL $ true .< null_
(TRUE < NULL)

>>> printSQL $ true .<= null_
(TRUE <= NULL)

>>> printSQL $ true .> null_
(TRUE > NULL)

>>> printSQL currentDate
CURRENT_DATE

>>> printSQL currentTime
CURRENT_TIME

>>> printSQL currentTimestamp
CURRENT_TIMESTAMP

>>> printSQL localTime
LOCALTIME

>>> printSQL localTimestamp
LOCALTIMESTAMP

>>> printSQL $ lower "ARRRGGG"
lower(E'ARRRGGG')

>>> printSQL $ upper "eeee"
upper(E'eeee')

>>> printSQL $ charLength "four"
char_length(E'four')

The like expression returns true if the string matches the supplied pattern. If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case like acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

>>> printSQL $ "abc" `like` "a%"
(E'abc' LIKE E'a%')

Get JSON value (object field or array element) at a key.

Get JSON value (object field or array element) at a key, as text.

Get JSON value at a specified path.

Get JSON value at a specified path as text.

Does the left JSON value contain the right JSON path/value entries at the top level?

Are the left JSON path/value entries contained at the top level within the right JSON value?

Does the string exist as a top-level key within the JSON value?

Do any of these array strings exist as top-level keys?

Do all of these array strings exist as top-level keys?

Delete a key or keys from a JSON object, or remove an array element.

If the right operand is..

text : Delete keyvalue pair or string element from left operand. Keyvalue pairs are matched based on their key value.

text[] : Delete multiple key/value pairs or string elements from left operand. Key/value pairs are matched based on their key value.

integer : Delete the array element with specified index (Negative integers count from the end). Throws an error if top level container is not an array.

Delete the field or element with specified path (for JSON arrays, negative integers count from the end)

Literal JSON

Literal binary JSON

Returns the value as json. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json value.

Returns the value as jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid jsonb value.

Returns the array as a JSON array. A PostgreSQL multidimensional array becomes a JSON array of arrays.

Returns the row as a JSON object.

Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list.

Builds a possibly-heterogeneously-typed (binary) JSON array out of a variadic argument list.

Builds a possibly-heterogeneously-typed JSON object out of a variadic argument list. The elements of the argument list must alternate between text and values.

Builds a possibly-heterogeneously-typed (binary) JSON object out of a variadic argument list. The elements of the argument list must alternate between text and values.

Builds a JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs, or two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.

Builds a binary JSON object out of a text array. The array must have either exactly one dimension with an even number of members, in which case they are taken as alternating key/value pairs, or two dimensions such that each inner array has exactly two elements, which are taken as a key/value pair.

This is an alternate form of jsonObject that takes two arrays; one for keys and one for values, that are zipped pairwise to create a JSON object.

This is an alternate form of jsonObject that takes two arrays; one for keys and one for values, that are zipped pairwise to create a binary JSON object.

Returns the number of elements in the outermost JSON array.

Returns the number of elements in the outermost binary JSON array.

Returns JSON value pointed to by the given path (equivalent to #> operator).

Returns JSON value pointed to by the given path (equivalent to #> operator).

Returns JSON value pointed to by the given path (equivalent to #> operator), as text.

Returns JSON value pointed to by the given path (equivalent to #> operator), as text.

Returns the type of the outermost JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

Returns the type of the outermost binary JSON value as a text string. Possible types are object, array, string, number, boolean, and null.

Returns its argument with all object fields that have null values omitted. Other null values are untouched.

Returns its argument with all object fields that have null values omitted. Other null values are untouched.

 jsonbSet target path new_value create_missing

Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true ( default is true) and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

 jsonbInsert target path new_value insert_after

Returns target with new_value inserted. If target section designated by path is in a JSONB array, new_value will be inserted before target or after if insert_after is true (default is false). If target section designated by path is in JSONB object, new_value will be inserted only if target does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.

Returns its argument as indented JSON text.

escape hatch to define aggregate functions

escape hatch to define aggregate functions over distinct values

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: 'Null 'PGnumeric]] ('Grouped bys) params ('Null 'PGnumeric)
  expression = sum_ #col
in printSQL expression
:}
sum("col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGnumeric]] ('Grouped bys) params (nullity 'PGnumeric)
  expression = sumDistinct #col
in printSQL expression
:}
sum(DISTINCT "col")

A constraint for PGTypes that you can take averages of and the resulting PGType.

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGint4]] (Grouped bys) params (nullity 'PGint4)
  expression = bitAnd #col
in printSQL expression
:}
bit_and("col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGint4]] (Grouped bys) params (nullity 'PGint4)
  expression = bitOr #col
in printSQL expression
:}
bit_or("col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGbool]] (Grouped bys) params (nullity 'PGbool)
  expression = boolAnd #col
in printSQL expression
:}
bool_and("col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGbool]] (Grouped bys) params (nullity 'PGbool)
  expression = boolOr #col
in printSQL expression
:}
bool_or("col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGint4]] (Grouped bys) params (nullity 'PGint4)
  expression = bitAndDistinct #col
in printSQL expression
:}
bit_and(DISTINCT "col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGint4]] (Grouped bys) params (nullity 'PGint4)
  expression = bitOrDistinct #col
in printSQL expression
:}
bit_or(DISTINCT "col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGbool]] (Grouped bys) params (nullity 'PGbool)
  expression = boolAndDistinct #col
in printSQL expression
:}
bool_and(DISTINCT "col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGbool]] (Grouped bys) params (nullity 'PGbool)
  expression = boolOrDistinct #col
in printSQL expression
:}
bool_or(DISTINCT "col")

A special aggregation that does not require an input

>>> printSQL countStar
count(*)

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity ty]] (Grouped bys) params ('NotNull 'PGint8)
  expression = count #col
in printSQL expression
:}
count("col")

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity ty]] (Grouped bys) params ('NotNull 'PGint8)
  expression = countDistinct #col
in printSQL expression
:}
count(DISTINCT "col")

synonym for boolAnd

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGbool]] (Grouped bys) params (nullity 'PGbool)
  expression = every #col
in printSQL expression
:}
every("col")

synonym for boolAndDistinct

>>> :{
let
  expression :: Expression schema '[tab ::: '["col" ::: nullity 'PGbool]] (Grouped bys) params (nullity 'PGbool)
  expression = everyDistinct #col
in printSQL expression
:}
every(DISTINCT "col")

minimum and maximum aggregation

minimum and maximum aggregation

minimum and maximum aggregation

minimum and maximum aggregation

TypeExpressions are used in casts and createTable commands.

pgtype is a demoted version of a PGType

The enum or composite type in a Typedef can be expressed by its alias.

The composite type corresponding to a Table definition can be expressed by its alias.

The composite type corresponding to a View definition can be expressed by its alias.

logical Boolean (true/false)

signed two-byte integer

signed two-byte integer

signed four-byte integer

signed four-byte integer

signed four-byte integer

signed eight-byte integer

signed eight-byte integer

arbitrary precision numeric type

single precision floating-point number (4 bytes)

single precision floating-point number (4 bytes)

double precision floating-point number (8 bytes)

double precision floating-point number (8 bytes)

variable-length character string

fixed-length character string

fixed-length character string

variable-length character string

variable-length character string

binary data ("byte array")

date and time (no time zone)

date and time, including time zone

calendar date (year, month, day)

time of day (no time zone)

time of day, including time zone

time span

universally unique identifier

IPv4 or IPv6 host address

textual JSON data

binary JSON data, decomposed

variable length array

fixed length array

>>> renderTypeExpression (fixarray @2 json)
"json[2]"

& is a reverse application operator. This provides notational convenience. Its precedence is one higher than that of the forward application operator $, which allows & to be nested in $.

>>> 5 & (+1) & show
"6"

Since: base-4.8.0.0

An n-ary product.

The product is parameterized by a type constructor f and indexed by a type-level list xs. The length of the list determines the number of elements in the product, and if the i-th element of the list is of type x, then the i-th element of the product is of type f x.

The constructor names are chosen to resemble the names of the list constructors.

Two common instantiations of f are the identity functor I and the constant functor K. For I, the product becomes a heterogeneous list, where the type-level list describes the types of its components. For K a, the product becomes a homogeneous list, where the contents of the type-level list are ignored, but its length still specifies the number of elements.

In the context of the SOP approach to generic programming, an n-ary product describes the structure of the arguments of a single data constructor.

Examples:

I 'x'    :* I True  :* Nil  ::  NP I       '[ Char, Bool ]
K 0      :* K 1     :* Nil  ::  NP (K Int) '[ Char, Bool ]
Just 'x' :* Nothing :* Nil  ::  NP Maybe   '[ Char, Bool ]