PostgreSQL Reference

PostgreSQL Functions

Aggregates

See the official docs for more on all functions in this category. The table immediately below provides you the most commonly used PostgreSQL functions as considred on PostgreSQL Tutorial. Links to these functions take you to the function's reference on the PostgreSQL Tutorial site. Underneath the table below is a reproduction of what currently exists in the Postgres docs in a more palatable form.

Function	Description
COUNT()	Count the number of rows in a table
AVG()	Calculate the average value of a set
SUM()	Calculate the sum of a set of values
MAX()	Get the maximum value of a set
MIN()	Get the minimum value of a set
STRING_AGG()	Concatenate strings and place a separator between them
ARRAY_AGG	Return an array from a set of input values

The tables below all come from the Postgres docs.

• General-Purpose Aggregate Functions
• Aggregate Functions for Statistics
• Ordered-Set Aggregate Functions
• Hypothetical-Set Aggregate Functions
• Grouping Operations

General-Purpose Aggregate Functions

Syntax	array_agg ( anynonarray ) → anyarray
Description	Collects all the input values, including nulls, into an array.
Partial mode	No

Syntax	array_agg ( anyarray ) → anyarray
Description	Concatenates all the input arrays into an array of one higher dimension. (The inputs must all have the same dimensionality, and cannot be empty or null.)
Partial mode	No

Syntax	avg ( smallint ) → numeric avg ( integer ) → numeric avg ( bigint ) → numeric avg ( numeric ) → numeric avg ( real ) → double precision avg ( double precision ) → double precision avg ( interval ) → interval
Description	Computes the average (arithmetic mean) of all the non-null input values.
Partial mode	Yes

Syntax	bit_and ( smallint ) → smallint bit_and ( integer ) → integer bit_and ( bigint ) → bigint bit_and ( bit ) → bit
Description	Computes the bitwise AND of all non-null input values.
Partial mode	Yes

Syntax	bit_or ( smallint ) → smallint bit_or ( integer ) → integer bit_or ( bigint ) → bigint bit_or ( bit ) → bit
Description	Computes the bitwise OR of all non-null input values.
Partial mode	Yes

Syntax	bool_and ( boolean ) → boolean
Description	Returns true if all non-null input values are true, otherwise false.
Partial mode	Yes

Syntax	bool_or ( boolean ) → boolean
Description	Returns true if any non-null input value is true, otherwise false.
Partial mode	Yes

Syntax	count ( * ) → bigint
Description	Computes the number of input rows.
Partial mode	Yes

Syntax	count ( "any" ) → bigint
Description	Computes the number of input rows in which the input value is not null.
Partial mode	Yes

Syntax	every ( boolean ) → boolean
Description	This is the SQL standard's equivalent to bool_and.
Partial mode	Yes

Syntax	json_agg ( anyelement ) → json jsonb_agg ( anyelement ) → jsonb
Description	Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.
Partial mode	No

Syntax	json_object_agg ( key "any", value "any" ) → json jsonb_object_agg ( key "any", value "any" ) → jsonb
Description	Collects all the key/value pairs into a JSON object. Key arguments are coerced to text; value arguments are converted as per to_json or to_jsonb. Values can be null, but not keys.
Partial mode	No

Syntax	max ( see text ) → same as input type
Description	Computes the maximum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, and arrays of any of these types.
Partial mode	Yes

Syntax	min ( see text ) → same as input type
Description	Computes the minimum of the non-null input values. Available for any numeric, string, date/time, or enum type, as well as inet, interval, money, oid, pg_lsn, tid, and arrays of any of these types.
Partial mode	Yes

Syntax	string_agg ( value text, delimiter text ) → text string_agg ( value bytea, delimiter bytea ) → bytea
Description	Concatenates the non-null input values into a string. Each value after the first is preceded by the corresponding delimiter (if it's not null).
Partial mode	No

Syntax	sum ( smallint ) → bigint sum ( integer ) → bigint sum ( bigint ) → numeric sum ( numeric ) → numeric sum ( real ) → real sum ( double precision ) → double precision sum ( interval ) → interval sum ( money ) → money
Description	Computes the sum of the non-null input values.
Partial mode	Yes

Syntax	xmlagg ( xml ) → xml
Description	Concatenates the non-null XML input values (see Section 9.15.1.7).
Partial mode	No

Aggregate Functions for Statistics

Syntax	corr ( Y double precision, X double precision ) → double precision
Description	Computes the correlation coefficient.
Partial mode	Yes

Syntax	covar_pop ( Y double precision, X double precision ) → double precision
Description	Computes the population covariance.
Partial mode	Yes

Syntax	covar_samp ( Y double precision, X double precision ) → double precision
Description	Computes the sample covariance.
Partial mode	Yes

Syntax	regr_avgx ( Y double precision, X double precision ) → double precision
Description	Computes the average of the independent variable, sum(X)/N.
Partial mode	Yes

Syntax	regr_avgy ( Y double precision, X double precision ) → double precision
Description	Computes the average of the dependent variable, sum(Y)/N.
Partial mode	Yes

Syntax	regr_count ( Y double precision, X double precision ) → double precision
Description	Computes the number of rows in which both inputs are non-null.
Partial mode	Yes

Syntax	regr_intercept ( Y double precision, X double precision ) → double precision
Description	Computes the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.
Partial mode	Yes

Syntax	regr_r2 ( Y double precision, X double precision ) → double precision
Description	Computes the square of the correlation coefficient.
Partial mode	Yes

Syntax	regr_slope ( Y double precision, X double precision ) → double precision
Description	Computes the slope of the least-squares-fit linear equation determined by the (X, Y) pairs.
Partial mode	Yes

Syntax	regr_sxx ( Y double precision, X double precision ) → double precision
Description	Computes the "sum of squares" of the independent variable, sum(X^2) - sum(X)^2/N.
Partial mode	Yes

Syntax	regr_sxy ( Y double precision, X double precision ) → double precision
Description	Computes the "sum of products" of independent times dependent variables, sum(X*Y) - sum(X) * sum(Y)/N.
Partial mode	Yes

Syntax	regr_syy ( Y double precision, X double precision ) → double precision
Description	Computes the "sum of squares" of the dependent variable, sum(Y^2) - sum(Y)^2/N.
Partial mode	Yes

Syntax	stddev ( numeric_type ) → double precision for real or double precision, otherwise numeric
Description	This is a historical alias for stddev_samp.
Partial mode	Yes

Syntax	stddev_pop ( numeric_type ) → double precision for real or double precision, otherwise numeric
Description	Computes the population standard deviation of the input values.
Partial mode	Yes

Syntax	stddev_samp ( numeric_type ) → double precision for real or double precision, otherwise numeric
Description	Computes the sample standard deviation of the input values.
Partial mode	Yes

Syntax	variance ( numeric_type ) → double precision for real or double precision, otherwise numeric
Description	This is a historical alias for var_samp.
Partial mode	Yes

Syntax	var_pop ( numeric_type ) → double precision for real or double precision, otherwise numeric
Description	Computes the population variance of the input values (square of the population standard deviation).
Partial mode	Yes

Syntax	var_samp ( numeric_type ) → double precision for real or double precision, otherwise numeric
Description	Computes the sample variance of the input values (square of the sample standard deviation).
Partial mode	Yes

Ordered-Set Aggregate Functions

Syntax	mode () WITHIN GROUP ( ORDER BY anyelement ) → anyelement
Description	Computes the mode, the most frequent value of the aggregated argument (arbitrarily choosing the first one if there are multiple equally-frequent values). The aggregated argument must be of a sortable type.
Partial mode	No

Syntax	percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY double precision ) → double precision percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY interval ) → interval
Description	Computes the continuous percentile, a value corresponding to the specified fraction within the ordered set of aggregated argument values. This will interpolate between adjacent input items if needed.
Partial mode	No

