INDEX

Some common SQL functions:




Mathematical Functions

Function Return Type Description Example Result
abs( x) (same as x) absolute value abs(-17.4) 17.4
cbrt( dp) dp cube root cbrt(27.0) 3
ceil( numeric) numeric smallest integer not less than argument ceil(-42.8) -42
degrees( dp) dp radians to degrees degrees(0.5) 28.6478897565412
exp( dp) dp exponential exp(1.0) 2.71828182845905
floor( numeric) numeric largest integer not greater than argument floor(-42.8) -43
ln( dp) dp natural logarithm ln(2.0) 0.693147180559945
log( dp) dp base 10 logarithm log(100.0) 2
log( bnumeric, xnumeric) numeric logarithm to base b log(2.0, 64.0) 6.0000000000
mod( y, x) (same as argument types) remainder of y/x mod(9,4) 1
pi() dp "Pi" constant pi() 3.14159265358979
pow( edp, n dp) dp raise a number to exponent e pow(9.0, 3.0) 729
radians( dp) dp degrees to radians radians(45.0) 0.785398163397448
random() dp value between 0.0 to 1.0 random()  
round( dp) dp round to nearest integer round(42.4) 42
round( vnumeric, s integer) numeric round to s decimal places round(42.4382, 2) 42.44
sign( numeric) numeric sign of the argument (-1, 0, +1) sign(-8.4) -1
sqrt( dp) dp square root sqrt(2.0) 1.4142135623731
trunc( dp) dp truncate toward zero trunc(42.8) 42
trunc( numeric, sinteger ) numeric truncate to s decimal places trunc(42.4382, 2) 42.43



Trigonometric Function

Function Description
acos( x) inverse cosine
asin( x) inverse sine
atan( x) inverse tangent
atan2( x, y) inverse tangent of y/x
cos( x) cosine
cot( x) cotangent
sin( x) sine
tan( x) tangent




SQL String Functions and Operators

Function Return Type Description Example Result
string ||string text string concatenation 'Postgre' || 'SQL' PostgreSQL
bit_length( string) integer number of bits in string bit_length('jose') 32
char_length (string) or character_length (string) integer number of characters in string char_length('jose') 4
lower( string) text Convert string to lower case. lower('TOM') tom
octet_length (string) integer number of bytes in string octet_length('jose') 4
position( substring in string) integer location of specified substring position('om' in 'Thomas') 3
substring( string [from integer ] [for integer]) text extract substring substring('Thomas' from 2 for 3) hom
trim([ leading | trailing | both] [ characters] from string ) text Removes the longest string containing only the characters (a space by default) from the beginning/end/both ends of the string . trim(both 'x' from 'xTomxx') Tom
upper( string) text Convert string to upper case. upper('tom') TOM




Other String Functions

Function Return Type Description Example Result
ascii( text) integer Returns the ASCII code of the first character of the argument. ascii('x') 120
btrim( stringtext, trim text) text Remove (trim) the longest string consisting only of characters in trim from the start and end of string . btrim('xyxtrimyyx','xy') trim
chr( integer) text Returns the character with the given ASCII code. chr(65) A
convert( stringtext, [ src_encodingname,] dest_encodingname) text Converts string using dest_encoding. The original encoding is specified by src_encoding. If src_encoding is omitted, database encoding is assumed. convert('text_in_unicode', 'UNICODE', 'LATIN1') text_in_unicode represented in ISO 8859-1
initcap( text) text Converts first letter of each word (whitespace separated) to upper case. initcap('hi thomas') Hi Thomas
length( string) integer length of string length('jose') 4
lpad( stringtext, lengthinteger [ , filltext ]) text Fills up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right). lpad('hi', 5, 'xy') xyxhi
ltrim( stringtext, trim text) text Removes the longest string containing only characters from trim from the start of the string. ltrim('zzzytrim','xyz') trim
pg_client_encoding () name Returns current client encoding name. pg_client_encoding() SQL_ASCII
repeat( text, integer) text Repeat text a number of times. repeat('Pg', 4) PgPgPgPg
rpad( stringtext, lengthinteger [ , filltext ]) text Fills up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated. rpad('hi', 5, 'xy') hixyx
rtrim( string text, trim text) text Removes the longest string containing only characters from trim from the end of the string. rtrim('trimxxxx','x') trim
strpos( string, substring) text Locates specified substring. (same as position(substring in string), but note the reversed argument order) strpos('high','ig') 2
substr( string, from [ , count]) text Extracts specified substring. (same as substring(string from from for count)) substr('alphabet', 3, 2) ph
to_ascii( text [, encoding ]) text Converts text from multibyte encoding to ASCII. to_ascii('Karel') Karel
translate( stringtext, fromtext, totext) text Any character in string that matches a character in the from set is replaced by the corresponding character in the to set. translate('12345', '14', 'ax') a23x5
encode( databytea, typetext) text Encodes binary data to ASCII-only representation. Supported types are: 'base64', 'hex', 'escape'. encode('123\\000\\001', 'base64') MTIzAAE=
decode( stringtext, typetext) bytea Decodes binary data from string previously encoded with encode(). Parameter type is same as in encode(). decode('MTIzAAE=', 'base64') 123\000\001




SQL Binary String Functions and Operators

Function Return Type Description Example Result
string ||string bytea string concatenation '\\\\Postgre'::bytea || '\\047SQL\\000'::bytea \\Postgre'SQL\000
octet_length (string) integer number of bytes in binary string octet_length('jo\\000se'::bytea) 5
position( substring in string) integer location of specified substring position('\\000om'::bytea in 'Th\\000omas'::bytea) 3
substring( string [from integer ] [for integer]) bytea extract substring substring('Th\\000omas'::bytea from 2 for 3) h\000o
trim([ both] characters from string) bytea Removes the longest string containing only the characters from the beginning/end/both ends of the string . trim('\\000'::bytea from '\\000Tom\\000'::bytea) Tom




Other Binary String Functions

Function Return Type Description Example Result
btrim( stringbytea, trim bytea) bytea Remove (trim) the longest string consisting only of characters in trim from the start and end of string . btrim('\\000trim\\000'::bytea,'\\000'::bytea) trim
length( string) integer length of binary string length('jo\\000se'::bytea) 5
encode( stringbytea, typetext) text Encodes binary string to ASCII-only representation. Supported types are: 'base64', 'hex', 'escape'. encode('123\\000456'::bytea, 'escape') 123\000456
decode( stringtext, typetext) bytea Decodes binary string from string previously encoded with encode(). Parameter type is same as in encode(). decode('123\\000456', 'escape') 123\000456

INDEX