Skip to content
Open in Gitpod
On this page

Appendix

Data Types

typeexamples
NULLNULL
INTEGER1, -1, 1234567
FLOAT2.3, -3.56, 232.4
NUMERIC1, 2.3
BOOLEANTRUE, FALSE
BYTEA'0xaa0xbb' :: BYTEA
STRING"deadbeef"
DATEDATE '2020-06-10'
TIMETIME '11:18:30'
TIMESTAMPTIMESTAMP '2022-01-01T12:00:00+08:00'
INTERVALINTERVAL 10 SECOND
JSON'{"a": 1, "b": 2}' :: JSONB
ARRAY[1, 2, 3]

Keywords

keyworddescription
ABSabsolute value
ACOSarccosine
ACOSHinverse hyperbolic cosine
ANDlogical and operator
ARRAY_CONTAINgiven an array, checks if a search value is contained in the array
ARRAY_DISTINCTreturns an array of all the distinct values
ARRAY_EXCEPTARRAY_DISTINCT except for those also present in the second array
ARRAY_INTERSECTreturns an array of all the distinct elements from the intersection of both input arrays
ARRAY_JOINcreates a flat string representation of all elements contained in the given array
ARRAY_LENGTHreturn the length of the given array
ARRAY_MAXreturns the maximum value from the given array of primitive elements
ARRAY_MINreturns the minimum value from the given array of primitive elements
ARRAY_REMOVEremoves all elements from the input array equal to the second argument
ARRAY_SORTsort the given array
ARRAY_UNIONreturns an array of all the distinct elements from the union of both input arrays
ASstream or field name alias
ASINarcsine
ASINHinverse hyperbolic sine
ATANarctangent
ATANHinverse hyperbolic tangent
AVGaverage function
BETWEENrange operator, used with AND
BYdo something by certain conditions, used with GROUP or ORDER
CEILrounds a number UPWARDS to the nearest integer
COScosine
COSHhyperbolic cosine
COUNTcount function
CREATEcreate a stream / connector
DATEprefix of date constant
DAYinterval unit
DROPdrop a stream
EXPexponent
FLOORrounds a number DOWNWARDS to the nearest integer
FROMspecify where to select data from
GROUPgroup values by certain conditions, used with BY
HAVINGfilter select values by a condition
HOPPINGhopping window
IFNULLif the first argument is NULL returns the second, else the first
INSERTinsert data into a stream, used with INTO
INTERVALprefix of interval constant
INTOinsert data into a stream, used with INSERT
IS_ARRAYto determine if the given value is an array of values
IS_BOOLto determine if the given value is a boolean
IS_DATEto determine if the given value is a date value
IS_FLOATto determine if the given value is a float
IS_INTto determine if the given value is an integer
IS_NUMto determine if the given value is a number
IS_STRto determine if the given value is a string
IS_TIMEto determine if the given value is a time value
JOINfor joining two streams
LEFTjoining type, used with JOIN
LEFT_TRIMtrim spaces from the left end of a string
LOGlogarithm with base e
LOG10logarithm with base 10
LOG2logarithm with base 2
MAXmaximum function
MINminimum function
MINUTEinterval unit
MONTHinterval unit
NOTlogical not operator
NULLIFreturns NULL if the first argument is equal to the second, otherwise the first
ORlogical or operator
ORDERsort values by certain conditions, used with BY
OUTERjoining type, used with JOIN
REVERSEreverse a string
RIGHT_TRIMtrim spaces from the right end of a string
ROUNDrounds a number to the nearest integer
SECONDinterval unit
SELECTquery a stream
SHOWshow something to stdout
SIGNreturn the sign of a numeric value as an INTEGER
SINsine
SINHhyperbolic sine
SLIDINGsliding window
SQRTsquare root
STREAMspecify a stream, used with CREATE
STRLENget the length of a string
SUMsum function
TANtangent
TANHhyperbolic tangent
TIMEprefix of the time constant
TO_LOWERconvert a string to lowercase
TO_STRconvert a value to string
TO_UPPERconvert a string to uppercase
TRIMtrim spaces from both ends of a string
TUMBLINGtumbling window
VALUESspecify inserted data, used with INSERT INTO
WEEKinterval unit
WHEREfilter selected values by a condition
WITHspecify properties when creating a stream
WITHINspecify time window when joining two streams
YEARinterval unit

Operators

operatordescription
=equal to
<>not equal to
<less than
>greater than
<=less than or equal to
>=greater than or equal to
+addition
-subtraction
*multiplication
.access field of a stream
[]access item of an array
ANDlogical and operator
ORlogical or operator
::type casting
->JSON access(as JSON) by key
->>JSON access(as text) by key
#>JSON access(as JSON) by path
#>>JSON access(as text) by path

Scalar Functions

functiondescription
ABSabsolute value
ACOSarccosine
ACOSHinverse hyperbolic cosine
ARRAY_CONTAINgiven an array, checks if a search value is contained in the array
ARRAY_DISTINCTreturns an array of all the distinct values
ARRAY_EXCEPTARRAY_DISTINCT except for those also present in the second array
ARRAY_INTERSECTreturns an array of all the distinct elements from the intersection of both input arrays
ARRAY_JOINcreates a flat string representation of all elements contained in the given array
ARRAY_LENGTHreturn the length of the given array
ARRAY_MAXreturns the maximum value from the given array of primitive elements
ARRAY_MINreturns the minimum value from the given array of primitive elements
ARRAY_REMOVEremoves all elements from the input array equal to the second argument
ARRAY_SORTsort the given array
ARRAY_UNIONreturns an array of all the distinct elements from the union of both input arrays
ASINarcsine
ASINHinverse hyperbolic sine
ATANarctangent
ATANHinverse hyperbolic tangent
CEILrounds a number UPWARDS to the nearest integer
COScosine
COSHhyperbolic cosine
EXPexponent
FLOORrounds a number DOWNWARDS to the nearest integer
IFNULLif the first argument is NULL returns the second, else the first
NULLIFreturns NULL if the first argument is equal to the second, otherwise the first
IS_ARRAYto determine if the given value is an array of values
IS_BOOLto determine if the given value is a boolean
IS_DATEto determine if the given value is a date value
IS_FLOATto determine if the given value is a float
IS_INTto determine if the given value is an integer
IS_NUMto determine if the given value is a number
IS_STRto determine if the given value is a string
IS_TIMEto determine if the given value is a time value
LEFT_TRIMtrim spaces from the left end of a string
LOGlogarithm with base e
LOG10logarithm with base 10
LOG2logarithm with base 2
REVERSEreverse a string
RIGHT_TRIMtrim spaces from the right end of a string
ROUNDrounds a number to the nearest integer
SIGNreturn the sign of a numeric value as an INTEGER
SINsine
SINHhyperbolic sine
SQRTsquare root
STRLENget the length of a string
TANtangent
TANHhyperbolic tangent
TO_LOWERconvert a string to lowercase
TO_STRconvert a value to string
TO_UPPERconvert a string to uppercase
TOPKtopk aggregate function
TOPKDISTINCTtopkdistinct aggregate function
TRIMtrim spaces from both ends of a string

Aggregate Functions

functiondescription
AVGaverage
COUNTcount
MAXmaximum
MINminimum
SUMsum
TOPKtop k values as array
TOPKDISTINCTdistinct top k values as array