Syntax	percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY double precision ) → double precision[] percentile_cont ( fractions double precision[] ) WITHIN GROUP ( ORDER BY interval ) → interval[]
Description	Computes multiple continuous percentiles. The result is an array of the same dimensions as the fractions parameter, with each non-null element replaced by the (possibly interpolated) value corresponding to that percentile.
Partial mode	No

Syntax	percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY anyelement ) → anyelement
Description	Computes the discrete percentile, the first value within the ordered set of aggregated argument values whose position in the ordering equals or exceeds the specified fraction. The aggregated argument must be of a sortable type.
Partial mode	No

Syntax	percentile_disc ( fractions double precision[] ) WITHIN GROUP ( ORDER BY anyelement ) → anyarray
Description	Computes multiple discrete percentiles. The result is an array of the same dimensions as the fractions parameter, with each non-null element replaced by the input value corresponding to that percentile. The aggregated argument must be of a sortable type.
Partial mode	No

Hypothetical-Set Aggregate Functions

Syntax	rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint
Description	Computes the rank of the hypothetical row, with gaps; that is, the row number of the first row in its peer group.
Partial mode	No

Syntax	dense_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → bigint
Description	Computes the rank of the hypothetical row, without gaps; this function effectively counts peer groups.
Partial mode	No

Syntax	percent_rank ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision
Description	Computes the relative rank of the hypothetical row, that is (rank - 1) / (total rows - 1). The value thus ranges from 0 to 1 inclusive.
Partial mode	No

Syntax	cume_dist ( args ) WITHIN GROUP ( ORDER BY sorted_args ) → double precision
Description	Computes the cumulative distribution, that is (number of rows preceding or peers with hypothetical row) / (total rows). The value thus ranges from 1/N to 1.
Partial mode	No

Grouping Operations

Syntax	GROUPING ( group_by_expression(s) ) → integer
Description	Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is included in the grouping criteria of the grouping set generating the current result row, and 1 if it is not included.

Strings

See the official docs for more on all functions in this category. The table immediately below provides you the most commonly used PostgreSQL functions as considred on PostgreSQL Tutorial. Links to these functions take you to the function's reference on the PostgreSQL Tutorial site. Underneath the table below is a reproduction of what currently exists in the Postgres docs in a more palatable form.

