Functions and operators

This chapter introduces the functions and operators executable on GPU devices.

Type cast

destination type	source type	description
`bool`	`int4`
`int2`	`int4,int8,float2,float4,float8,numeric`
`int4`	`int2,int8,float2,float4,float8,numeric`
`int8`	`int2,int4,float2,float4,float8,numeric`
`float2`	`int2,int4,int8,float4,float8,numeric`
`float4`	`int2,int4,int8,float2,float8,numeric`
`float8`	`int2,int4,int8,float2,float4,numeric`
`numeric`	`int2,int4,int8,float2,float4,float8`
`money`	`int4,int8,numeric`
`inet`	`cidr`
`date`	`timestamp,timestamptz`
`time`	`timetz,timestamp,timestamptz`
`timetz`	`time,timestamptz`
`timestamp`	`date,timestamptz`
`timestamptz`	`date,timestamp`

Numeric functions/operators

function/operator	description
`TYPE COMP TYPE`	Comparison of two values `TYPE` is any of `int2,int4,int8` `COMP` is any of `=,<>,<,<=,>=,>`
`TYPE COMP TYPE`	Comparison of two values `TYPE` is any of `float2,float4,float8` `COMP` is any of `=,<>,<,<=,>=,>`
`numeric COMP numeric`	Comparison of two values `COMP` is any of `=,<>,<,<=,>=,>`
`TYPE + TYPE`	Arithemetic addition `TYPE` is any of `int2,int4,int8,float2,float4,float8,numeric`
`TYPE - TYPE`	Arithemetic substract `TYPE` is any of `int2,int4,int8,float2,float4,float8,numeric`
`TYPE * TYPE`	Arithemetic multiplication `TYPE` is any of `int2,int4,int8,float2,float4,float8,numeric`
`TYPE / TYPE`	Arithemetic division `TYPE` is any of `int2,int4,int8,float2,float4,float8,numeric`
`TYPE % TYPE`	Reminer operator `TYPE` is any of `int2,int4,int8`
`TYPE & TYPE`	Bitwise AND `TYPE` is any of `int2,int4,int8`
`TYPE \| TYPE`	Bitwise OR `TYPE` is any of `int2,int4,int8`
`TYPE # TYPE`	Bitwise XOR `TYPE` is any of `int2,int4,int8`
`~ TYPE`	Bitwise NOT `TYPE` is any if `int2,int4,int8`
`TYPE >> int4`	Right shift `TYPE` is any of `int2,int4,int8`
`TYPE << int4`	Left shift `TYPE` is any of `int2,int4,int8`
`+ TYPE`	Unary plus `TYPE` is any of `int2,int4,int8,float2,float4,float8,numeric`
`- TYPE`	Unary minus `TYPE` is any of `int2,int4,int8,float2,float4,float8,numeric`
`@TYPE`	Absolute value `TYPE` is any of `int2,int4,int8,float2,float4,float8,numeric`

Mathematical functions

functions/operators	description
`cbrt(float8)`	cube root
`dcbrt(float8)`	cube root
`ceil(float8)`	nearest integer greater than or equal to argument
`ceiling(float8)`	nearest integer greater than or equal to argument
`exp(float8)`	exponential
`dexp(float8)`	exponential
`floor(float8)`	nearest integer less than or equal to argument
`ln(float8)`	natural logarithm
`dlog1(float8)`	natural logarithm
`log(float8)`	base 10 logarithm
`dlog10(float8)`	base 10 logarithm
`pi()`	circumference ratio
`power(float8,float8)`	power
`pow(float8,float8)`	power
`dpow(float8,float8)`	power
`round(float8)`	round to the nearest integer
`dround(float8)`	round to the nearest integer
`sign(float8)`	sign of the argument
`sqrt(float8)`	square root
`dsqrt(float8)`	square root
`trunc(float8)`	truncate toward zero
`dtrunc(float8)`	truncate toward zero

Trigonometric functions

functions/operators	description
`degrees(float8)`	radians to degrees
`radians(float8)`	degrees to radians
`acos(float8)`	inverse cosine
`asin(float8)`	inverse sine
`atan(float8)`	inverse tangent
`atan2(float8,float8)`	inverse tangent of `arg1 / arg2`
`cos(float8)`	cosine
`cot(float8)`	cotangent
`sin(float8)`	sine
`tan(float8)`	tangent

Date and time operators

functions/operators	description
`date COMP date`	`COMP` is any of `=,<>,<,<=,>=,>`
`date COMP timestamp`	`COMP` is any of `=,<>,<,<=,>=,>`
`date COMP timestamptz`	`COMP` is any of `=,<>,<,<=,>=,>`
`time COMP time`	`COMP` is any of `=,<>,<,<=,>=,>`
`timetz COMP timetz`	`COMP` is any of `=,<>,<,<=,>=,>`
`timestamp COMP timestamp`	`COMP` is any of `=,<>,<,<=,>=,>`
`timestamp COMP date`	`COMP` is any of `=,<>,<,<=,>=,>`
`timestamptz COMP timestamptz`	`COMP` is any of `=,<>,<,<=,>=,>`
`timestamptz COMP date`	`COMP` is any of `=,<>,<,<=,>=,>`
`timestamp COMP timestamptz`	`COMP` is any of `=,<>,<,<=,>=,>`
`timestamptz COMP timestamp`	`COMP` is any of `=,<>,<,<=,>=,>`
`interval COMP interval`	`COMP` is any of `=,<>,<,<=,>=,>`
`date OP int4`	`OP` is either of `+,-`
`int4 + date`
`date - date`
`date + time`
`date + timetz`
`time + date`
`time - time`
`timestamp - timestamp`
`timetz OP interval`	`OP` is either of `+,-`
`timestamptz OP interval`	`OP` is either of `+,-`
`overlaps(TYPE,TYPE,TYPE,TYPE)`	`TYPE` is any of `time,timetz,timestamp,timestamptz`
`extract(text FROM TYPE)`	`TYPE` is any of `time,timetz,timestamp,timestamptz,interval`
`now()`
`- interval`	unary minus operator
`interval OP interval`	`OP` is either of `+,-`

