PerfQL Functions

avg

returns the average of a series of numbers

count

to get the number of rows that match a specific condition of a query:
SELECT
   COUNT(column)
FROM
   table_name
WHERE
   condition;

max

SELECT MAX(amount)
FROM payment;

min

SELECT
   MIN (rental_rate)
FROM
   film;

string_agg

concatenates a list of strings and places a separator between them:
STRING_AGG ( expression, separator [order_by_clause] )

sum

SELECT SUM (amount) AS total
FROM payment
WHERE customer_id = 2000;

grouping

SELECT
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand;

mode

returns the most frequent input value (arbitrarily choosing the first one if there are multiple equally-frequent results):
mode() WITHIN GROUP (ORDER BY sort_expression)

percentile_cont

continuous percentile: returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed:
percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression)

percentile_disc

discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction:
percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)

stddev

historical alias for stddev_samp:
stddev(expression)

stddev_pop

population standard deviation of the input values:
stddev_pop(expression)

stddev_samp

sample standard deviation of the input values
stddev_samp(expression)

substring

the substring function returns a part of string.:
SUBSTRING ( string ,start_position , length )

trim

the TRIM() function removes the longest string that contains a specific character from a string.
By default, the TRIM() function remove spaces (‘ ‘) if you don’t specify explicitly which character that you want to remove.

convert

convert a value of one type to another

substr

extract substring (same as substring(string from from for count))

substr(string, from [, count])

coalesce

accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null.

SELECT
COALESCE (NULL, 2 , 1); -- 2

nullif

function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.

SELECT
NULLIF (1, 1); -- return NULL

SELECT
NULLIF (1, 0); -- return 1

greatest

returns the “greatest” or “largest” value from the list of expressions.

Syntax
GREATEST ( value_1, [value_n] )

least

returns the “least” or “smallest” value from the list of expressions.

Syntax
LEAST  ( value_1, [value_n] )

age

accepts two TIMESTAMP values. It subtracts the second argument from the first one and returns an interval as a result.

See the following example:

SELECT AGE('2017-01-01','2011-06-24');

          AGE
-----------------------
 5 years 6 mons 7 days

current_date

returns a DATE value that represents the current date.

current_time

returns a TIME WITH TIME ZONE value that represents the current time with time zone.

current_timestamp

returns a TIMESTAMP WITH TIME ZONE that represents the date and time at which the transaction started.

date_part

allows to retrieve subfields e.g., year, month, week from a date or time value.

date_trunc

truncates a TIMESTAMP or an  INTERVAL value based on a specified date part e.g., hour, week, or month and returns the truncated timestamp or interval with a level of precision.

extract

retrieves a field such as a year, month, and day from a date/time value.

isfinite

check if a date, a timestamp, or an interval is finite or not (not +/-infinity)

now

returns the current date and time.

SELECT NOW();
              now
-------------------------------
 2017-03-18 08:21:36.175627+07

timeofday

current date and time

timeofday()

to_timestamp

converts a string to a timestamp according to the specified format.

SELECT TO_TIMESTAMP(
    '2017-03-31 9:30:20',
    'YYYY-MM-DD HH:MI:SS');

to_timestamp
------------------------
 2017-03-31 09:30:20-07

to_char

converts a timestamp, an interval, an integer, a double precision, or a numeric value to a string.

to_date

helps convert a string to a date.

TO_DATE(text,format);

to_number

converts a character string to a numeric value.

TO_NUMBER(string, format)

abs

calculate the absolute value of a number

cbrt

calculate the cube root of a number

ceil

round a number up to the nearest integer, which is greater than or equal to number

ceiling

same as CEIL

degrees

convert radians to degrees

div

return the integer quotient of two numeric values

exp

return the exponential value in scientific notation of a number

floor

round a number down to the nearest integer, which is less than or equal to number

ln

return the natural logarithm of a numeric value

log

return the logarithm of a numeric value to a specified base

mod

divide the first parameter by the second one and return the remainder

pi

return the value of PI

power

raise a numeric value to the power of a second numeric value

radians

convert degrees to radians

round

round a number to the nearest integer or to a specified decimal places

scale

return the number of decimal digits in the fractional part

sign

return the sign (positive, negative) of a numeric value

sqrt

return the square root of a numeric value

trunc

truncate a numeric value to a whole number of to the specified decimal places

width_bucket

assign values to buckets in an equi-width histogram.

setseed

sets the internal seed for the random number generator (the function random). Allowed values are floating-point numbers between -1 and 1, which are then multiplied by 231-1.

The seed can also be set by invoking the function setseed:

SELECT setseed(value);

lower

convert a string to lowercase

upper

convert a string to uppercase

lower_inc

this function is used to check whether the lower bound inclusive or not within the specified range and return a boolean value.

Syntax:

lower_inc(anyrange)

upper_inc

this function is used to check whether the upper bound is inclusive or not within the specified range and return a boolean value.

Syntax:

upper_inc(anyrange)

lower_inf

this function is used to check whether the lower bound is infinite or not within the specified range and return a boolean value.

Syntax:

