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.

analagous to Nothing

>>> renderExpression $ null_
"NULL"

analagous to Just

>>> renderExpression $ unNull true
"TRUE"

return the leftmost value which is not NULL

>>> renderExpression $ coalesce [null_, unNull true] false
"COALESCE(NULL, TRUE, FALSE)"

analagous to fromMaybe using COALESCE

>>> renderExpression $ fromNull true null_
"COALESCE(NULL, TRUE)"

>>> renderExpression $ null_ & isNull
"NULL IS NULL"

>>> renderExpression $ null_ & isn'tNull
"NULL IS NOT NULL"

analagous to maybe using IS NULL

>>> renderExpression $ 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
>>> renderExpression @_ @_ @'[_] $ fromNull false (nullIf false (param @1))
"COALESCE(NULL IF (FALSE, ($1 :: bool)), FALSE)"

>>> renderExpression $ array [null_, unNull false, unNull true]
"ARRAY[NULL, FALSE, TRUE]"

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

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

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

>>> :{
let
  expression :: Expression relations grouping params (nullity 'PGfloat4)
  expression = atan2_ pi 2
in renderExpression expression
:}
"atan2(pi(), 2)"

>>> renderExpression $ true & cast int4
"(TRUE :: int4)"

integer division, truncates the result

>>> :{
let
  expression :: Expression relations grouping params (nullity 'PGint2)
  expression = 5 `quot_` 2
in renderExpression expression
:}
"(5 / 2)"

remainder upon integer division

>>> :{
let
  expression :: Expression relations grouping params (nullity 'PGint2)
  expression = 5 `rem_` 2
in renderExpression expression
:}
"(5 % 2)"

>>> :{
let
  expression :: Expression relations grouping params (nullity 'PGfloat4)
  expression = trunc pi
in renderExpression expression
:}
"trunc(pi())"

>>> :{
let
  expression :: Expression relations grouping params (nullity 'PGfloat4)
  expression = round_ pi
in renderExpression expression
:}
"round(pi())"

>>> :{
let
  expression :: Expression relations grouping params (nullity 'PGfloat4)
  expression = ceiling_ pi
in renderExpression expression
:}
"ceiling(pi())"

>>> renderExpression @_ @_ @'[_] $ greatest currentTimestamp [param @1]
"GREATEST(CURRENT_TIMESTAMP, ($1 :: timestamp with time zone))"

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

A Condition is a boolean valued Expression. While SQL allows conditions to have NULL, Squeal instead chooses to disallow NULL, forcing one to handle the case of NULL explicitly to produce a Condition.

>>> renderExpression true
"TRUE"

>>> renderExpression false
"FALSE"

>>> renderExpression $ not_ true
"(NOT TRUE)"

>>> renderExpression $ true .&& false
"(TRUE AND FALSE)"

>>> renderExpression $ true .|| false
"(TRUE OR FALSE)"

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

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

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

>>> renderExpression $ unNull true .== null_
"(TRUE = NULL)"

>>> renderExpression $ unNull true ./= null_
"(TRUE <> NULL)"

>>> renderExpression $ unNull true .>= null_
"(TRUE >= NULL)"

>>> renderExpression $ unNull true .< null_
"(TRUE < NULL)"

>>> renderExpression $ unNull true .<= null_
"(TRUE <= NULL)"

>>> renderExpression $ unNull true .> null_
"(TRUE > NULL)"

>>> renderExpression currentDate
"CURRENT_DATE"

>>> renderExpression currentTime
"CURRENT_TIME"

>>> renderExpression currentTimestamp
"CURRENT_TIMESTAMP"

>>> renderExpression localTime
"LOCALTIME"

>>> renderExpression localTimestamp
"LOCALTIMESTAMP"

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

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

>>> renderExpression $ 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.

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

escape hatch to define aggregate functions

escape hatch to define aggregate functions over distinct values

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

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

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

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

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

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

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

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

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

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

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

A special aggregation that does not require an input

>>> renderExpression countStar
"count(*)"

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

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

synonym for boolAnd

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

synonym for boolAndDistinct

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

minimum and maximum aggregation

minimum and maximum aggregation

minimum and maximum aggregation

minimum and maximum aggregation

A Table from a table expression is a way to call a table reference by its alias.

TypeExpressions are used in casts and createTable commands.

pgtype is a demoted version of a PGType

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)

not a true type, but merely a notational convenience for creating unique identifier columns with type `PGint2

not a true type, but merely a notational convenience for creating unique identifier columns with type `PGint2

not a true type, but merely a notational convenience for creating unique identifier columns with type `PGint4

not a true type, but merely a notational convenience for creating unique identifier columns with type `PGint4

not a true type, but merely a notational convenience for creating unique identifier columns with type `PGint8

not a true type, but merely a notational convenience for creating unique identifier columns with type `PGint8

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 (Proxy @2) json)
"json[2]"

used in createTable commands as a column constraint to ensure NULL is not present

used in createTable commands as a column constraint to give a default

& 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 $.

Since: 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 ]