Functions and operators

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

Type cast

  • bool <-- int4
  • int1 <-- int2, int4, int8, float2, float4, float8, numeric
  • int2 <-- int1, int4, int8, float2, float4, float8, numeric
  • int4 <-- bool, int1, int2, int8, float2, float4, float8, numeric
  • int8 <-- int1, int2, int4, float2, float4, float8, numeric
  • float2 <-- int1, int2, int4, int8, float4, float8, numeric
  • float4 <-- int1, int2, int4, int8, float2, float8, numeric
  • float8 <-- int1, int2, int4, int8, float2, float4, numeric
  • numeric <-- int1, int2, int4, int8, float2, float4, float8
  • money <-- int4, int8, numeric
  • date <-- timestamp, timestamptz
  • time <-- timetz, timestamp, timestamptz
  • timetz <-- time, timestamptz
  • timestamp <-- date, timestamptz
  • timestamptz <-- date, timestamp

Numeric functions/operators

bool COMP bool
comparison operators of boolean type. COMP is any of =,<>
INT COMP INT
comparison operators of integer types.
INT is any of int1,int2,int4,int8. It is acceptable if left side and right side have different interger types.
COMP is any of =,<>,<,<=,>=,>
FP COMP FP
comparison operators of floating-point types.
FP is any of float2,float4,float8. It is acceptable if left side and right side have different floating-point types.
COMP is any of =,<>,<,<=,>=,>
numeric COMP numeric
comparison operators of numeric type.
COMP is any of =,<>,<,<=,>=,>
INT OP INT
arithemetic operators of integer types.
INT is any of int1,int2,int4,int8. It is acceptable if left side and right side have different interger types.
OP is any of +,-,*,/
FP OP FP
arithemetic operators of floating-point types.
FP is any of float2,float4,float8. It is acceptable if left side and right side have different floating-point types.
COMP is any of +,-,*,/
numeric OP numeric
comparison operators of numeric type.
OP is any of +,-,*,/
INT % INT
Reminer operator. INT is any of int1,int2,int4,int8
INT & INT
Bitwise AND operator. INT is any of int1,int2,int4,int8
INT | INT
Bitwise OR operator. INT is any of int1,int2,int4,int8
INT # INT
Bitwise XOR operator. INT is any of int1,int2,int4,int8
~ INT
Bitwise NOT operator. INT is any of int1,int2,int4,int8
INT >> int4
Right shift operator. INT is any of int1,int2,int4,int8
INT << int4
Left shift operator. INT is any of int1,int2,int4,int8
+ TYPE
Unary plus operator. TYPE is any of int1,int2,int4,int8,float2,float4,float8,numeric.
- TYPE
Unary minus operator. TYPE is any of int1,int2,int4,int8,float2,float4,float8,numeric.
@ TYPE
Absolute value. TYPE is any of int1,int2,int4,int8,float2,float4,float8,numeric.

Mathematical functions

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

Trigonometric functions

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

Date and time operators

date COMP date
comparison operators for date type. COMP is any of =,<>,<,<=,>=,>.
date COMP timestamp
timestamp COMP date
comparison operators for date and timestamp type. COMP is any of =,<>,<,<=,>=,>.
date COMP timestamptz
timestamptz COMP date
comparison operators for date and timestamptz type. COMP is any of =,<>,<,<=,>=,>.
time COMP time
comparison operators for time type. COMP is any of =,<>,<,<=,>=,>.
timetz COMP timetz
comparison operators for timetz type. COMP is any of =,<>,<,<=,>=,>.
timestamp COMP timestamp
comparison operators for timestamp type. COMP is any of =,<>,<,<=,>=,>.
timestamptz COMP timestamptz
comparison operators for timestamptz type. COMP is any of =,<>,<,<=,>=,>.
timestamp COMP timestamptz
timestamptz COMP timestamp
comparison operators for timestamp and timestamptz type. COMP is any of =,<>,<,<=,>=,>.
interval COMP interval
comparison operators for interval type. COMP is any of =,<>,<,<=,>=,>.
date + int4
int4 + date
addition operator of date type
date - int4
subtraction operator of date type
date - date
difference between date types
date + time
time + date
constructs a timestamp from date and time
date + timetz
constructs a timestamptz from date and timetz
time - time
difference between time types
timestamp - timestamp
difference between timestamp types
timetz + interval
timetz - interval
addition or subtraction operator of timetz by interval.
timestamptz + interval
timestamptz - interval
addition or subtraction operator of timestamptz by interval.
overlaps(TYPE,TYPE,TYPE,TYPE)
checks whether the 2 given time periods overlaps.
TYPE is any of time,timetz,timestamp,timestamptz.
extract(text FROM TYPE)
retrieves subfields such as day or hour from date/time values.
TYPE is any of time,timetz,timestamp,timestamptz,interval.
now()
current time of the transaction
- interval
unary minus operator of interval type
interval + interval
addition operator of interval type
interval - interval
subtraction operator of interval type

Text functions/operators