Function	Description	Example	Result
ASCII	Return the ASCII code value of a character or Unicode code point of a UTF8 character	ASCII('A')	65
CHR	Convert an ASCII code to a character or a Unicode code point to a UTF8 character	CHR(65)	'A'
CONCAT	Concatenate two or more strings into one	CONCAT('A','B','C')	'ABC'
CONCAT_WS	Concatenate strings with a separator	CONCAT_WS(',','A','B','C')	'A,B,C'
FORMAT	Format arguments based on a format string	FORMAT('Hello %s','PostgreSQL')	'Hello PostgreSQL'
INITCAP	Convert words in a string to title case	INITCAP('hI tHERE')	Hi There
LEFT	Return the first n character in a string	LEFT('ABC',1)	'A'
LENGTH	Return the number of characters in a string	LENGTH('ABC')	3
LOWER	Convert a string to lowercase	LOWER('hI tHERE')	'hi there'
LPAD	Pad on the left a a string with a character to a certain length	LPAD('123′, 5, '00')	'00123'
LTRIM	Remove the longest string that contains specified characters from the left of the input string	LTRIM('00123')	'123'
MD5	Return MD5 hash of a string in hexadecimal	MD5('PostgreSQL MD5')	f78fdb18bf39b23d42313edfaf7e0a44
POSITION	Return the location of a substring in a string	POSTION('B' in 'A B C')	3
REGEXP_MATCHES	Match a POSIX regular expression against a string and returns the matching substrings	SELECT REGEXP_MATCHES('ABC', '^(A)(..)$', 'g');	{A,BC}
REGEXP_REPLACE	Replace substrings that match a POSIX regular expression by a new substring	REGEXP_REPLACE('John Doe','(.*) (.*)','\2, \1′);	'Doe, John'
REPEAT	Repeat string the specified number of times	REPEAT('*', 5)	'*****'
REPLACE	Replace all occurrences in a string of substring from with substring to	REPLACE('ABC','B','A')	'AAC'
REVERSE	Return reversed string	REVERSE('ABC')	'CBA'
RIGHT	Return last n characters in the string. When n is negative, return all but first `	n	` characters
RPAD	Pad on the right of a string with a character to a certain length	RPAD('ABC', 6, 'xo')	'ABCxox'
RTRIM	Remove the longest string that contains specified characters from the right of the input string	RTRIM('abcxxzx', 'xyz')	'abc'
SPLIT_PART	Split a string on a specified delimiter and return nth substring	SPLIT_PART('2017-12-31′,'-',2)	'12'
SUBSTRING	Extract a substring from a string	SUBSTRING('ABC',1,1)	'A'
TRIM	Remove the longest string that contains specified characters from the left, right or both of the input string	TRIM(' ABC ')	'ABC'
UPPER	Convert a string to uppercase	UPPER('hI tHERE')	'HI THERE'
TO_CHAR	Converts a timestamp, an interval, an integer, a double precision, or a numeric value to a string	TO_CHAR(TIMESTAMP '2017-08-18 22:30:59', 'HH24:MI:SS')	22:30:59
TO_NUMBER	Converts a character string to a numeric value	TO_NUMBER('12,345.6-', '99G999D9S')	-12345.6

What currently exists in the Postgres docs is listed below.

• SQL String Functions and Operators
• Other String Functions

SQL String Functions and Operators

Syntax	text || text → text
Description	Concatenates the two strings..
Example(s)	'Post' || 'greSQL' → PostgreSQL

Syntax	text || anynonarray → text anynonarray || text → text
Description	Converts the non-string input to text, then concatenates the two strings. (The non-string input cannot be of an array type, because that would create ambiguity with the array || operators. If you want to concatenate an array's text equivalent, cast it to text explicitly.).
Example(s)	'Value: ' || 42 → Value: 42

Syntax	text IS [NOT] [form] NORMALIZED → boolean
Description	Checks whether the string is in the specified Unicode normalization form. The optional form key word specifies the form: NFC (the default), NFD, NFKC, or NFKD. This expression can only be used when the server encoding is UTF8. Note that checking for normalization using this expression is often faster than normalizing possibly already normalized strings.
Example(s)	actualExamples

Syntax	bit_length ( text ) → integer
Description	Returns number of bits in the string (8 times the octet_length)
Example(s)	bit_length('jose') → 32

Syntax	char_length ( text ) → integer character_length ( text ) → integer
Description	Returns number of characters in the string
Example(s)	char_length('josé') → 4

Syntax	lower ( text ) → text
Description	Converts the string to all lower case, according to the rules of the database's locale
Example(s)	lower('TOM') → tom

Syntax	normalize ( text [, form ] ) → text
Description	Converts the string to the specified Unicode normalization form. The optional form key word specifies the form: NFC (the default), NFD, NFKC, or NFKD. This function can only be used when the server encoding is UTF8.
Example(s)	normalize(U&'\0061\0308bc', NFC) → U&'\00E4bc'

Syntax	octet_length ( text ) → integer
Description	Returns number of bytes in the string
Example(s)	octet_length('josé') → 5 (if server encoding is UTF8)

Syntax	octet_length ( character ) → integer
Description	Returns number of bytes in the string. Since this version of the function accepts type character directly, it will not strip trailing spaces
Example(s)	octet_length('abc '::character(4)) → 4

Syntax	overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text
Description	Replaces the substring of string that starts at the start'th character and extends for count characters with newsubstring. If count is omitted, it defaults to the length of newsubstring.
Example(s)	overlay('Txxxxas' placing 'hom' from 2 for 4) → Thomas

Syntax	position ( substring text IN string text ) → integer
Description	Returns starting index of specified substring within string, or zero if it's not present.
Example(s)	position('om' in 'Thomas') → 3

Syntax	substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text
Description	Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.
Example(s)	substring('Thomas' from 2 for 3) → hom substring('Thomas' from 3) → omas substring('Thomas' for 2) → Th

Syntax	substring ( string text FROM pattern text ) → text
Description	Extracts substring matching POSIX regular expression; see Section 9.7.3
Example(s)	substring('Thomas' from '...$') → mas

Syntax	substring ( string text FROM pattern text FOR escape text ) → text
Description	Extracts substring matching SQL regular expression; see Section 9.7.2
Example(s)	substring('Thomas' from '%#"o_a#"_' for '#') → oma

Syntax	trim ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text
Description	Removes the longest string containing only characters in characters (a space by default) from the start, end, or both ends (BOTH is the default) of string.
Example(s)	trim(both 'xyz' from 'yxTomxx') → Tom

Syntax	trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] string text [, characters text ] ) → text
Description	This is a non-standard syntax for trim().
Example(s)	trim(both from 'yxTomxx', 'xyz') → Tom

Syntax	upper ( text ) → text
Description	Converts the string to all upper case, according to the rules of the database's locale.
Example(s)	upper('tom') → TOM

Other String Functions

Additional string manipulation functions are available and are listed in the table below. Some of them are used internally to implement the SQL-standard string functions listed in the table above.

Syntax	ascii ( text ) → integer
Description	Returns the numeric code of the first character of the argument. In UTF8 encoding, returns the Unicode code point of the character. In other multibyte encodings, the argument must be an ASCII character.
Example(s)	ascii('x') → 120

Syntax	btrim ( string text [, characters text ] ) → text
Description	Removes the longest string containing only characters in characters (a space by default) from the start and end of string.
Example(s)	btrim('xyxtrimyyx', 'xyz') → trim

Syntax	chr ( integer ) → text
Description	Returns the character with the given code. In UTF8 encoding the argument is treated as a Unicode code point. In other multibyte encodings the argument must designate an ASCII character. chr(0) is disallowed because text data types cannot store that character.
Example(s)	chr(65) → A

Syntax	concat ( val1 "any" [, val2 "any" [, ...] ] ) → text
Description	Concatenates the text representations of all the arguments. NULL arguments are ignored.
Example(s)	concat('abcde', 2, NULL, 22) → abcde222

Syntax	concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text
Description	Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored.
Example(s)	concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22

Syntax	format ( formatstr text [, formatarg "any" [, ...] ] ) → text
Description	Formats arguments according to a format string; see Section 9.4.1. This function is similar to the C function sprintf.

Syntax	initcap ( text ) → text
Description	Converts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.
Example(s)	initcap('hi THOMAS') → Hi Thomas

Syntax	left ( string text, n integer ) → text
Description	Returns first n characters in the string, or when n is negative, returns all but last |n| characters.
Example(s)	left('abcde', 2) → ab

Syntax	length ( text ) → integer
Description	Returns the number of characters in the string.
Example(s)	length('jose') → 4

Syntax	lpad ( string text, length integer [, fill text ] ) → text
Description	Extends 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).
Example(s)	lpad('hi', 5, 'xy') → xyxhi

Syntax	ltrim ( string text [, characters text ] ) → text
Description	Removes the longest string containing only characters in characters (a space by default) from the start of string.
Example(s)	ltrim('zzzytest', 'xyz') → test

Syntax	md5 ( text ) → text
Description	Computes the MD5 hash of the argument, with the result written in hexadecimal.
Example(s)	md5('abc') → 900150983cd24fb0​d6963f7d28e17f72

Syntax

parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[]

Description

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[].

Syntax	pg_client_encoding ( ) → name
Description	Returns current client encoding name.
Example(s)	pg_client_encoding() → UTF8

Syntax	quote_ident ( text ) → text
Description	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. See also Example 42.1.

Syntax	quote_literal ( text ) → text
Description	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. See also Example 42.1.
Example(s)	quote_literal(E'O\'Reilly') → 'O''Reilly'

Syntax	quote_literal ( anyelement ) → text
Description	Converts the given value to text and then quotes it as a literal. Embedded single-quotes and backslashes are properly doubled.
Example(s)	quote_literal(42.5) → '42.5'

Syntax	quote_nullable ( text ) → text
Description	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. See also Example 42.1.
Example(s)	quote_nullable(NULL) → NULL

Syntax	quote_nullable ( anyelement ) → text
Description	Converts the given value to text and then quotes it as a literal; or, if the argument is null, returns NULL. Embedded single-quotes and backslashes are properly doubled.
Example(s)	quote_nullable(42.5) → '42.5'

Syntax	regexp_match ( string text, pattern text [, flags text ] ) → text[]
Description	Returns captured substring(s) resulting from the first match of a POSIX regular expression to the string; see Section 9.7.3.

Syntax	regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]
Description	Returns captured substring(s) resulting from matching a POSIX regular expression to the string; see Section 9.7.3.

