WEBVTT 00:07.700 --> 00:10.490 I welcome you back again to another lecture in PostgreSQL. 00:10.520 --> 00:16.190 And in this video, let me go ahead and teach you about the Coalesce function in PostgreSQL. 00:16.220 --> 00:18.620 So what is the Coalesce function? 00:18.740 --> 00:26.030 The Coalesce function is a function in PostgreSQL that accepts a list of arguments, and then it goes 00:26.030 --> 00:30.350 on to return the very first argument, which is non-null. 00:30.980 --> 00:35.630 Let me give an example of this, because this is argument that you need to experience. 00:35.630 --> 00:36.830 What actually happens. 00:36.830 --> 00:45.350 If I go ahead and say select and I'll go ahead and put in that function, and the function name is a 00:45.380 --> 00:47.000 cumulus. 00:49.280 --> 00:50.420 So this is a function. 00:50.420 --> 00:53.840 You can see that it takes the keyword and it has changed the color. 00:53.840 --> 00:59.030 And now if I go ahead and move inside here and put in no. 00:59.030 --> 01:01.340 And let's go ahead and execute this query. 01:01.580 --> 01:04.160 It goes on to give us the Coalesce text. 01:04.160 --> 01:05.810 And it printed out. 01:05.890 --> 01:07.330 No, no. 01:07.330 --> 01:12.190 This argument can take one or more of some values. 01:12.190 --> 01:19.660 If I go ahead and insert in something like one and go ahead and execute this query, it goes on to print 01:19.690 --> 01:22.600 out one instead of no. 01:22.600 --> 01:25.510 So it takes a non-null argument first. 01:25.510 --> 01:28.120 That is, the very first argument is going to print out. 01:28.720 --> 01:36.820 So what we have now and uh, have uh any argument that is not known, it goes on to print out the one 01:36.820 --> 01:37.780 that is not null. 01:37.780 --> 01:39.910 So let me go ahead and change that. 01:39.940 --> 01:42.640 Maybe you might say, hey, this is because of the position. 01:42.670 --> 01:46.390 Go ahead and put in one first and no and execute this query. 01:46.420 --> 01:48.880 And let's see how one printed out. 01:48.910 --> 01:55.330 Because whatever it gives you is the very first argument which is non-null. 01:55.540 --> 02:05.710 And if I go ahead and uh, let me see, go ahead and uh, inserts, let me say three and two. 02:07.660 --> 02:07.930 All right. 02:07.960 --> 02:10.180 Let me start three and one. 02:10.270 --> 02:13.110 It was first of all print out at 341. 02:13.140 --> 02:14.280 Go ahead and check it out. 02:14.310 --> 02:18.240 Three because it's going to print at the very first moment. 02:18.270 --> 02:19.500 No argument. 02:19.530 --> 02:26.340 What if all you have is idea and no coherent changes to also no. 02:26.490 --> 02:26.760 Correct. 02:26.790 --> 02:28.410 I'm going to make this to be an uppercase. 02:28.410 --> 02:33.600 No problem that all just the same and execute this query I have a no printed out. 02:33.630 --> 02:37.530 What if I have any other value which is not null? 02:37.560 --> 02:44.970 That is what is going to print out now how it is important in this discussion in PostgreSQL. 02:45.000 --> 02:48.270 Let me go ahead and give an example based on what we have. 02:48.300 --> 02:57.330 Let's go ahead and remove all these, and let me go ahead and select the email when I select an email 02:58.860 --> 03:00.600 from students. 03:00.600 --> 03:02.670 And let's go ahead and execute this query. 03:02.700 --> 03:04.170 Now I have this email. 03:04.200 --> 03:09.270 Remember I have emails that are no and have this emails that are known. 03:09.270 --> 03:13.650 And this I know because these are ones which don't have email attached to them. 03:13.650 --> 03:16.530 And what if I want to replace this? 03:16.530 --> 03:22.800 I want to, uh, put a convex function right in here and, uh, right in there. 03:22.830 --> 03:28.590 If I should go ahead and do that, it's going to print out any value I put in there instead of no, 03:28.620 --> 03:30.900 that is the very first thing is going to print out. 03:30.930 --> 03:32.820 Let me go ahead and I'll show you an example of this. 03:32.820 --> 03:38.340 So if I go right inside here and say select and I'll go ahead and put a call this function. 03:38.340 --> 03:39.240 And uh. 03:41.580 --> 03:43.110 So this is a cool function. 03:43.110 --> 03:48.480 And let me go ahead and uh, inside this inside this uh, bracket. 03:48.480 --> 03:53.370 And uh, if I run this, I'll still get email now, right in here. 03:53.400 --> 04:00.030 Now, what if I add in another argument, just like we have before where our email, we have some that 04:00.030 --> 04:00.540 are. 04:00.570 --> 04:01.230 No. 04:01.230 --> 04:07.020 And let me go ahead and add something like uh for and execute this query. 04:07.680 --> 04:08.130 Okay. 04:08.160 --> 04:12.540 I have uh, this on I need to put this inside a string format. 04:12.570 --> 04:15.210 Let me say add info right in here and execute this query. 04:15.240 --> 04:15.870 Check it out. 04:15.900 --> 04:19.350 It goes into print out for everywhere. 04:19.440 --> 04:22.290 What for is not a better thing to use. 04:22.380 --> 04:25.340 Then go ahead and say for any email that are not. 04:25.340 --> 04:31.970 No, go ahead and check that Twitter email not provided or email not exist or email. 04:32.270 --> 04:34.130 Um, not email. 04:34.160 --> 04:39.740 Okay, let's go ahead and see new email and go ahead and execute this query and check it out. 04:39.740 --> 04:43.340 So for anyone who doesn't provide email we're going to write no email. 04:44.360 --> 04:46.580 Now what happens. 04:46.580 --> 04:49.520 The very first email here is a not no sorry. 04:49.550 --> 04:51.140 The very first email is null. 04:51.140 --> 04:56.180 So it takes the value which is not null, which is not a null value. 04:56.210 --> 04:57.710 Why this is a null value. 04:57.740 --> 05:03.200 The very second one is a not null value, because this has a value of string and it goes into input 05:03.200 --> 05:04.040 that day. 05:04.100 --> 05:11.000 So I believe we understand what actually happens and how this function is being is being used in PostgreSQL. 05:11.000 --> 05:17.630 So I believe the very first example I gave you before we tried out this email, uh, function, you 05:17.630 --> 05:25.940 might try to understand what actually went on because it keeps in the it puts in the not null values 05:25.940 --> 05:27.470 first before the null values. 05:27.470 --> 05:34.000 So it checks in the functions which are not null and is set them right in there while it ignores the 05:34.000 --> 05:34.330 null. 05:34.360 --> 05:36.550 But when you have just only the null. 05:36.580 --> 05:42.130 Let me say I have only the email right in there and the query is going to add no for day. 05:42.130 --> 05:46.720 But if I have any other option right in here, which is not email, I. 05:46.720 --> 05:48.610 So I have clicked on it. 05:48.610 --> 05:50.800 So it selects a new email. 05:51.160 --> 05:51.700 All right. 05:51.700 --> 05:52.570 So that is okay. 05:52.600 --> 05:53.680 There is no issue at all. 05:53.710 --> 05:54.820 Everything is okay. 05:55.090 --> 05:58.060 So if you go ahead and execute the query that is what you're going to get. 05:58.060 --> 06:00.400 You can change this to any name or anything you want. 06:00.430 --> 06:05.530 Maybe email not provided or email does not exist or nothing, but that is what is going to print out 06:05.530 --> 06:11.470 before it prints out the null value, which is the email and the nominal values for the email that doesn't 06:11.470 --> 06:13.180 have anything attached to them. 06:13.180 --> 06:15.280 So whatever, that doesn't have anything attached to them. 06:15.280 --> 06:19.750 Instead of writing out null, it goes on to print out the value no email. 06:19.750 --> 06:20.830 So that is it. 06:20.860 --> 06:25.990 Check it out, practice with it, and if you have any questions, go ahead and use the questions section 06:25.990 --> 06:28.780 and I'm going to see you in the next video lecture.