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}

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)

VCF Support Functions

text vcf_variant_getattr(text, text)

It regards the first argument as a string of tokens separated by ':' and returns the string after KEY= specified in the second argument.

=# SELECT vcf_variant_getattr('ABC=123:NM=this is a pen:XYZ=3.1415', 'NM');
 vcf_variant_getattr
---------------------
 this is a pen
(1 row)

text vcf_info_getattr(text, text)

It regards the first argument as a string of tokens separated by ';' and returns the string after KEY= specified in the second argument.

=# SELECT vcf_info_getattr('ABC=123;NM=this is a pen;XYZ=3.1415', 'XYZ');
 vcf_info_getattr
------------------
 3.141
(1 row)