I have a question related to "View" in SQL If I created a View, would it disappear when I restart my PC? "I have a local DB"
I've searched a lot of documents online and they almost say that it gets removed, However, I have created a View and restarted my PC and the view is still there (isn't removed) I'm wondering if you could help me with how a view and a temporary table behave.
Regards
In PostgreSQL, a VIEW is something kind of like a macro or shortcut. Basically, it stores the parsed form of a query so that it can be called with a simple name. The idea is that the output of a query is a table -- therefore, it would be logically simpler to give a table-name to that output, and you can SELECT * FROM <table-name>. This is what a VIEW ultimately is, and it is created by CREATE VIEW <table-name> AS <query that would produce table-name>.
With that in mind, this shortcut is not something that is persisted to disk, but simply available to users for sake of simplicity and convenience, and this shortcut lives on between reboots and restarts. More information about VIEW can be found in the documentation
A TEMPORARY TABLE, however, is a table that exists only until the end of your session. It can be seen only by the current user of the session, and is discarded at the end of that session. It does not live on between reboots/restarts. However, it is persisted to disk (but is immediately removed after the session ends). Conceivably, if the machine is unplugged and there is no chance for Postgres to drop the temporary table, it could live on, but would not be accessible by any other session. Autovacuum will ultimately remove that table when the autovacuum process begins. More information about TEMPORARY TABLE can also be found in the documentation
Finally, a MATERLIALIZED VIEW is something like a VIEW, but is not simply a shortcut for a longer query. Rather, it is a snapshot of a query that is stored on disk, accessible by multiple sessions, and lives on between reboots/restarts. With MATERIALIZED VIEW, it is possible to get the output of a query and store it for future use, potentially saving you from having to re-run a really long query that takes minutes or hours to complete. More information about MATERIALIZED VIEW can be found in the documentation