Syntax	regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text
Description	Replaces substring(s) matching a POSIX regular expression; see Section 9.7.3.
Example(s)	regexp_replace('Thomas', '.[mN]a.', 'M') → ThM

Syntax	regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]
Description	Splits string using a POSIX regular expression as the delimiter; see Section 9.7.3.

Syntax	regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text
Description	Splits string using a POSIX regular expression as the delimiter; see Section 9.7.3.
Example(s)	regexp_split_to_table('hello world', '\s+') → hello world

Syntax	repeat ( string text, number integer ) → text
Description	Repeats string the specified number of times.
Example(s)	repeat('Pg', 4) → PgPgPgPg

Syntax	replace ( string text, from text, to text ) → text
Description	Replaces all occurrences in string of substring from with substring to.
Example(s)	replace('abcdefabcdef', 'cd', 'XX') → abXXefabXXef

Syntax	reverse ( text ) → text
Description	Reverses the order of the characters in the string.
Example(s)	reverse('abcde') → edcba

Syntax	right ( string text, n integer ) → text
Description	Returns last n characters in the string, or when n is negative, returns all but first |n| characters.
Example(s)	right('abcde', 2) → de

Syntax	rpad ( string text, length integer [, fill text ] ) → text
Description	Extends 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.
Example(s)	rpad('hi', 5, 'xy') → hixyx

Syntax	rtrim ( string text [, characters text ] ) → text
Description	Removes the longest string containing only characters in characters (a space by default) from the end of string.
Example(s)	rtrim('testxxzx', 'xyz') → test

Syntax	split_part ( string text, delimiter text, n integer ) → text
Description	Splits string at occurrences of delimiter and returns the n'th field (counting from one).
Example(s)	split_part('abc~@~def~@~ghi', '~@~', 2) → def

Syntax	strpos ( string text, substring text ) → integer
Description	Returns starting index of specified substring within string, or zero if it's not present. (Same as position(substring in string), but note the reversed argument order.)
Example(s)	strpos('high', 'ig') → 2

Syntax	substr ( string text, start integer [, count integer ] ) → text
Description	Extracts the substring of string starting at the start'th character, and extending for count characters if that is specified. (Same as substring(string from start for count).)
Example(s)	substr('alphabet', 3) → phabet substr('alphabet', 3, 2) → ph

Syntax	starts_with ( string text, prefix text ) → boolean
Description	Returns true if string starts with prefix.
Example(s)	starts_with('alphabet', 'alph') → t

Syntax	to_ascii ( string text ) → text to_ascii ( string text, encoding name ) → text to_ascii ( string text, encoding integer ) → text
Description	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. (See the unaccent module for another, more flexible solution.)
Example(s)	to_ascii('Karél') → Karel

Syntax	to_hex ( integer ) → text to_hex ( bigint ) → text
Description	Converts the number to its equivalent hexadecimal representation.
Example(s)	to_hex(2147483647) → 7fffffff

Syntax	translate ( string text, from text, to text ) → text
Description	Replaces each character in string that matches a character in the from set with the corresponding character in the to set. If from is longer than to, occurrences of the extra characters in from are deleted.
Example(s)	translate('12345', '143', 'ax') → a2x5

Math

See the official docs for more on all functions in this category. The table immediately below provides you the most commonly used PostgreSQL functions as considred on PostgreSQL Tutorial. Links to these functions take you to the function's reference on the PostgreSQL Tutorial site. Underneath the table below is a reproduction of what currently exists in the Postgres docs in a more palatable form.

Function	Description	Example	Result
ABS	Calculate the absolute value of a number	ABS(-10)	10
CBRT	Calculate the cube root of a number	CBRT(8)	2
CEIL	Round a number up to the nearest integer, which is greater than or equal to number	CEIL(-12.8)	-12
CEILING	Same as CEIL	example	result
DEGREES	Convert radians to degrees	DEGREES(0.8)	45.83662361
DIV	Return the integer quotient of two numeric values	DIV(8,3)	2
EXP	Return the exponential value in scientific notation of a number	EXP(1)	2.718281828
FLOOR	Round a number down to the nearest integer, which is less than or equal to number	FLOOR(10.6)	10
LN	Return the natural logarithm of a numeric value	LN(3)	1.098612289
LOG	Return the base 10 logarithm of a numeric value	LOG(1000)	3
LOG	Return the logarithm of a numeric value to a specified base	LOG(2, 64)	6
MOD	Divide the first parameter by the second one and return the remainder	MOD(10,4)	1
PI	Return the value of PI	PI()	3.141592654
POWER	Raise a numeric value to the power of a second numeric value	POWER(5, 3)	125
RADIANS	Convert degrees to radians	RADIANS(60)	1.047197551
ROUND	Round a number to the nearest integer or to a specified decimal places	ROUND(10.3)	10
SCALE	Return the number of decimal digits in the fractional part	SCALE(1.234)	3
SIGN	Return the sign (positive, negative) of a numeric value	SIGN(-1)	-1
SQRT	Return the square root of a numeric value	SQRT(3.0)	1.732050808
TRUNC	Truncate a numeric value to a whole number of to the specified decimal places	TRUNC(12.3)	12
WIDTH_BUCKET	Assign values to buckets in an equi-width histogram
RANDOM	Return a random number that ranges from 0 to 1		0.968435665

All tables below appear in the official Postgres docs.

• Mathematical Operators
• Mathematical Functions
• Random Functions
• Trigonometric Functions
• Hyperbolic Functions

Mathematical Operators

Syntax	numeric_type + numeric_type → numeric_type
Description	Addition
Example(s)	2 + 3 → 5

Syntax	+ numeric_type → numeric_type
Description	Unary plus (no operation)
Example(s)	+ 3.5 → 3.5

Syntax	numeric_type - numeric_type → numeric_type
Description	Subtraction
Example(s)	2 - 3 → -1

Syntax	- numeric_type → numeric_type
Description	Negation
Example(s)	- (-4) → 4

Syntax	numeric_type * numeric_type → numeric_type
Description	Multiplication
Example(s)	2 * 3 → 6

Syntax	numeric_type / numeric_type → numeric_type
Description	Division (for integral types, division truncates the result towards zero)
Example(s)	5.0 / 2 → 2.5000000000000000 5 / 2 → 2 (-5) / 2 → -2

Syntax	numeric_type % numeric_type → numeric_type
Description	Modulo (remainder); available for smallint, integer, bigint, and numeric
Example(s)	5 % 4 → 1

Syntax	numeric ^ numeric → numeric double precision ^ double precision → double precision
Description	Exponentiation (unlike typical mathematical practice, multiple uses of ^ will associate left to right)
Example(s)	2 ^ 3 → 8 2 ^ 3 ^ 3 → 512

