PostgreSQL Functions

This section provides you with the most useful PostgreSQL functions including aggregate functions, string functions, and date & time functions.

PostgreSQL Aggregate Functions

• PostgreSQL MIN – gets the minimum value of a set.
• PostgreSQL MAX – gets the maximum value of a set.
• PostgreSQL AVG – calculates 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 to the second argument. We show you how to apply NULLIF function to substitute null values for displaying data and preventing 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 date, from a string into 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, and CHAR_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, and BTRIM functions.
• PostgreSQL CONCAT – shows you how to use CONCAT and CONCAT_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.