This section provides you with the most useful PostgreSQL functions including aggregate functions, string functions, and date & time functions.
PostgreSQL aggregate functions
AVG– calculates the average value of a list of values.
COUNT– counts the number of values in a group.
SUM– calculates the total of a list of values.
MAX– get the maximum value of in a set of values.
MIN– gets the minimum value of in a set of values.
PostgreSQL conditional expressions & operators
CASE– shows you how to form conditional queries with the
COALESCE– returns the first non-null argument. You can use it to substitute
NULLby a default value.
NULLif the first argument equals the second one.
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
CONCAT– shows you how to use
CONCAT_WSfunctions to concatenate two or more strings into one.
LENGTH– returns the number of characters in a string. The
CHAR_LENGTHfunctions are also covered.
LOWER, UPPER, and INITCAP functions– gives you three useful string functions to format letter case of a string expression.
REPLACE– searches and replaces all occurrences of substrings with a new substring.
SUBSTRING– extracts a substring from a string using start position and length, as well as regular expression.
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
PostgreSQL date/time functions
The following section shows you the most commonly used PostgreSQL date/time functions that allow you to manipulate date and time values more effectively.
AGE– calculate ages and return a result as an interval.
DATE_TRUNC– truncates a date to a level of precision e.g., hour, minute, second etc.
DATE_PART– extracts a subfield e.g., year, month, week, day, etc., from a date or time value.
NOW– returns the current date and time with the time zone information.
TO_DATE– converts a string into a date value.