5 Important PostgreSQL Functions

Hello folks, I’m back with a new article. This article is a bit interesting and knowledgeable as you can see in the title as well. This article can be very useful for you during your databases interviews and exams. This topics were asked in my interview, and in some of them, I’m blank at that moment XD. Then, I learn about these concepts and find them very interesting.

In this article, I’ll be discussing about some of the powerful functions of the postgreSQL, which can help you in solving mathematical queries in your interviews and exams. With this, I’ll be discussing some questions as well so that you can understand where we can use these functions properly.

These five functions are:

1. NOW & INTERVAL
2. EXTRACT
3. NULLIF
4. COALESCE
5. AGE

NOW & INTERVAL

Suppose, there is a case in which you have to use the current timestamp, then how will you find it in the postgreSQL. Then comes the NOW, this function takes no parameter and returns a column with a single row of the current timestamp.

select NOW();

Timestamp format of postgreSQL

Let suppose, someone wants to know the date from the timestamp. Then you can use ‘::’ to get the date, time from the timestamp.

select NOW():: DATE;        ## this will give you the date.
select NOW():: TIME;        ## this will give you the time.

Now, if someone ask to find the date of 10 months before today’s date, or 20 years after today’s date. For this, you can use INTERVAL method. What this function does is, it will just add/subtract the interval mentioned and returns the timestamp for the same.

select NOW () - INTERVAL '10 MONTH';         
# this will return the timestamp before 10 months from now.
select NOW () + INTERVAL '20 YEAR';             
# this will return the timestamp after 20 years from now.

NOW and INTERVAL combined

EXTRACT

Now this comes the second function, EXTRACT. This is used to extract something from the timestamp like day, month, year, century etc. This can be a very helpful function, if someone gives you a question where you have to extract the month from a given date.

select EXTRACT(month FROM timestamp);
select EXTRACT(century FROM timestamp);

Suppose you have given a dummy table in which we have to create a column in which we have to extract the month of their date of birth ??

select name, date_of_birth, EXTRACT(month FROM DATE(date_of_birth)) as month_of_birth from student;

This function can only extract values from the timestamp not from the simple date. If we have to extract from the date as I’ve done in the above example, then you need to type cast the date into timestamp using DATE(date).

AGE

This special function is used to find the difference between the two timestamps. As you understand from the name as well, this can be used to find the age.

select AGE( upper_bound, lower_bound);

This function takes two parameters, first one is the upper bound, and second one is the lower bound. This will return the gap between the upper bound and the lower bound.

Output of AGE function

Suppose you have given a question in which you have a table with two columns name, and date_of_birth and you have to make a new column which will store the current age of each student ??

select name, date_of_birth, EXTRACT(year FROM (AGE ( NOW (), date_of_birth))) as current_age from student;

Finding the age from the date of birth using AGE

NULLIF

This functions returns null if the two arguments of the function are same, else it will return the first argument. This function combined with COALESCE can help to handle “divide by zero” error.

select NULLIF (argument_1, argument_2);

As you can see from the above image, in the example1, the arguments are different, so NULLIF returns the first argument i.e. 2. In the second example, the arguments are same, so this returns NULL.

COALESCE

Now this comes our last function. This is very interesting and helpful function in my opinion.

Suppose you have a table in which some of the entries are NULL, and you need to show something else in the null entries ??

So, how will you do it??

COALESCE is the function which can help you with this. This function takes number of parameters and returns the first NON NULL value from the sequence.

Let suppose, you use COALESCE (null, null, null, 2, 4, 5), then this returns 2, as 2 is the first non-null parameter.

select COALESCE ( argument1, argument2, argument3 );

Handling NULL values using COALESCE

As from the above example, you can see that coalesce fills all the entries in which email field is null with an alias we provided.

This can also help in handling “divide by zero”. When we divide something with zero, it will throw an error. To handle this and not to stop the execution, we can create an exception using NULLIF & COALESCE.

postgres=# select 100/0;
ERROR: division by zero
For this, we can write a query like this:
postgres=# select COALESCE (100/ NULLIF (0, 0), 0);

Now the implementation part, suppose you have given a table of two columns (a, b), and you have to create a third column having the value of a/b ??

select a, b, ROUND (COALESCE (a/ NULLIF (b, 0), 0), 2) as a_divided_by_b from division;

Handling divide by zero error

Thank you for sparing your time for this article. Hope this will help you !!

If you have any doubt, please feel free to reach out to me on linkedin.