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).}
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.

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 geometry2.
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.

CUBE Type Functions

cube COMP cube
comparison operators; COMP is any of =,<>,<,<=,>=,>}
bool cube_contains(cube, cube)
It returns whether the first cube fully contains the second cube.
bool cube_contained(cube, cube)
It returns whether the first cube is fully contained by the second cube.
float8 cube_ll_coord(cube, int4)