Syntax	|/ double precision → double precision
Description	Square root
Example(s)	|/ 25.0 → 5

Syntax	||/ double precision → double precision
Description	Cube root
Example(s)	||/ 64.0 → 4

Syntax	bigint ! → numeric
Description	Factorial (deprecated, use factorial() instead)
Example(s)	5 ! → 120

Syntax	!! bigint → numeric
Description	Factorial as a prefix operator (deprecated, use factorial() instead)
Example(s)	!! 5 → 120

Syntax	@ numeric_type → numeric_type
Description	Absolute value
Example(s)	@ -5.0 → 5

Syntax	integral_type & integral_type → integral_type
Description	Bitwise AND
Example(s)	91 & 15 → 11

Syntax	integral_type | integral_type → integral_type
Description	Bitwise OR
Example(s)	32 | 3 → 35

Syntax	integral_type # integral_type → integral_type
Description	Bitwise exclusive OR
Example(s)	17 # 5 → 20

Syntax	~ integral_type → integral_type
Description	Bitwise NOT
Example(s)	~1 → -2

Syntax	integral_type << integer → integral_type
Description	Bitwise shift left
Example(s)	1 << 4 → 16

Syntax	integral_type >> integer → integral_type
Description	Bitwise shift right
Example(s)	8 >> 2 → 2

Mathematical Functions

Syntax	abs ( numeric_type ) → numeric_type
Description	Absolute value
Example(s)	abs(-17.4) → 17.4

Syntax	cbrt ( double precision ) → double precision
Description	Cube root
Example(s)	cbrt(64.0) → 4

Syntax	ceil ( numeric ) → numeric ceil ( double precision ) → double precision
Description	Nearest integer greater than or equal to argument
Example(s)	ceil(42.2) → 43 ceil(-42.8) → -42

Syntax	ceiling ( numeric ) → numeric ceiling ( double precision ) → double precision
Description	Nearest integer greater than or equal to argument (same as ceil)
Example(s)	ceiling(95.3) → 96

Syntax	degrees ( double precision ) → double precision
Description	Converts radians to degrees
Example(s)	degrees(0.5) → 28.64788975654116

Syntax	div ( y numeric, x numeric ) → numeric
Description	Integer quotient of y/x (truncates towards zero)
Example(s)	div(9,4) → 2

Syntax	exp ( numeric ) → numeric exp ( double precision ) → double precision
Description	Exponential (e raised to the given power)
Example(s)	exp(1.0) → 2.7182818284590452

Syntax	factorial ( bigint ) → numeric
Description	Factorial
Example(s)	factorial(5) → 120

Syntax	floor ( numeric ) → numeric floor ( double precision ) → double precision
Description	Nearest integer less than or equal to argument
Example(s)	floor(42.8) → 42 floor(-42.8) → -43

Syntax	gcd ( numeric_type, numeric_type ) → numeric_type
Description	Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns 0 if both inputs are zero; available for integer, bigint, and numeric
Example(s)	gcd(1071, 462) → 21

Syntax	lcm ( numeric_type, numeric_type ) → numeric_type
Description	Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns 0 if either input is zero; available for integer, bigint, and numeric
Example(s)	lcm(1071, 462) → 23562

Syntax	ln ( numeric ) → numeric ln ( double precision ) → double precision
Description	Natural logarithm
Example(s)	ln(2.0) → 0.6931471805599453

Syntax	log ( numeric ) → numeric log ( double precision ) → double precision
Description	Base 10 logarithm
Example(s)	log(100) → 2

Syntax	log10 ( numeric ) → numeric log10 ( double precision ) → double precision
Description	Base 10 logarithm (same as log)
Example(s)	log10(1000) → 3

Syntax	log ( b numeric, x numeric ) → numeric
Description	Logarithm of x to base b
Example(s)	log(2.0, 64.0) → 6.0000000000

Syntax	min_scale ( numeric ) → integer
Description	Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely
Example(s)	min_scale(8.4100) → 2

Syntax	mod ( y numeric_type, x numeric_type ) → numeric_type
Description	Remainder of y/x; available for smallint, integer, bigint, and numeric
Example(s)	mod(9,4) → 1

Syntax	pi ( ) → double precision
Description	Approximate value of π
Example(s)	pi() → 3.141592653589793

Syntax	power ( a numeric, b numeric ) → numeric power ( a double precision, b double precision ) → double precision
Description	a raised to the power of b
Example(s)	power(9, 3) → 729

Syntax	radians ( double precision ) → double precision
Description	Converts degrees to radians
Example(s)	radians(45.0) → 0.7853981633974483

Syntax	round ( numeric ) → numeric round ( double precision ) → double precision
Description	Rounds to nearest integer
Example(s)	round(42.4) → 42

Syntax	round ( v numeric, s integer ) → numeric
Description	Rounds v to s decimal places
Example(s)	round(42.4382, 2) → 42.44

Syntax	scale ( numeric ) → integer
Description	Scale of the argument (the number of decimal digits in the fractional part)
Example(s)	scale(8.4100) → 4

Syntax	sign ( numeric ) → numeric sign ( double precision ) → double precision
Description	Sign of the argument (-1, 0, or +1)
Example(s)	sign(-8.4) → -1

Syntax	sqrt ( numeric ) → numeric sqrt ( double precision ) → double precision
Description	Square root
Example(s)	sqrt(2) → 1.4142135623730951

Syntax	trim_scale ( numeric ) → numeric
Description	Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes
Example(s)	trim_scale(8.4100) → 8.41

Syntax	trunc ( numeric ) → numeric trunc ( double precision ) → double precision
Description	Truncates to integer (towards zero)
Example(s)	trunc(42.8) → 42 trunc(-42.8) → -42

Syntax	trunc ( v numeric, s integer ) → numeric
Description	Truncates v to s decimal places
Example(s)	trunc(42.4382, 2) → 42.43

Syntax	width_bucket ( operand numeric, low numeric, high numeric, count integer ) → integer width_bucket ( operand double precision, low double precision, high double precision, count integer ) → integer
Description	Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 or count+1 for an input outside that range.
Example(s)	width_bucket(5.35, 0.024, 10.06, 5) → 3

Syntax	width_bucket ( operand anyelement, thresholds anyarray ) → integer
Description	Returns the number of the bucket in which operand falls given an array listing the lower bounds of the buckets. Returns 0 for an input less than the first lower bound. operand and the array elements can be of any type having standard comparison operators. The thresholds array must be sorted, smallest first, or unexpected results will be obtained.
Example(s)	width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]) → 2

Random Functions

Syntax	random ( ) → double precision
Description	Returns a random value in the range 0.0 <= x < 1.0
Example(s)	random() → 0.897124072839091

Syntax	setseed ( double precision ) → void
Description	Sets the seed for subsequent random() calls; argument must be between -1.0 and 1.0, inclusive
Example(s)	setseed(0.12345)

Trigonometric Functions