lower_inf(anyrange)

upper_inf

this function is used to check whether the upper bound is infinite or not within the specified range and return a boolean value.

Syntax:

upper_inf(anyrange)

nextval

to get the next value from the sequence to you use the nextval() function:

SELECT nextval('mysequence');

setval

reset the counter value of the sequence object. Setting the last_value field of the sequence to the specified value and its is_called field to true indicates that the next nextvalue will increment the sequence before returning the value.

currval

returns the last value of the sequence captured by nextval in the current session. (If nextval has never been invoked on this sequence in this session, an error will be reported. ) Note that because this function returns a session range value and can give a predictable result, it can be used to determine whether other sessions have executed nextval.

lastval

returns the last value returned by nextval in the current session. This function is equivalent to currval, except that it does not use the sequence name as a parameter, it grabs the sequence used by the last nextval in the current session. If the current session has not invoked nextval, calling lastval will result in an error.

generate_series

to get multiple random numbers between two integers, you use the following statement:

SELECT random_between(1,100)
FROM generate_series(1,5);

generate_subscripts

generate a series comprising the given array's subscripts.

ascii

return the ASCII code value of a character or Unicode code point of a UTF8 character

chr

convert an ASCII code to a character or a Unicode code point to a UTF8 character

concat

concatenate two or more strings into one

concat_ws

concatenate strings with a separator

format

format arguments based on a format string

initcap

convert words in a string to title case

left

return the first n character in a string

lpad

pad on the left a a string with a character to a certain length

ltrim

remove the longest string that contains specified characters from the left of the input string

parse_ident

splits qualified_identifier into an array of identifiers, removing any quoting of individual identifiers. By default, extra characters after the last identifier are considered an error; but if the second parameter is false, then such extra characters are ignored. (This behavior is useful for parsing names for objects like functions.) Note that this function does not truncate over-length identifiers. If you want truncation you can cast the result to name[].

parse_ident('"SomeSchema".someTable') → {SomeSchema,sometable}

quote_ident

returns the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled.

quote_ident('Foo bar') → "Foo bar"

quote_literal

returns the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. Note that quote_literal returns null on null input; if the argument might be null, quote_nullable is often more suitable.

quote_literal(E'O\'Reilly') → 'O''Reilly'

quote_nullable

returns the given string suitably quoted to be used as a string literal in an SQL statement string; or, if the argument is null, returns NULL. Embedded single-quotes and backslashes are properly doubled

regexp_matches

match a POSIX regular expression against a string and returns the matching substrings

regexp_replace

replace substrings that match a POSIX regular expression by a new substring

regexp_split_to_array

splits string using a POSIX regular expression as the delimiter

regexp_split_to_table

splits string using a POSIX regular expression as the delimiter

repeat

repeat string the specified number of times

replace

replace all occurrences in a string of substring from with substring to

reverse

return reversed string.

right

return last n characters in the string. When n is negative, return all but first |n| characters.

split_part

split a string on a specified delimiter and return nth substring

strpos

the PostgreSQL strpos function returns the location of a substring in a string.

starts_with

returns true if string starts with prefix.

to_ascii

converts string to ASCII from another encoding, which may be identified by name or number. If encoding is omitted the database encoding is assumed (which in practice is the only useful case). The conversion consists primarily of dropping accents. Conversion is only supported from LATIN1, LATIN2, LATIN9, and WIN1250 encodings.

to_ascii('Karél') → Karel

to_hex

converts the number to its equivalent hexadecimal representation.

to_hex(2147483647) → 7fffffff

char_length

returns number of characters in the string.

character_length

returns number of characters in the string.

row_number

it is a window function that assigns a sequential integer to each row in a result set.

ntile

allows you to divide ordered rows in the partition into a specified number of ranked groups as equal size as possible.

lag

provides access to a row that comes before the current row at a specified physical offset. In other words, from the current row the LAG() function can access data of the previous row, or the row before the previous row, and so on.

lead

provide access to a row that follows the current row at a specified physical offset.

first_value

returns a value evaluated against the first row in a sorted partition of a result set.

The following is the syntax of the FIRST_VALUE() function:

FIRST_VALUE ( expression ) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...)

last_value

returns the last value in an ordered partition of a result set.

The syntax of the LAST_VALUE() function is as follows:

LAST_VALUE ( expression ) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...)

nth_value

returns a value from the nth row in an ordered partition of a result set.

Here is the syntax of the NTH_VALUE() function:

NTH_VALUE(expression, offset)
OVER (
    [PARTITION BY partition_expression]
    [ ORDER BY sort_expression [ASC | DESC]
    frame_clause ])

rpad

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

rtrim

remove the longest string containing only characters from characters (a space by default) from the end of string

translate

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')

Length() 

It is used to find the length of a string i.e. number of characters in the given string.

dense_rank() 

Returns the rank of the current row, without gaps; this function effectively counts peer groups.

array_to_string 

This function is used to concatenate array elements using supplied delimiter and optional null string.

unnest

This function is used to expand an array to a set of rows.

jsonb_array_elements()

Expands a JSON array to a set of JSON values.