Forget about using your database simply as an advanced version of Microsoft Excel.
When you are bringing down thousands of rows of unrefined data to your app in order to perform simple actions such as calculating average, counting distinct users, and formatting dates, then you are wasting your valuable bandwidth and time. The truth is that SQL is more than just data storage; it is a highly advanced computational tool. With the help of SQL functions, one can refine data and derive useful insights even before it gets downloaded from the server.In this article we shall ocus on the commoly used postgresql functions.
Postgresql functions are built-in operations that can be used to perform various calculations, manipulations, or transformations on data within an postgresql database.
Function Types
Functions can be categorized by what input they take.
Syntax of postgresql functions
The output of a function in postgresql is treated as a derived column and appears in the SELECT clause of a query. The general syntax of an postgresql function is:
1.Numeric Functions
These functions perform calculations on a set of values in a
column and return a numerical value.Numeric functions is grouped
into aggregate numeric functions and scalar numeric functions.
Aggregate numeric functions include
1.1 SUM()
The SUM() function returns the total sum of a specified numeric column.
1.2 AVG()
The AVG() function returns the average value of the selected numeric column.
1.3 MIN()
The MIN() function returns the smallest or lowest value of the selected column.
1.4 MAX()
The MAX() function returns the largest or highest value of the selected column.
scalar numeric functions include
1.5 ROUND()
The ROUND() function rounds a numerical value to a specified number of decimal places.
The following is the syntax of round function.
1.6 SQRT()
The SQRT() function returns the square root of a numerical value.
2.0 String Functions
These functions operate on string values and perform
operations such as concatenation, manipulation, and text
formatting.They include
2.1 UPPER() and LOWER() functions
The UPPER() function is used to convert a string to uppercase while the LOWER() function is used to convert a string to lowercase. Their syntaxes are as follows:
example
2.2 LTRIM() and RTRIM() functions
The LTRIM() function is used to remove leading spaces from the left end of a string while the RTRIM() function is used to remove trailing spaces from the right end of a string.You can use TRIM()
to remove both the leading and trailing spaces.
example
2.3 LEFT() and RIGHT() functions
The LEFT() function is used to extract a specified number of characters from the beginning (leftmost side) of a string while the RIGHT() function is used to extract a specified number of
characters from the end (rightmost side) of a string.
2.4 SUBSTRING() function
The SUBSTRING() function is used to extract a substring from a string. It takes three arguments: the original string, the starting position of the substring, and optionally, the length
of the substring.
example
2.5 CONCAT() function
The CONCAT() function is used to concatenate or join multiple strings together. It takes two or more string arguments (separated by commas) and returns a single concatenated string.
example
2.6 REPLACE() function
The REPLACE() function is used to replace all occurrences of a specified substring within a string with a new substring. It takes three arguments: the original string, the substring to be
replaced, and the new substring.
example
3.0 Miscellaneous functions
This category represents a variety of functions that do a
variety of things such as converting data types and dealing
with NULL values.They include
3.1 CAST() function
The CAST() function is used to convert a value from its current data type into a specified data type. Its basic syntax is as follows
example
3.2 CONVERT() function
CONVERT() is another function that can be used for conversion from one data type to another. Its basic syntax is as follows:
example
3.3 IFNULL() function
The IFNULL() function returns a specified value if the given expression is null. Otherwise, it returns the value of the expression itself. Its basic syntax is as follow
example
3.4 NULLIF() function
The NULLIF() function is used to compare two expressions and return NULL if they are equal. Otherwise, the first expression is returned. Its basic syntax is as follows:
example
3.5 ISNULL() function
The ISNULL() function helps to determine whether an expression is NULL or not. If the expression is NULL, this function returns 1. Otherwise, it returns 0. Its basic syntax is as follows:
example
3.6 COALESCE() function
The COALESCE() function evaluates a list of expressions from left to right, searching for the first non-NULL value and returning it. If all the expressions are NULL, the function returns
NULL. Its basic syntax is as follows:
example
4.0 Datetime
These are functions used to handle date and time values and
perform operations like formatting, extraction, and manipulation.They include
4.1 CURRENT_DATE()
The CURRENT_DATE() function is used to retrieve the current date without the time component.
4.2 NOW()
The NOW() function is used to retrieve the current date and time from the system. It returns a datetime value representing the current timestamp.
4.3 DATEDIFF()
The DATEDIFF() function is used to calculate the difference between two dates. It takes three parameters: the part of the date for which to calculate the difference (day, month, or year), the start date, and the end date.
example
*4.4 DATE_ADD() *
The DATE_ADD() function is used to add a specified interval to a date or datetime value. It takes three parameters: a date or datetime to which the interval will be added, a value representing the interval you want to add, and an interval unit, which can be DAY, MONTH, or YEAR.
example
4.5 TO_DATE()
TO_DATE function evaluates a character string according to the date-formatting directive that you specify and returns a DATETIME value.
4.6 TO_CHAR
It converts a date, number, or other value into a text string.
I hope you read this article and continuously practice these functions because that is the only way you will master them.
Top comments (0)