PostgreSQL explore - SQL Tutorial

- [Instructor] Postgres is considered one of the most advanced databases out there. Not only that it's open source, low maintenance and uses SQL. Compared to other databases, Postgres offers the most data types. They have 41 data types listed as built-in native data types and gives the option for the user to create more types on their own. It has ACID transaction isolation which stands for: atomicity, consistency, isolation and durability. Which not all databases are. ACID is a great standard to have in the database if your priority is data validity. Atomicity checks that the entire transaction either fails or succeeds, meaning if one part of it fails the entire transaction will fail. Consistency checks that any data change is changed under the constraints put in place, removing the risk of an illegal transaction. Isolation checks that the transaction would have remained the same if all transactions were done sequentially. Most transactions are done concurrently for efficiency. Finally, durability checks that the transaction won't get removed from system failures. Again, ACID may or may not be critical depending on what transactions in the database you are doing. A great example of where ACID transactions are important is with financial data. If you try to withdraw money out of an ATM and it errored you wouldn't want to see your bank account go lower without having taken the money out. Postgres is also known for other data structures. Because Postgres is an object relational database it provides the user a lot of freedom to create views, materialized views, stored procedures, user defined functions and user defined objects. The difference between a view and a materialized view is that a view does not store the results anywhere in the disc. Meaning every time you select from a view it will pull the latest data from the underlying tables. Now we're going to see an example of how a view and the materialized view differentiates. Now we're in the console and I'm going to show you the difference between a view and a materialized view. So first we're going to create a view. And we're going to do this by joining two tables on the customers and the orders table. So we're just going to call this the customers and orders. Let me just grab everything. And we created the view. So now let's check what's inside our view. I'm going to do that by doing a select star from the name of our view. And you can see it, with the comment that I made, and here we have our view. It just has 20 rows of everyone who is inside customer's table and the orders table. So this is important 'cause we're going to play with this column, the city column. Now we're going to create a materialized view. So we're going to go through a very similar process with what we did to create a view. So just going to create a materialized view, customer and order, and we're just going to pin mat inside it just so we know it's a materialized view versus the regular view. And we're going to do the same query. Awesome. Now we have created our materialized view. Now we're going to check what's inside our materialized view. So we can see in here it's the same information, 20 rows. Awesome. Now this is where we're going to see the difference between a view and a materialized view. First, we're going to make an update to our customer's table and we're going to do that by saying update customers, we're going to set the city to be New York. Sorry, I like using single quotes. Where the city is Philadelphia. So we can check inside our view where we just joined from the customer's table. All of the cities are Philadelphia. So let's run this. And now we're going to check inside the customer's table. This is the original table we used to join our materialized view and in our view. Select star. And we can see, even though the state is Pennsylvania we have New York all the way down. Awesome. Now we're going to check inside our materialized view. You can see that even though the customer's table has been updated to New York this still says Philadelphia. Let's see what's inside our view. You can see here even though it's the same query as the materialized view this automatically updated to show New York because we had updated it after we had created the view. The main reason for that is because every time you are running a view, it reruns the query. So when you're doing a select from the view it's rerunning this inner query here. When it's a materialized view it runs the query once and then it holds that data. So what if we wanted to update our materialized view to match the view in the new customers table? We can simply do something called refreshing the materialized view where we just say, refresh materialized view. Spelled materialized wrong. There we go. So we just refreshed it. So it basically reran the select query from here. We reran that. So we check what's inside our materialized view now, we'll see that it has updated to New York also. So that ends our quick demonstration on views versus materialized views. And again, there are so many things in Postgres that we can do besides this really simple example. We saw that this functionality is great for giving you the flexibility to design the most efficient way to run your day-to-day needs. Whether it be speeding up queries or minimizing operational work. Other databases can do this as well. Although not quite SQL standard compliant it's emphasis on complex operations, advanced features and data integrity makes Postgres one of the most popular databases today. Although not as scalable or performant as other databases I'll continue to show you why Postgres is still a strong and popular option for your data platform. I'll show you its nuances and its SQL dialect and how it integrates with the rest of the stack.