How Can I Efficiently Create and Populate a Temporary Table in SQL?

Creating Temporary Tables in SQL: A Quick Overview

Your query aims to create a temporary table named 'temp1' and populate it with data that has a specific 'register_type' ('%gen%'). However, the issue arises from the usage of 'CREATE TABLE' instead of 'CREATE TABLE AS.'

The Correct Approach

To create a temporary table and populate it with data from a query, you should use 'CREATE TABLE AS':

CREATE TEMP TABLE temp1 AS
SELECT dataid
     , register_type
     , timestamp_localtime
     , read_value_avg
FROM   rawdata.egauge
WHERE  register_type LIKE '%gen%'
ORDER  BY dataid, timestamp_localtime;

This command creates a temporary table in memory and populates it with the resulting rows from the query. It resembles a regular table but resides in RAM.

Understanding Temporary Tables in PostgreSQL

Temporary tables are visible within the current session and disappear upon session termination. Alternatively, you can specify 'ON COMMIT DROP' to have them disappear at the end of the transaction.

Alternatives to Temporary Tables

For dynamic temporary tables, consider using 'CREATE VIEW,' which provides a continuously up-to-date snapshot of data. However, 'CREATE TABLE AS' is preferred for static snapshots.

While 'SELECT INTO' can also create temporary tables, it's considered discouraged. Instead, use 'CREATE TABLE AS.' 'CREATE TABLE LIKE' only copies the table structure and not the data.

For temporary tables solely used within a single query, 'derived tables' in CTEs or subqueries offer a lightweight alternative.

The above is the detailed content of How Can I Efficiently Create and Populate a Temporary Table in SQL?. For more information, please follow other related articles on the PHP Chinese website!