This section provides you with the most useful PostgreSQL functions including aggregate functions, string functions, and date & time functions.
PostgreSQL aggregate functions
- PostgreSQL MIN – selects the minimum value of a set.
- PostgreSQL MAX – selects the maximum value of a set.
- PostgreSQL AVG – calculates the average value of a numeric column.
- PostgreSQL COUNT – counts the number of rows in a table.
- PostgreSQL SUM – returns the sum of values or distinct values.
PostgreSQL conditional expressions & operators
- PostgreSQL CASE – shows you how to form conditional queries with CASE expression.
- PostgreSQL COALESCE – returns the first non-null argument. You can use to substitute null value by another default value.
- PostgreSQL NULLIF – returns a null value if the first argument equals the second argument. We show you how to apply the
NULLIF
function to substitute null values for displaying data and preventing the query from division by zero error. - PostgreSQL CAST – converts from one data type into another e.g., from a string into an integer, from a string into a date, etc.
PostgreSQL string functions
PostgreSQL string functions and operators help you manipulate string values. Strings mean character, character varying, and text.
- PostgreSQL SUBSTRING – extracts a substring from a string using start position and length, as well as regular expression.
- PostgreSQL REPLACE – searches and replaces all occurrences of substrings with a new substring.
- PostgreSQL LENGTH – returns the number of characters in a string. The
OCTET_LENGTH
,BIT_LENGTH
, andCHAR_LENGTH
functions are also covered. - PostgreSQL TRIM – removes the longest string that contains a character from the beginning, ending, and both beginning and ending of a string. We will also introduce you to the
LTRIM
,RTRIM
, andBTRIM
functions. - PostgreSQL CONCAT – shows you how to use
CONCAT
andCONCAT_WS
functions to concatenate two or more strings into one. - PostgreSQL LOWER, UPPER, and INITCAP functions – gives you three useful string functions to format letter case of a string expression.