PostgreSQL Functions

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 CASE expression.
• COALESCE – returns the first non-null argument. You can use it to substitute NULL by a default value.
• NULLIF – returns NULL if 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

PostgreSQL string functions and operators help you manipulate string values effectively. Strings mean character, character varying, and text.

• CONCAT – shows you how to use CONCAT and CONCAT_WS functions to concatenate two or more strings into one.
• LENGTH – returns the number of characters in a string. The OCTET_LENGTH, BIT_LENGTH, and CHAR_LENGTH functions 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 LTRIM, RTRIM, and BTRIM functions.

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.