How Do I Correctly Create and Use Temporary Tables in PostgreSQL?

Creating Temporary Tables in SQL

You're attempting to create a temporary table, but your query doesn't work. Here's why:

The correct syntax for creating a temporary table using PostgreSQL is:

CREATE TEMP TABLE table_name AS
SELECT columns FROM table WHERE conditions;

This executes the SELECT statement and creates a temporary table with the selected data.

In your case, change your query to:

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;

Remember, temporary tables are visible only within the current session and are dropped at the end of it. To create a temporary table that drops at the end of a transaction, use ON COMMIT DROP instead of TEMP.

If you need a dynamic representation of data, consider using CREATE VIEW instead. It provides a live view of data, reflecting changes made to underlying tables.

Alternatively, you can use a derived table (CTE) or subquery for single-query purposes. These options come with lower overhead compared to creating a temporary table.

The above is the detailed content of How Do I Correctly Create and Use Temporary Tables in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!