{text,bpchar} COMP {text,bpchar}
comparison operators; COMP is any of =,<>,<,<=,>=,>
Note that <,<=,>=,> operators are valid only when locale is UTF-8 or C (no locale).
varchar || varchar
concatenates the two strings.
Both side must be varchar(n) to ensure maximum length of the result being predictible.
substring(text,int4)
substring(text,int4,int4)
substr(text,int4)
substr(text,int4,int4)
extracts the substring
length({text,bpchar})
length of the string
{text,bpchar} [NOT] LIKE text
pattern-matching according to the LIKE expression
{text,bpchar} [NOT] ILIKE text
case-insensitive pattern-matching according to the LIKE expression.
Note that ILIKE operator is valid only when locale is UTF-8 or C (no locale).

Network functions/operators

macaddr COMP macaddr
comparison operators; 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
comparison operators; COMP is any of =,<>,<,<=,>=,>
inet << inet
Left side is contained by right side
inet <<= inet
Left side is contained by or equals to right side
inet >> inet
Left side contains right side
inet >>= inet
Left side contains or is equals to right side
inet && inet
Left side contains or is contained by right side
~ inet
Bitwise NOT operator
inet & inet
Bitwise AND operator
inet | inet
Bitwise OR operator
inet + int8
addition operator
inet - int8
subtraction operator
inet - inet
subtraction operator
broadcast(inet)
returns the broadcast address of the given network address
family(inet)
returns the family of the given network address; 4 for IPv4, and 6 for IPv6
hostmask(inet)
extract host mask of the given network address
masklen(inet)
extract netmask length of the given network address
netmask(inet)
extract netmask of the given network address
network(inet)
extract network part of the given network address
set_masklen(NETADDR,int)
set netmask length of the given network address; NETADDR is either inet or cidr.
inet_merge(inet,inet)
the smallest network which includes both of the given networks

Currency operators

money COMP money
comparison operators; COMP is any of =,<>,<,<=,>=,>
money OP money
arthmetric operators; OP is any of +,-,/
money * TYPE
TYPE * money
Multiply a currency with a numeric value; TYPE is any of int2,int4,float2,float4,float8
money / TYPE
Division of a currency by a numeric value; TYPE is any of int2,int4,float2,float4,float8
money / money
Division of currency values

UUID operators

uuid COMP uuid
comparison operator. COMP is any of =,<>,<,<=,>=,>

JSONB operators

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, as text
(jsonb ->> KEY)::TYPE
If 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
If 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

Note

RANGE is any of {int4range, int8range, tsrange, tstzrange, daterange} in this section.
TYPE is the element type of the RANGE which is introduced together in this section.

RANGE = RANGE
Both sides are equal.
RANGE <> RANGE
Both sides are not equal.
RANGE < RANGE
Left side is less than right side.
RANGE <= RANGE
Left side is less than or equal to right side.
RANGE > RANGE
Left side is greater than right side.
RANGE >= RANGE
Left side is greater than or equal to right side.
RANGE @ RANGE
The range in left side contains the range in right side.
RANGE @ TYPE
The range in left side contains the element in right side.
RANGE <@ RANGE
The range in left side is contained by the range in right side.
TYPE <@ RANGE
The element in left side is contained by the range in right side.
RANGE && RANGE
Left and right side are overlap (they have points in common).
RANGE << RANGE
Left side is strictly less than the right side
RANGE >> RANGE
Left side is strictly greater than the right side
RANGE &< RANGE
Any points in the left side is never greater than the right side
RANGE &> RANGE
Any points in the right side is never greater than the left side
RANGE -|- RANGE
Left side is adjacent to the right side
RANGE + RANGE
A union range by the left side and right side
RANGE * RANGE
An intersection range by the left and right side
RANGE - RANGE
An difference range by the left and right side
lower(RANGE)
lower bound of the range
upper(RANGE)
upper bound of the range
isempty(RANGE)
checks whether the range is empty
lower_inc(RANGE)
checks whether the lower bound is inclusive
upper_inc(RANGE)
checks whether the upper bound is inclusive
lower_inf(RANGE)
checks whether the lower bound is infinite
upper_inf(RANGE)
checks whether the upper bound is infinite
range_merge(RANGE,RANGE)
returns the smallest range which includes both of the given ranges

PostGIS Functions

geometry st_makepoint(float8,float8)
geometry st_point(float8,float8)
It makes 2-dimensional POINT geometry.
geometry st_makepoint(float8,float8,float8)
It makes 3-dimensional POINT geometry.
geometry st_makepoint(float8,float8,float8,float8)
It makes 4-dimensional POINT geometry.
geometry st_setsrid(geometry,int4)
It assigns SRID on the given geometry
float8 st_distance(geometry,geometry)
It returns the distance between geometries in float8.
bool 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.
text st_relate(geometry,geometry)
It checks intersection of geometries, then returns DE9-IM(Dimensionally Extended Nine-Intersection Matrix) format string.
bool st_contains(geometry,geometry)
It returns whether the geometry1 fully contains the geometry1.
bool st_crosses(geometry,geometry)
It returns whether the geometries are crossed.
int4 st_linecrossingdirection(geometry,geometry)
It checks how two LINESTRING geometries are crossing, or not crossing.