Syntax	acos ( double precision ) → double precision
Description	Inverse cosine, result in radians
Example(s)	acos(1) → 0

Syntax	acosd ( double precision ) → double precision
Description	Inverse cosine, result in degrees
Example(s)	acosd(0.5) → 60

Syntax	asin ( double precision ) → double precision
Description	Inverse sine, result in radians
Example(s)	asin(1) → 1.5707963267948966

Syntax	asind ( double precision ) → double precision
Description	Inverse sine, result in degrees
Example(s)	asind(0.5) → 30

Syntax	atan ( double precision ) → double precision
Description	Inverse tangent, result in radians
Example(s)	atan(1) → 0.7853981633974483

Syntax	atand ( double precision ) → double precision
Description	Inverse tangent, result in degrees
Example(s)	atand(1) → 45

Syntax	atan2 ( y double precision, x double precision ) → double precision
Description	Inverse tangent of y/x, result in radians
Example(s)	atan2(1,0) → 1.5707963267948966

Syntax	atan2d ( y double precision, x double precision ) → double precision
Description	Inverse tangent of y/x, result in degrees
Example(s)	atan2d(1,0) → 90

Syntax	cos ( double precision ) → double precision
Description	Cosine, argument in radians
Example(s)	cos(0) → 1

Syntax	cosd ( double precision ) → double precision
Description	Cosine, argument in degrees
Example(s)	cosd(60) → 0.5

Syntax	cot ( double precision ) → double precision
Description	Cotangent, argument in radians
Example(s)	cot(0.5) → 1.830487721712452

Syntax	cotd ( double precision ) → double precision
Description	Cotangent, argument in degrees
Example(s)	cotd(45) → 1

Syntax	sin ( double precision ) → double precision
Description	Sine, argument in radians
Example(s)	sin(1) → 0.8414709848078965

Syntax	sind ( double precision ) → double precision
Description	Sine, argument in degrees
Example(s)	sind(30) → 0.5

Syntax	tan ( double precision ) → double precision
Description	Tangent, argument in radians
Example(s)	tan(1) → 1.5574077246549023

Syntax	tand ( double precision ) → double precision
Description	Tangent, argument in degrees
Example(s)	tand(45) → 1

Hyperbolic Functions

Syntax	sinh ( double precision ) → double precision
Description	Hyperbolic sine
Example(s)	sinh(1) → 1.1752011936438014

Syntax	cosh ( double precision ) → double precision
Description	Hyperbolic cosine
Example(s)	cosh(0) → 1

Syntax	tanh ( double precision ) → double precision
Description	Hyperbolic tangent
Example(s)	tanh(1) → 0.7615941559557649

Syntax	asinh ( double precision ) → double precision
Description	Inverse hyperbolic sine
Example(s)	asinh(1) → 0.881373587019543

Syntax	acosh ( double precision ) → double precision
Description	Inverse hyperbolic cosine
Example(s)	acosh(1) → 0

Syntax	atanh ( double precision ) → double precision
Description	Inverse hyperbolic tangent
Example(s)	atanh(0.5) → 0.5493061443340548

Dates

See the official docs for more on all functions in this category. The table immediately below provides you the most commonly used PostgreSQL functions as considred on PostgreSQL Tutorial. Links to these functions take you to the function's reference on the PostgreSQL Tutorial site. Underneath the table below is a reproduction of what currently exists in the Postgres docs in a more palatable form.

Function	Return Type	Description
AGE	INTERVAL	Calculate ages between two timestamps and returns a "symbolic" result which uses years and months
AGE	INTERVAL	Calculate ages between current date (at midnight) and a timestamp and returns a "symbolic" result which uses years and months
CLOCK_TIMESTAMP	TIMESTAMPTZ	Return the current date and time which changes during statement execution
CURRENT_DATE	DATE	Return the current date
CURRENT_TIME	TIMESTAMPTZ	Return the current time
CURRENT_TIMESTAMP	TIMESTAMPTZ	Return the current date and time with time zone at which the current transaction starts
DATE_PART	DOUBLE PRECISION	Get a field of a timestamp or an interval e.g., year, month, day, etc.
DATE_TRUNC	TIMESTAMP	Return a timestamp truncated to a specified precision
EXTRACT	DOUBLE PRECISION	Same as DATE_PART() function
ISFINITE	BOOLEAN	Check if a date, a timestamp, or an interval is finite or not (not +/-infinity)
JUSTIFY_DAYS	INTERVAL	Adjust interval so 30-day time periods are represented as months
JUSTIFY_HOURS	INTERVAL	Adjust interval so 24-hour time periods are represented as days
JUSTIFY_INTERVAL	INTERVAL	Adjust interval using justify_days and justify_hours, with additional sign adjustments
LOCALTIME	TIME	Return the time at which the current transaction start
LOCALTIMESTAMP	TIMESTAMP	Return the date and time at which the current transaction start
NOW	TIMESTAMPTZ	Return the date and time with time zone at which the current transaction start
STATEMENT_TIMESTAMP	TIMESTAMPTZ	Return the current date and time at which the current statement executes
TIMEOFDAY	TEXT	Return the current date and time, like clock_timestamp, as a text string
TRANSACTION_TIMESTAMP	TIMESTAMPTZ	Same as NOW() function
TO_DATE	DATE	Convert a string to a date
TO_TIMESTAMP	TIMESTAMPTZ	Convert a string to a timestamp

The tables below appear in the official Postgres docs.

• Date/Time Operators
• Date/Time Functions

Syntax	date + integer → date
Description	Add a number of days to a date
Example(s)	date '2001-09-28' + 7 → 2001-10-05

Syntax	date + interval → timestamp
Description	Add an interval to a date
Example(s)	date '2001-09-28' + interval '1 hour' → 2001-09-28 01:00:00

Syntax	date + time → timestamp
Description	Add a time-of-day to a date
Example(s)	date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00

Syntax	interval + interval → interval
Description	Add intervals
Example(s)	interval '1 day' + interval '1 hour' → 1 day 01:00:00

Syntax	timestamp + interval → timestamp
Description	Add an interval to a timestamp
Example(s)	timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00

Syntax	time + interval → time
Description	Add an interval to a time
Example(s)	time '01:00' + interval '3 hours' → 04:00:00

Syntax	- interval → interval
Description	Negate an interval
Example(s)	- interval '23 hours' → -23:00:00

Syntax	date - date → integer
Description	Subtract dates, producing the number of days elapsed
Example(s)	date '2001-10-01' - date '2001-09-28' → 3

Syntax	date - integer → date
Description	Subtract a number of days from a date
Example(s)	date '2001-10-01' - 7 → 2001-09-24

Syntax	date - interval → timestamp
Description	Subtract an interval from a date
Example(s)	date '2001-09-28' - interval '1 hour' → 2001-09-27 23:00:00

Syntax	time - time → interval
Description	Subtract times
Example(s)	time '05:00' - time '03:00' → 02:00:00

