I welcome you back again to another lecture in PostgreSQL.
And in this video, let me go ahead and teach you about the Coalesce function in PostgreSQL.
So what is the Coalesce function?
The Coalesce function is a function in PostgreSQL that accepts a list of arguments, and then it goes
on to return the very first argument, which is non-null.
Let me give an example of this, because this is argument that you need to experience.
What actually happens.
If I go ahead and say select and I'll go ahead and put in that function, and the function name is a
cumulus.
So this is a function.
You can see that it takes the keyword and it has changed the color.
And now if I go ahead and move inside here and put in no.
And let's go ahead and execute this query.
It goes on to give us the Coalesce text.
And it printed out.
No, no.
This argument can take one or more of some values.
If I go ahead and insert in something like one and go ahead and execute this query, it goes on to print
out one instead of no.
So it takes a non-null argument first.
That is, the very first argument is going to print out.
So what we have now and uh, have uh any argument that is not known, it goes on to print out the one
that is not null.
So let me go ahead and change that.
Maybe you might say, hey, this is because of the position.
Go ahead and put in one first and no and execute this query.
And let's see how one printed out.
Because whatever it gives you is the very first argument which is non-null.
And if I go ahead and uh, let me see, go ahead and uh, inserts, let me say three and two.
All right.
Let me start three and one.
It was first of all print out at 341.
Go ahead and check it out.
Three because it's going to print at the very first moment.
No argument.
What if all you have is idea and no coherent changes to also no.
Correct.
I'm going to make this to be an uppercase.
No problem that all just the same and execute this query I have a no printed out.
What if I have any other value which is not null?
That is what is going to print out now how it is important in this discussion in PostgreSQL.
Let me go ahead and give an example based on what we have.
Let's go ahead and remove all these, and let me go ahead and select the email when I select an email
from students.
And let's go ahead and execute this query.
Now I have this email.
Remember I have emails that are no and have this emails that are known.
And this I know because these are ones which don't have email attached to them.
And what if I want to replace this?
I want to, uh, put a convex function right in here and, uh, right in there.
If I should go ahead and do that, it's going to print out any value I put in there instead of no,
that is the very first thing is going to print out.
Let me go ahead and I'll show you an example of this.
So if I go right inside here and say select and I'll go ahead and put a call this function.
And uh.
So this is a cool function.
And let me go ahead and uh, inside this inside this uh, bracket.
And uh, if I run this, I'll still get email now, right in here.
Now, what if I add in another argument, just like we have before where our email, we have some that
are.
No.
And let me go ahead and add something like uh for and execute this query.
Okay.
I have uh, this on I need to put this inside a string format.
Let me say add info right in here and execute this query.
Check it out.
It goes into print out for everywhere.
What for is not a better thing to use.
Then go ahead and say for any email that are not.
No, go ahead and check that Twitter email not provided or email not exist or email.
Um, not email.
Okay, let's go ahead and see new email and go ahead and execute this query and check it out.
So for anyone who doesn't provide email we're going to write no email.
Now what happens.
The very first email here is a not no sorry.
The very first email is null.
So it takes the value which is not null, which is not a null value.
Why this is a null value.
The very second one is a not null value, because this has a value of string and it goes into input
that day.
So I believe we understand what actually happens and how this function is being is being used in PostgreSQL.
So I believe the very first example I gave you before we tried out this email, uh, function, you
might try to understand what actually went on because it keeps in the it puts in the not null values
first before the null values.
So it checks in the functions which are not null and is set them right in there while it ignores the
null.
But when you have just only the null.
Let me say I have only the email right in there and the query is going to add no for day.
But if I have any other option right in here, which is not email, I.
So I have clicked on it.
So it selects a new email.
All right.
So that is okay.
There is no issue at all.
Everything is okay.
So if you go ahead and execute the query that is what you're going to get.
You can change this to any name or anything you want.
Maybe email not provided or email does not exist or nothing, but that is what is going to print out
before it prints out the null value, which is the email and the nominal values for the email that doesn't
have anything attached to them.
So whatever, that doesn't have anything attached to them.
Instead of writing out null, it goes on to print out the value no email.
So that is it.
Check it out, practice with it, and if you have any questions, go ahead and use the questions section
and I'm going to see you in the next video lecture.