Text functions/operators

functions/operators	description
`{text,bpchar} COMP {text,bpchar}`	`COMP` is either of `=,<>`
`{text,bpchar} COMP {text,bpchar}`	`COMP` is either of `<,<=,>=,>` Only available on no-locale or UTF-8
`varchar \|\| varchar`	Both side must be `varchar(n)` with maximum length.
`substring`, `substr`
`length(TYPE)`	length of the string `TYPE` is either of `text,bpchar`
`TYPE LIKE text`	`TYPE` is either of `text,bpchar`
`TYPE NOT LIKE text`	`TYPE` is either of `text,bpchar`
`TYPE ILIKE text`	`TYPE` is either of `text,bpchar` Only available on no-locale or UTF-8
`TYPE NOT ILIKE text`	`TYPE` is either of `text,bpchar` Only available on no-locale or UTF-8

Network functions/operators

functions/operators	description
`macaddr COMP macaddr`	`COMP` is any of `=,<>,<,<=,>=,>`
`macaddr & macaddr`	Bitwise AND operator
`macaddr \| macaddr`	Bitwise OR operator
`~ macaddr`	Bitwise NOT operator
`trunc(macaddr)`	Set last 3 bytes to zero
`inet COMP inet`	`COMP` is any of `=,<>,<,<=,>=,>`
`inet INCL inet`	`INCL` is any of `<<,<<=,>>,>>=,&&`
`~ inet`
`inet & inet`
`inet \| inet`
`inet + int8`
`inet - int8`
`inet - inet`
`broadcast(inet)`
`family(inet)`
`hostmask(inet)`
`masklen(inet)`
`netmask(inet)`
`network(inet)`
`set_masklen(cidr,int)`
`set_masklen(inet,int)`
`inet_same_family(inet, inet)`
`inet_merge(inet,inet)`

Currency operators

functions/operators	description
`money COMP money`	`COMP` is any of `=,<>,<,<=,>=,>`
`money OP money`	`OP` is any of `+,-,/`
`money * TYPE`	`TYPE` is any of `int2,int4,float2,float4,float8`
`TYPE * money`	`TYPE` is any of `int2,int4,float2,float4,float8`
`money / TYPE`	`TYPE` is any of `int2,int4,float2,float4,float8`

UUID operators

functions/operators	description
`uuid COMP uuid`	`COMP` is any of `=,<>,<,<=,>=,>`

JSONB operators

functions/operators	description
`jsonb -> KEY`	Get a JSON object field specified by the `KEY`
`jsonb -> NUM`	Get a JSON array element indexed by `NUM`
`jsonb ->> KEY`	Get a JSON object field specified by the `KEY`, as text
`jsonb ->> NUM`	Get a JSON array element indexed by `NUM`
`(jsonb ->> KEY)::TYPE`	TYPE is any of `int2,int4,int8,float4,float8,numeric` Get a JSON object field specified by `KEY`, as numeric data type. See the note below.
`(jsonb ->> NUM)::TYPE`	TYPE is any of `int2,int4,int8,float4,float8,numeric` Get a JSON array element indexed by `NUM`, as numeric data type. See the note below.
`jsonb ? KEY`	Check whether jsonb object contains the `KEY`

Note

When we convert a jsonb element fetched by jsonb ->> KEY operator into numerical data types like float or numeric, PostgreSQL takes 2 steps operations; an internal numerical form is printed as text first, then it is converted into numerical data type. PG-Strom optimizes the GPU code using a special device function to fetch a numerical datum from jsonb object/array, if jsonb ->> KEY operator and text-to-numeric case are continuously used.

Range type functions/operators

functions/operators	description
`RANGE = RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE <> RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE < RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE <= RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE > RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE >= RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE @ RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE @ TYPE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange` `TYPE` is element type of `RANGE`.
`RANGE <@RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`TYPE <@RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange` `TYPE` is element type of `RANGE`.
`RANGE && RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE << RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE >> RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE &< RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE &> RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE -\|- RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE + RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE * RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`RANGE - RANGE`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`lower(RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`upper(RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`isempty(RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`lower_inc(RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`upper_inc(RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`lower_inf(RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`upper_inf(RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`
`range_merge(RANGE,RANGE)`	`RANGE` is any of `int4range,int8range,tsrange,tstzrange,daterange`

PostGIS Functions

functions/operators	description
`st_makepoint(float8,float8)`	It makes 2-dimensional POINT geometry.
`st_makepoint(float8,float8,float8)`	It makes 3-dimensional POINT geometry.
`st_makepoint(float8,float8,float8,float8)`	It makes 4-dimensional POINT geometry.
`st_distance(geometry,geometry)`	It returns the distance between geometries in `float8`.
`st_dwithin(geometry,geometry,float8)`	It returns `true` if the distance between geometries is shorter than the specified threshold. It is often faster than the combination of `st_distance` and comparison operator.
`st_relate(geometry,geometry)`	It checks intersection of geometries, then returns DE9-IM(Dimensionally Extended Nine-Intersection Matrix) format string.
`st_contains(geometry,geometry)`	It returns whether the geometry1 fully contains the geometry1.
`st_crosses(geometry,geometry)`	It returns whether the geometries are crossed.
`st_linecrossingdirection(geometry,geometry)`