Performance
Performance tuning
Database design and modeling
Database replication
Data analysis
Data aggregation
Scaling postgres
Database security
Data management
Database operations
Query optimization
Database tools and libraries
Database indexes
Database backups and restore
Published at Aug 3, 2023
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.