Log In Try for free

Home Time series basics Postgres basics Postgres guides Postgres best practices Postgres extensions Postgres for real-time analytics

Performance

Schema design

PostgreSQL Performance Tuning: Designing and Implementing Your Database Schema

Guide to PostgreSQL Performance

Performance tuning

PostgreSQL Performance Tuning: Key Parameters PostgreSQL Performance Tuning: Optimizing Database Indexes How to Reduce Bloat in Large PostgreSQL Tables PostgreSQL Performance Tuning: How to Size Your Database

Partitioning

Determining the Optimal Postgres Partition Size Navigating Growing PostgreSQL Tables With Partitioning (and More)When to Consider Postgres Partitioning Pg_partman vs. Hypertables for Postgres Partitioning

Database design and modeling

An Intro to Data Modeling on PostgreSQL Designing Your Database Schema: Wide vs. Narrow Postgres Tables Best Practices for Time-Series Data Modeling: Single or Multiple Partitioned Table(s) a.k.a. Hypertables Best Practices for (Time-)Series Metadata Tables Guide to PostgreSQL Database Design

Database replication

A PostgreSQL Database Replication Guide

Data management

Understanding PostgreSQL Tablespaces Guide to Postgres Data Management Handling Large Objects in Postgres

Data aggregation

How PostgreSQL Data Aggregation Works

Scaling postgres

Building a Scalable Database A Guide to Scaling PostgreSQL

Database operations

Guide to PostgreSQL Database Operations

JSON

How to Query JSONB in PostgreSQL How to Index JSONB Columns in PostgreSQL SQL/JSON Data Model and JSON in SQL: A PostgreSQL Perspective How to Query JSON Metadata in PostgreSQL

Database indexes

How to Monitor and Optimize PostgreSQL Index Performance

Query optimization

Explaining PostgreSQL EXPLAIN What Is a PostgreSQL Temporary View?Optimizing Array Queries With GIN Indexes in PostgreSQL Recursive Query in SQL: What It Is, and How to Write One

Database backups and restore

A Guide to pg_restore (and pg_restore Example)

Data analysis

A Guide to Data Analysis on PostgreSQL How to Compute Standard Deviation With PostgreSQL

Data transformation

How to Use PostgreSQL for Data Transformation

Database tools and libraries

How to Use Psycopg2: The PostgreSQL Adapter for Python Top PostgreSQL Drivers for Python

Database security

What Is Audit Logging and How to Enable It in PostgreSQL Guide to PostgreSQL Security

Published at Aug 3, 2023

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.