Syntax	time - interval → time
Description	Subtract an interval from a time
Example(s)	time '05:00' - interval '2 hours' → 03:00:00

Syntax	timestamp - interval → timestamp
Description	Subtract an interval from a timestamp
Example(s)	timestamp '2001-09-28 23:00' - interval '23 hours' → 2001-09-28 00:00:00

Syntax	interval - interval → interval
Description	Subtract intervals
Example(s)	interval '1 day' - interval '1 hour' → 1 day -01:00:00

Syntax	timestamp - timestamp → interval
Description	Subtract timestamps (converting 24-hour intervals into days, similarly to justify_hours())
Example(s)	timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00' → 63 days 15:00:00

Syntax	interval * double precision → interval
Description	Multiply an interval by a scalar
Example(s)	interval '1 second' * 900 → 00:15:00 interval '1 day' * 21 → 21 days interval '1 hour' * 3.5 → 03:30:00

Syntax	interval / double precision → interval
Description	Divide an interval by a scalar
Example(s)	interval '1 hour' / 1.5 → 00:40:00

Date/Time Functions

Syntax	age ( timestamp, timestamp ) → interval
Description	Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days
Example(s)	age(timestamp '2001-04-10', timestamp '1957-06-13') → 43 years 9 mons 27 days

Syntax	age ( timestamp ) → interval
Description	Subtract argument from current_date (at midnight)
Example(s)	age(timestamp '1957-06-13') → 62 years 6 mons 10 days

Syntax	clock_timestamp ( ) → timestamp with time zone
Description	Current date and time (changes during statement execution); see Section 9.9.4
Example(s)	clock_timestamp() → 2019-12-23 14:39:53.662522-05

Syntax	current_date → date
Description	Current date; see Section 9.9.4
Example(s)	current_date → 2019-12-23

Syntax	current_time → time with time zone
Description	Current time of day; see Section 9.9.4
Example(s)	current_time → 14:39:53.662522-05

Syntax	current_time ( integer ) → time with time zone
Description	Current time of day, with limited precision; see Section 9.9.4
Example(s)	current_time(2) → 14:39:53.66-05

Syntax	current_timestamp → timestamp with time zone
Description	Current date and time (start of current transaction); see Section 9.9.4
Example(s)	current_timestamp → 2019-12-23 14:39:53.662522-05

Syntax	current_timestamp ( integer ) → timestamp with time zone
Description	Current date and time (start of current transaction), with limited precision; see Section 9.9.4
Example(s)	current_timestamp(0) → 2019-12-23 14:39:53-05

Syntax	date_part ( text, timestamp ) → double precision
Description	Get timestamp subfield (equivalent to extract); see Section 9.9.1
Example(s)	date_part('hour', timestamp '2001-02-16 20:38:40') → 20

Syntax	date_part ( text, interval ) → double precision
Description	Get interval subfield (equivalent to extract); see Section 9.9.1
Example(s)	date_part('month', interval '2 years 3 months') → 3

Syntax	date_trunc ( text, timestamp ) → timestamp
Description	Truncate to specified precision; see Section 9.9.2
Example(s)	date_trunc('hour', timestamp '2001-02-16 20:38:40') → 2001-02-16 20:00:00

Syntax	date_trunc ( text, timestamp with time zone, text ) → timestamp with time zone
Description	Truncate to specified precision in the specified time zone; see Section 9.9.2
Example(s)	date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') → 2001-02-16 13:00:00+00

Syntax	date_trunc ( text, interval ) → interval
Description	Truncate to specified precision; see Section 9.9.2
Example(s)	date_trunc('hour', interval '2 days 3 hours 40 minutes') → 2 days 03:00:00

Syntax	extract ( field from timestamp ) → double precision
Description	Get timestamp subfield; see Section 9.9.1
Example(s)	extract(hour from timestamp '2001-02-16 20:38:40') → 20

Syntax	extract ( field from interval ) → double precision
Description	Get interval subfield; see Section 9.9.1
Example(s)	extract(month from interval '2 years 3 months') → 3

Syntax	isfinite ( date ) → boolean
Description	Test for finite date (not +/-infinity)
Example(s)	isfinite(date '2001-02-16') → true

Syntax	isfinite ( timestamp ) → boolean
Description	Test for finite timestamp (not +/-infinity)
Example(s)	isfinite(timestamp 'infinity') → false

Syntax	isfinite ( interval ) → boolean
Description	Test for finite interval (currently always true)
Example(s)	isfinite(interval '4 hours') → true

Syntax	justify_days ( interval ) → interval
Description	Adjust interval so 30-day time periods are represented as months
Example(s)	justify_days(interval '35 days') → 1 mon 5 days

Syntax	justify_hours ( interval ) → interval
Description	Adjust interval so 24-hour time periods are represented as days
Example(s)	justify_hours(interval '27 hours') → 1 day 03:00:00

Syntax	justify_interval ( interval ) → interval
Description	Adjust interval using justify_days and justify_hours, with additional sign adjustments
Example(s)	justify_interval(interval '1 mon -1 hour') → 29 days 23:00:00

Syntax	localtime → time
Description	Current time of day; see Section 9.9.4
Example(s)	localtime → 14:39:53.662522

Syntax	localtime ( integer ) → time
Description	Current time of day, with limited precision; see Section 9.9.4
Example(s)	localtime(0) → 14:39:53

Syntax	localtimestamp → timestamp
Description	Current date and time (start of current transaction); see Section 9.9.4
Example(s)	localtimestamp → 2019-12-23 14:39:53.662522

Syntax	localtimestamp ( integer ) → timestamp
Description	Current date and time (start of current transaction), with limited precision; see Section 9.9.4
Example(s)	localtimestamp(2) → 2019-12-23 14:39:53.66

Syntax	make_date ( year int, month int, day int ) → date
Description	Create date from year, month and day fields
Example(s)	make_date(2013, 7, 15) → 2013-07-15

Syntax	make_interval ( [ years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]] ) → interval
Description	Create interval from years, months, weeks, days, hours, minutes and seconds fields, each of which can default to zero
Example(s)	make_interval(days => 10) → 10 days

Syntax	make_time ( hour int, min int, sec double precision ) → time
Description	Create time from hour, minute and seconds fields
Example(s)	make_time(8, 15, 23.5) → 08:15:23.5

