Expressions are used in a variety of contexts, such as in the target list of the select command, as new column values in insertInto 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.

A HasColumn constraint indicates an unqualified column reference. getColumn can only be unambiguous when the TableExpression the column references is unique, in which case the column may be referenced using -XOverloadedLabels. Otherwise, combined with a HasTable constraint, the qualified column reference operator ! may be used.

A Column is a witness to a HasColumn constraint. It's used in unique and other TableConstraints to witness a subcolumns relationship.

Render a Column.

A GroupedBy constraint indicates that a table qualified column is a member of the auxiliary namespace created by GROUP BY clauses and thus, may be called in an output Expression without aggregating.

>>> renderExpression def
"DEFAULT"

>>> renderExpression $ unDef false
"FALSE"

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 $ unsafeBinaryOp "OR" true false
"(TRUE OR FALSE)"

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

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

>>> renderExpression @_ @_ @_ @(_ (_ 'PGfloat4)) $ atan2_ pi 2
"atan2(pi(), 2)"

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

integer division, truncates the result

>>> renderExpression @_ @_ @_ @(_(_ 'PGint2)) $ 5 `quot_` 2
"(5 / 2)"

remainder upon integer division

>>> renderExpression @_ @_ @_ @(_ (_ 'PGint2)) $ 5 `rem_` 2
"(5 % 2)"

>>> renderExpression @_ @_ @_ @(_ (_ 'PGfloat4)) $ trunc pi
"trunc(pi())"

>>> renderExpression @_ @_ @_ @(_ (_ 'PGfloat4)) $ round_ pi
"round(pi())"

>>> renderExpression @_ @_ @_ @(_ (_ 'PGfloat4)) $ ceiling_ pi
"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)"

>>> renderExpression @_ @_ @_ @(_ (_ 'PGint2)) $ caseWhenThenElse [(true, 1), (false, 2)] 3
"CASE WHEN TRUE THEN 1 WHEN FALSE THEN 2 ELSE 3 END"

>>> renderExpression @_ @_ @_ @(_ (_ 'PGint2)) $ ifThenElse true 1 0
"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

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGnumeric)]] $ sum_ #col
"sum(col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGnumeric)]] $ sumDistinct #col
"sum(DISTINCT col)"

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

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGint4)]] $ bitAnd #col
"bit_and(col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGint4)]] $ bitOr #col
"bit_or(col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGbool)]] $ boolAnd #col
"bool_and(col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGbool)]] $ boolOr #col
"bool_or(col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGint4)]] $ bitAndDistinct #col
"bit_and(DISTINCT col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGint4)]] $ bitOrDistinct #col
"bit_or(DISTINCT col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGbool)]] $ boolAndDistinct #col
"bool_and(DISTINCT col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGbool)]] $ boolOrDistinct #col
"bool_or(DISTINCT col)"

A special aggregation that does not require an input

>>> renderExpression countStar
"count(*)"

>>> renderExpression @'[_ ::: '["col" ::: 'Optional _]] $ count #col
"count(col)"

>>> renderExpression @'[_ ::: '["col" ::: 'Required _]] $ countDistinct #col
"count(DISTINCT col)"

synonym for boolAnd

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGbool)]] $ every #col
"every(col)"

synonym for boolAndDistinct

>>> renderExpression @'[_ ::: '["col" ::: 'Required (_ 'PGbool)]] $ everyDistinct #col
"every(DISTINCT col)"

minimum and maximum aggregation

minimum and maximum aggregation

minimum and maximum aggregation

minimum and maximum aggregation

A Table from a schema without its alias with an IsLabel instance to call a table reference by its alias.

A HasTable constraint indicates a table reference.

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

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 ]