What Is a PostgreSQL Temporary View?
A temporary view in PostgreSQL is a database object that's only available for the duration of a session. It's similar to a regular view, but it disappears once the session ends. Temporary views can be useful when you need to work with a subset of data multiple times within a single session.
However, once your session ends or you disconnect from the database, PostgreSQL will automatically drop the temporary view.
Here's how you create a temporary view:
CREATE TEMPORARY VIEW temp_view AS
SELECT column1, column2
FROM table_name
WHERE condition;A PostgreSQL temporary view is more of a convenience, really. It may help the query writer “clear its head” a little, and it also simplifies the query into multiple parts, much like a common table expression (CTE).
There is one additional vantage, albeit very very small: creating a temporary view is not (write-ahead log) WAL-logged, which may seem pretty trivial, but it adds up. While creating a regular view is WAL-logged (just creating it, not selecting the data), a CTE is not—unless it modifies the data. Want to learn more about PostgreSQL basics? Check out our guide to Understanding PostgreSQL Functions.