postgresql - Creating temporary tables in SQL

Question

I am trying to create a temporary table that selects only the data for a certain register_type. I wrote this query but it does not work:

$ CREATE TABLE temp1
(Select 
    egauge.dataid,
    egauge.register_type,
    egauge.timestamp_localtime,
    egauge.read_value_avg
from rawdata.egauge
where register_type like '%gen%'
order by dataid, timestamp_localtime ) $

I am using PostgreSQL.
Could you please tell me what is wrong with the query?

Answer 1

You probably want CREATE TABLE AS - also works for TEMPORARY (TEMP) tables:

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 creates a temporary table and copies data into it. A static snapshot of the data, mind you. It's just like a regular table, but resides in RAM if temp_buffers is set high enough. It is only visible within the current session and dies at the end of it. When created with ON COMMIT DROP it dies at the end of the transaction.

Temp tables come first in the default schema search path, hiding other visible tables of the same name unless schema-qualified:

• How does the search_path influence identifier resolution and the "current schema"

---

If you want dynamic, you would be looking for CREATE VIEW - a completely different story.

---

The SQL standard also defines, and Postgres also supports: SELECT INTO. But its use is discouraged:

It is best to use CREATE TABLE AS for this purpose in new code.

There is really no need for a second syntax variant, and SELECT INTO is used for assignment in plpgsql, where the SQL syntax is consequently not possible.

Related:

• Combine two tables into a new one so that select rows from the other one are ignored
• ERROR: input parameters after one with a default value must also have defaults in Postgres

---

CREATE TABLE LIKE (...) only copies the structure from another table and no data:

The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints.

---

If you need a "temporary" table just for the purpose of a single query (and then discard it) a "derived table" in a CTE or a subquery comes with considerably less overhead:

• Change the execution plan of query in postgresql manually?
• Combine two SELECT queries in PostgreSQL
• Reuse computed select value
• Multiple CTE in single query
• Update with results of another sql