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 valuesTYPE is any of int2,int4,int8 COMP is any of =,<>,<,<=,>=,> |
TYPE COMP TYPE |
Comparison of two valuesTYPE is any of float2,float4,float8 COMP is any of =,<>,<,<=,>=,> |
numeric COMP numeric |
Comparison of two valuesCOMP is any of =,<>,<,<=,>=,> |
TYPE + TYPE |
Arithemetic additionTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE - TYPE |
Arithemetic substractTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE * TYPE |
Arithemetic multiplicationTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE / TYPE |
Arithemetic divisionTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE % TYPE |
Reminer operatorTYPE is any of int2,int4,int8 |
TYPE & TYPE |
Bitwise ANDTYPE is any of int2,int4,int8 |
TYPE | TYPE |
Bitwise ORTYPE is any of int2,int4,int8 |
TYPE # TYPE |
Bitwise XORTYPE is any of int2,int4,int8 |
~ TYPE |
Bitwise NOTTYPE is any if int2,int4,int8 |
TYPE >> int4 |
Right shiftTYPE is any of int2,int4,int8 |
TYPE << int4 |
Left shiftTYPE is any of int2,int4,int8 |
+ TYPE |
Unary plusTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
- TYPE |
Unary minusTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
@TYPE |
Absolute valueTYPE 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 stringTYPE 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) |