Syntax	make_timestamp ( year int, month int, day int, hour int, min int, sec double precision ) → timestamp
Description	Create timestamp from year, month, day, hour, minute and seconds fields
Example(s)	make_timestamp(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5

Syntax	make_timestamptz ( year int, month int, day int, hour int, min int, sec double precision [, timezone text ] ) → timestamp with time zone
Description	Create timestamp with time zone from year, month, day, hour, minute and seconds fields; if timezone is not specified, the current time zone is used
Example(s)	make_timestamptz(2013, 7, 15, 8, 15, 23.5) → 2013-07-15 08:15:23.5+01

Syntax	now ( ) → timestamp with time zone
Description	Current date and time (start of current transaction); see Section 9.9.4
Example(s)	now() → 2019-12-23 14:39:53.662522-05

Syntax	statement_timestamp ( ) → timestamp with time zone
Description	Current date and time (start of current statement); see Section 9.9.4
Example(s)	statement_timestamp() → 2019-12-23 14:39:53.662522-05

Syntax	timeofday ( ) → text
Description	Current date and time (like clock_timestamp, but as a text string); see Section 9.9.4
Example(s)	timeofday() → Mon Dec 23 14:39:53.662522 2019 EST

Syntax	transaction_timestamp ( ) → timestamp with time zone
Description	Current date and time (start of current transaction); see Section 9.9.4
Example(s)	transaction_timestamp() → 2019-12-23 14:39:53.662522-05

Syntax	to_timestamp ( double precision ) → timestamp with time zone
Description	Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp with time zone
Example(s)	to_timestamp(1284352323) → 2010-09-13 04:32:03+00

Window Functions

See the official docs for more on all functions in this category. The table immediately below provides you the most commonly used PostgreSQL functions as considred on PostgreSQL Tutorial. Links to these functions take you to the function's reference on the PostgreSQL Tutorial site. Underneath the table below is a reproduction of what currently exists in the Postgres docs in a more palatable form.

The table immediately below lists all window functions provided by PostgreSQL. Note that some aggregate functions such as AVG(), MIN(), MAX(), SUM(), and COUNT() can be also used as window functions.

Function	Description
CUME_DIST	Return the relative rank of the current row
DENSE_RANK	Rank the current row within its partition without gaps
FIRST_VALUE	Return a value evaluated against the first row within its partition
LAG	Return a value evaluated at the row that is at a specified physical offset row before the current row within the partition
LAST_VALUE	Return a value evaluated against the last row within its partition
LEAD	Return a value evaluated at the row that is offset rows after the current row within the partition
NTILE	Divide rows in a partition as equally as possible and assign each row an integer starting from 1 to the argument value
NTH_VALUE	Return a value evaluated against the nth row in an ordered partition
PERCENT_RANK	Return the relative rank of the current row (rank-1) / (total rows – 1)
RANK	Rank the current row within its partition with gaps
ROW_NUMBER	Number the current row within its partition starting from 1

The following table is from the official Postgres docs.

Syntax	row_number () → bigint
Description	Returns the number of the current row within its partition, counting from 1.

Syntax	rank () → bigint
Description	Returns the rank of the current row, with gaps; that is, the row_number of the first row in its peer group.

Syntax	dense_rank () → bigint
Description	Returns the rank of the current row, without gaps; this function effectively counts peer groups.

Syntax	percent_rank () → double precision
Description	Returns the relative rank of the current row, that is (rank - 1) / (total partition rows - 1). The value thus ranges from 0 to 1 inclusive.

Syntax	cume_dist () → double precision
Description	Returns the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows). The value thus ranges from 1/N to 1.

Syntax	ntile ( num_buckets integer ) → integer
Description	Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible.

Syntax	lag ( value anyelement [, offset integer [, default anyelement ]] ) → anyelement
Description	Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead returns default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.

Syntax	lead ( value anyelement [, offset integer [, default anyelement ]] ) → anyelement
Description	Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead returns default (which must be of the same type as value). Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to NULL.

Syntax	first_value ( value anyelement ) → anyelement
Description	Returns value evaluated at the row that is the first row of the window frame.

Syntax	last_value ( value anyelement ) → anyelement
Description	Returns value evaluated at the row that is the last row of the window frame.

Syntax	nth_value ( value anyelement, n integer ) → anyelement
Description	Returns value evaluated at the row that is the n'th row of the window frame (counting from 1); returns NULL if there is no such row.

Set Returning Functions

See the official docs for more on all functions in this category. Specifically, this section describes functions that possibly return more than one row. The most widely used functions in this class are series generating functions, as shown below:

Syntax	generate_series ( start integer, stop integer [, step integer ] ) → setof integer generate_series ( start bigint, stop bigint [, step bigint ] ) → setof bigint generate_series ( start numeric, stop numeric [, step numeric ] ) → setof numeric
Description	Generates a series of values from start to stop, with a step size of step. step defaults to 1.

Syntax	generate_series ( start timestamp, stop timestamp, step interval ) → setof timestamp generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval ) → setof timestamp with time zone
Description	Generates a series of values from start to stop, with a step size of step.

When step is positive, zero rows are returned if start is greater than stop. Conversely, when step is negative, zero rows are returned if start is less than stop. Zero rows are also returned if any input is NULL. It is an error for step to be zero. Some examples follow:

1. Generate series of sequential integers

SELECT * FROM GENERATE_SERIES(2,4);

yields

┌─────────────────┐
│ generate_series │
├─────────────────┤
│               2 │
│               3 │
│               4 │
└─────────────────┘
(3 rows)

2. Generate series of sequential integers in reverse with a specified step size

SELECT * FROM GENERATE_SERIES(5,1,-2);

yields

┌─────────────────┐
│ generate_series │
├─────────────────┤
│               5 │
│               3 │
│               1 │
└─────────────────┘
(3 rows)

3. Return 0 rows by specifying a stop value smaller than a start value

SELECT * FROM GENERATE_SERIES(4,3);

yields

┌─────────────────┐
│ generate_series │
├─────────────────┤
└─────────────────┘
(0 rows)

4. Specify a non-integer step size

SELECT GENERATE_SERIES(1.1, 4, 1.3);

yields

┌─────────────────┐
│ generate_series │
├─────────────────┤
│             1.1 │
│             2.4 │
│             3.7 │
└─────────────────┘
(3 rows)

5. Generate sequential dates

-- this example relies on the date-plus-integer operator:
SELECT CURRENT_DATE + s.a AS dates FROM GENERATE_SERIES(0,14,7) AS s(a);

yields

┌────────────┐
│   dates    │
├────────────┤
│ 2021-03-04 │
│ 2021-03-11 │
│ 2021-03-18 │
└────────────┘
(3 rows)

6. Generate range of timestamps between two specified timestamps using a given step size

SELECT * FROM GENERATE_SERIES('2008-03-01 00:00'::TIMESTAMP, '2008-03-04 12:00', '10 hours');

yields

┌─────────────────────┐
│   generate_series   │
├─────────────────────┤
│ 2008-03-01 00:00:00 │
│ 2008-03-01 10:00:00 │
│ 2008-03-01 20:00:00 │
│ 2008-03-02 06:00:00 │
│ 2008-03-02 16:00:00 │
│ 2008-03-03 02:00:00 │
│ 2008-03-03 12:00:00 │
│ 2008-03-03 22:00:00 │
│ 2008-03-04 08:00:00 │
└─────────────────────┘
(9 rows)