WEBVTT 00:00:00.330 --> 00:00:01.890 Hi guys and welcome back. 00:00:01.890 --> 00:00:05.150 In this video we're gonna talk about Views. 00:00:05.150 --> 00:00:06.900 line:20% So this is gonna be a bit of a longer one, 00:00:06.900 --> 00:00:07.980 line:20% but please bear with me. 00:00:07.980 --> 00:00:09.180 line:20% Views are pretty important. 00:00:09.180 --> 00:00:12.280 line:20% They can really help make your life much easier. 00:00:12.280 --> 00:00:14.670 But what are Views? 00:00:14.670 --> 00:00:17.820 For normal Views, a name for a query. 00:00:17.820 --> 00:00:19.670 They behave like a table in many ways 00:00:19.670 --> 00:00:22.160 so they're not just a function 00:00:22.160 --> 00:00:24.430 and every time the View is accessed, 00:00:24.430 --> 00:00:27.743 the query that is associated with that View is executed. 00:00:28.850 --> 00:00:31.080 And if this happens, 00:00:31.080 --> 00:00:32.540 if every time the View is accessed 00:00:32.540 --> 00:00:34.340 that means the query is executed, 00:00:34.340 --> 00:00:38.260 then we say the View is not materialised, 00:00:38.260 --> 00:00:40.640 because the data that the View represents 00:00:40.640 --> 00:00:42.940 is never essentially stored in disc. 00:00:42.940 --> 00:00:44.990 Every time you wanna access the View 00:00:44.990 --> 00:00:46.630 you run the query. 00:00:46.630 --> 00:00:48.240 Let's talk about what Views really are. 00:00:48.240 --> 00:00:52.010 So here we've got a relatively complicated query 00:00:52.010 --> 00:00:55.440 from the "Distinct On" lecture where we could have 00:00:55.440 --> 00:00:58.480 the top voted option for each poll. 00:00:58.480 --> 00:01:00.930 And let's say that you're accessing this quite often, 00:01:00.930 --> 00:01:02.990 so often in fact that you don't want 00:01:02.990 --> 00:01:05.300 to be typing this query out all the time. 00:01:05.300 --> 00:01:08.280 Well, what you might do is you might create a View out of it 00:01:08.280 --> 00:01:10.830 and that essentially saves it under a name. 00:01:10.830 --> 00:01:13.440 So what you would do is you would go ahead 00:01:13.440 --> 00:01:15.310 and do "create View", 00:01:15.310 --> 00:01:16.327 you would give it a name, 00:01:16.327 --> 00:01:18.440 "most voted options" in this case 00:01:18.440 --> 00:01:19.780 and then you would say "as" 00:01:19.780 --> 00:01:21.630 and then you would put in your query. 00:01:21.630 --> 00:01:23.040 Then you can do that. 00:01:23.040 --> 00:01:25.930 Select "star" from most voted options 00:01:25.930 --> 00:01:27.880 and then when you do that, 00:01:27.880 --> 00:01:30.650 Postgre's behind the scenes would run your initial query 00:01:30.650 --> 00:01:33.403 and it would give you, in this case, all the columns. 00:01:34.940 --> 00:01:36.550 Couple of benefits, you could potentially 00:01:36.550 --> 00:01:38.870 not select "star" for most voted options 00:01:38.870 --> 00:01:42.370 so you can potentially select fewer columns if you want 00:01:42.370 --> 00:01:44.180 and you can do other things in this query, 00:01:44.180 --> 00:01:45.810 like more filtering, et cetera, 00:01:45.810 --> 00:01:47.720 if that's what you want to do. 00:01:47.720 --> 00:01:50.980 So a View is essentially a table 00:01:50.980 --> 00:01:53.710 but the data is never actually stored on disc, 00:01:53.710 --> 00:01:55.030 the data is always fetched 00:01:55.030 --> 00:01:57.233 from running the underlying query. 00:01:58.370 --> 00:02:01.490 However, it also comes with a couple extra features. 00:02:01.490 --> 00:02:03.660 You've got updatable Views. 00:02:03.660 --> 00:02:07.130 These are often called "simple Views" in the documentation. 00:02:07.130 --> 00:02:11.000 If a View has one entry in its "from" list, 00:02:11.000 --> 00:02:13.740 so it doesn't select from multiple tables, 00:02:13.740 --> 00:02:16.850 it does not use any of these clauses: 00:02:16.850 --> 00:02:20.070 WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET. 00:02:20.070 --> 00:02:22.020 It does not use set operations, 00:02:22.020 --> 00:02:23.150 which we haven't looked at 00:02:23.150 --> 00:02:24.470 but they exist and they allow you 00:02:24.470 --> 00:02:27.180 to use tables as if they were sets, essentially 00:02:27.180 --> 00:02:29.970 Does not use select aggregates, window functions, 00:02:29.970 --> 00:02:31.690 or set-returning functions. 00:02:31.690 --> 00:02:34.150 Then what you have is a simple View 00:02:34.150 --> 00:02:38.360 so clearly these here are not so simple 00:02:38.360 --> 00:02:41.420 and what that means is these that are not simple 00:02:41.420 --> 00:02:43.760 are not updatable. 00:02:43.760 --> 00:02:45.720 Let's talk about what that means. 00:02:45.720 --> 00:02:49.230 Let's imagine we've got this dataset of employees. 00:02:49.230 --> 00:02:50.750 We've got the IDs, their names, 00:02:50.750 --> 00:02:52.533 their salaries, and the department. 00:02:53.430 --> 00:02:55.997 A useless View might be this one. 00:02:55.997 --> 00:02:58.130 "CREATE VIEW, useless employee View 00:02:58.130 --> 00:03:00.510 AS SELECT star FROM employees.". 00:03:00.510 --> 00:03:02.700 Not terribly useful but you can see 00:03:02.700 --> 00:03:04.570 that it is a simple View. 00:03:04.570 --> 00:03:06.330 It doesn't have any of the things 00:03:06.330 --> 00:03:07.780 that we discussed a moment ago. 00:03:07.780 --> 00:03:10.190 It has a simple entry in its "from" list, 00:03:10.190 --> 00:03:12.470 it's not using any set operations 00:03:12.470 --> 00:03:15.030 or any aggregates or anything like that. 00:03:15.030 --> 00:03:17.470 What this means is that because it's a simple View 00:03:17.470 --> 00:03:19.890 it's an updatable View 00:03:19.890 --> 00:03:22.640 and you can therefore insert into it 00:03:22.640 --> 00:03:24.123 if so you desire. 00:03:25.530 --> 00:03:28.010 Now, again not a terribly useful one here 00:03:28.010 --> 00:03:32.430 but you can see that a View is not just a name for a query. 00:03:32.430 --> 00:03:34.990 Behind the scenes it's more than that 00:03:34.990 --> 00:03:38.210 and therefore you can insert into it if you want. 00:03:38.210 --> 00:03:40.260 You can have Views with "where" 00:03:40.260 --> 00:03:43.340 and they can still be simple Views. 00:03:43.340 --> 00:03:45.147 So you can create a View of high earners, 00:03:45.147 --> 00:03:46.970 "WHERE SELECT star from employees", 00:03:46.970 --> 00:03:50.450 where they have a high salary of 8000 or more. 00:03:50.450 --> 00:03:53.410 You can still insert into this View, 00:03:53.410 --> 00:03:55.390 even without following the "where" 00:03:55.390 --> 00:03:58.720 and that means that you can do "insert" into high earners, 00:03:58.720 --> 00:04:00.710 somebody that's not a high earner. 00:04:00.710 --> 00:04:02.580 So this might look a bit weird. 00:04:02.580 --> 00:04:04.770 Right, we've got here our simple View 00:04:04.770 --> 00:04:07.090 and we're doing this "where" clause 00:04:07.090 --> 00:04:09.550 which means whenever we select from this View, 00:04:09.550 --> 00:04:13.050 we're only gonna get people with 8000 or more as a salary 00:04:13.050 --> 00:04:16.410 but you can still insert people 00:04:16.410 --> 00:04:19.960 that don't have a high salary into this View. 00:04:19.960 --> 00:04:22.340 What happens if you do that is essentially, 00:04:22.340 --> 00:04:23.760 just goes into the table 00:04:23.760 --> 00:04:26.290 and if you select from the View again 00:04:26.290 --> 00:04:29.250 this row would not appear in the "select" 00:04:29.250 --> 00:04:30.660 but you can insert. 00:04:30.660 --> 00:04:31.870 What you can't do though with a View 00:04:31.870 --> 00:04:34.360 is you can't update or delete rows 00:04:34.360 --> 00:04:36.310 that are not in the View. 00:04:36.310 --> 00:04:40.160 So essentially lets you manipulate the View 00:04:40.160 --> 00:04:43.500 without really going into danger zone 00:04:43.500 --> 00:04:45.820 of potentially deleting or changing data 00:04:45.820 --> 00:04:47.870 from the View because you've got 00:04:47.870 --> 00:04:49.320 this "where" clause in there. 00:04:50.170 --> 00:04:52.810 You can avoid the ability to do that 00:04:52.810 --> 00:04:55.120 if you use local check option. 00:04:55.120 --> 00:04:56.960 So if you define a View with local check option 00:04:56.960 --> 00:04:59.280 then you can't do anything that violates its definition, 00:04:59.280 --> 00:05:02.330 for example if we have this create View high earners 00:05:02.330 --> 00:05:06.220 as the same stuff but we add with local check option 00:05:06.220 --> 00:05:09.070 that tells Postgre that whenever we try to do anything 00:05:09.070 --> 00:05:10.510 that modifies this View 00:05:10.510 --> 00:05:12.460 it's gonna check the "where" clause here 00:05:12.460 --> 00:05:14.200 and see if that's permitted. 00:05:14.200 --> 00:05:16.597 So if you try to do something like update high earners, 00:05:16.597 --> 00:05:19.400 "SET salary equal 5000 WHERE ID equals six", 00:05:19.400 --> 00:05:20.360 you're not gonna be able to do that. 00:05:20.360 --> 00:05:22.970 If you try to insert something that doesn't fit 00:05:22.970 --> 00:05:24.060 the "where" clause then you're not 00:05:24.060 --> 00:05:26.703 gonna be able to do that either and that's that. 00:05:27.810 --> 00:05:30.830 However, using local check option 00:05:30.830 --> 00:05:33.690 means that you have to follow the View's definition 00:05:33.690 --> 00:05:37.120 but you don't have to follow sub-View definitions. 00:05:37.120 --> 00:05:39.030 And for that we have cascaded check option. 00:05:39.030 --> 00:05:41.300 So let's look at an example for that. 00:05:41.300 --> 00:05:43.060 Here we've got high earners 00:05:43.060 --> 00:05:45.000 where the salary is greater than 8000. 00:05:45.000 --> 00:05:47.513 This is the base View and has no checks. 00:05:48.470 --> 00:05:51.330 Here we've got the high salary researchers 00:05:51.330 --> 00:05:54.120 which select from high earners 00:05:54.120 --> 00:05:56.350 but the department must be research 00:05:56.350 --> 00:05:58.800 and this has local check option. 00:05:58.800 --> 00:06:00.760 So if you try to insert into here, 00:06:00.760 --> 00:06:03.480 you can't insert "product" employees 00:06:03.480 --> 00:06:06.120 but you can insert "low earners" 00:06:06.120 --> 00:06:10.440 because this View is only checking the local "where" clause. 00:06:10.440 --> 00:06:14.033 It's not checking the sub-View "where" clause. 00:06:14.890 --> 00:06:16.460 Finally, if you have a View 00:06:16.460 --> 00:06:18.550 of high salary researchers for example, 00:06:18.550 --> 00:06:22.253 that does the same thing but with a cascaded check option. 00:06:23.120 --> 00:06:27.050 Then you can only insert research employees and high earners 00:06:27.050 --> 00:06:29.550 because this one is gonna check this "where" clause 00:06:29.550 --> 00:06:31.610 and then it's gonna go down to the sub-View and check that 00:06:31.610 --> 00:06:33.810 and if there were more cascaded Views 00:06:33.810 --> 00:06:35.660 then it would check those as well. 00:06:35.660 --> 00:06:37.640 You can also see from here that you can build 00:06:37.640 --> 00:06:39.350 a hierarchy of Views 00:06:39.350 --> 00:06:42.960 but remember every time you select in here from a View, 00:06:42.960 --> 00:06:44.820 you're running its query. 00:06:44.820 --> 00:06:47.100 So if you have some part of your application accessing 00:06:47.100 --> 00:06:49.480 high salary researchers you are actually 00:06:49.480 --> 00:06:51.780 doing a "select" of high salary researchers 00:06:51.780 --> 00:06:53.780 and a "select" of high earners 00:06:53.780 --> 00:06:55.210 and a "select" from employees, 00:06:55.210 --> 00:06:56.500 so if you do a lot of these 00:06:56.500 --> 00:06:59.250 it can potentially become a bit slower. 00:06:59.250 --> 00:07:01.790 So use Views for like really complicated stuff 00:07:01.790 --> 00:07:03.560 that you don't wanna be repeating over and over, 00:07:03.560 --> 00:07:05.643 not necessarily for really simple stuff. 00:07:07.350 --> 00:07:10.070 What are non-updatable columns? 00:07:10.070 --> 00:07:11.670 These are columns that are generated 00:07:11.670 --> 00:07:13.420 and they can't be changed in the View. 00:07:13.420 --> 00:07:17.600 For example, columns that are a result of a sub-query. 00:07:17.600 --> 00:07:19.047 If you have something like this View, 00:07:19.047 --> 00:07:20.920 "projects per high earner", 00:07:20.920 --> 00:07:24.000 where that is the "find" as employees dot star, 00:07:24.000 --> 00:07:25.470 everything the employees table, 00:07:25.470 --> 00:07:28.470 and the count of projects where the employee ID 00:07:28.470 --> 00:07:32.140 matches the outer table's ID as project count. 00:07:32.140 --> 00:07:35.600 This here is a generated column 00:07:35.600 --> 00:07:39.120 because it's not part of the table where we selected, 00:07:39.120 --> 00:07:42.930 therefore if you try to "insert" or update a View, 00:07:42.930 --> 00:07:45.640 this View, you're not going to be able to affect 00:07:45.640 --> 00:07:48.940 the generated column but you will still be able to insert 00:07:48.940 --> 00:07:52.110 or update things from the employees table. 00:07:52.110 --> 00:07:54.400 So that's something to take into account with Views, 00:07:54.400 --> 00:07:56.150 you can still use it as a normal View, 00:07:56.150 --> 00:07:58.713 you just can't affect the generated columns. 00:08:00.390 --> 00:08:02.800 So this was a quick primer on Views. 00:08:02.800 --> 00:08:04.800 Essentially, as I've been saying, 00:08:04.800 --> 00:08:07.910 Views are names for queries 00:08:07.910 --> 00:08:11.100 and you can use them as tables in some places 00:08:11.100 --> 00:08:13.820 and it's worth looking at the official documentation 00:08:13.820 --> 00:08:16.490 for more examples and more edge cases 00:08:16.490 --> 00:08:18.510 'cause there's quite a lot of them 00:08:18.510 --> 00:08:21.670 but as well as Views, which are useful but you know, 00:08:21.670 --> 00:08:23.260 they have their limitations. 00:08:23.260 --> 00:08:26.290 Especially the fact that the query has to run every time. 00:08:26.290 --> 00:08:28.760 You have something called materialised Views. 00:08:28.760 --> 00:08:32.250 And, naturally, earlier I said that the View 00:08:32.250 --> 00:08:33.360 where you have to execute the query 00:08:33.360 --> 00:08:35.600 every time is not materialised. 00:08:35.600 --> 00:08:37.520 Clearly materialised Views are the ones 00:08:37.520 --> 00:08:40.440 where you don't execute every time. 00:08:40.440 --> 00:08:42.660 The data is saved to the database permanently, 00:08:42.660 --> 00:08:46.490 so accessing the View does not rerun the query. 00:08:46.490 --> 00:08:48.940 That does mean you can have stale data in your query 00:08:48.940 --> 00:08:51.650 because the View does not run the query. 00:08:51.650 --> 00:08:54.700 So you can manually refresh the View whenever you want 00:08:54.700 --> 00:08:58.060 so that the query does re-run. 00:08:58.060 --> 00:08:59.350 You can create a materialised View 00:08:59.350 --> 00:09:00.616 the same way as you do a view, 00:09:00.616 --> 00:09:03.210 but you just put the materialised key word in there 00:09:03.210 --> 00:09:04.590 and whenever you wanna refresh it, 00:09:04.590 --> 00:09:06.410 you just say "REFRESH MATERIALISED VIEW" 00:09:06.410 --> 00:09:08.530 and you give it a name and that's gonna re-run the query 00:09:08.530 --> 00:09:12.980 and update the table that creates behind the scenes. 00:09:12.980 --> 00:09:14.380 Materialised Views are pretty good 00:09:14.380 --> 00:09:15.690 for when you don't really care 00:09:15.690 --> 00:09:18.530 about having the latest data at all times 00:09:18.530 --> 00:09:22.090 and you want speed, especially if your Views are slow. 00:09:22.090 --> 00:09:23.850 You can use a materialised View 00:09:23.850 --> 00:09:26.180 and that is gonna be much faster to access 00:09:26.180 --> 00:09:27.760 but of course it's gonna take up 00:09:27.760 --> 00:09:29.480 more disc space in exchange 00:09:29.480 --> 00:09:30.590 because you actually have to store 00:09:30.590 --> 00:09:32.820 the data in the disc. 00:09:32.820 --> 00:09:34.790 So something good to remember for when 00:09:34.790 --> 00:09:36.550 you have long, complicated, 00:09:36.550 --> 00:09:38.630 computationally intensive Views 00:09:38.630 --> 00:09:40.300 but you're not that concerned 00:09:40.300 --> 00:09:42.540 about always having the latest data, 00:09:42.540 --> 00:09:45.070 you can use a materialised View for that. 00:09:45.070 --> 00:09:47.020 All right, thank you guys for joining me in this video, 00:09:47.020 --> 00:09:49.730 I hope you've learned something about Views and mat-Views. 00:09:49.730 --> 00:09:51.263 I'll see you in the next one.