Creating temporary tables in SQL

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?

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:

• https://stackoverflow.com/questions/9067335/how-does-the-search-path-influence-identifier-resolution-and-the-current-schema/9067777#9067777

---

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:

• https://stackoverflow.com/questions/15559090/combine-two-tables-into-a-new-one-so-that-select-rows-from-the-other-one-are-ign/15560009#15560009
• https://stackoverflow.com/questions/24038195/error-input-parameters-after-one-with-a-default-value-must-also-have-defaults/24072348#24072348

---

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:

• https://stackoverflow.com/questions/14982120/change-the-execution-plan-of-query-in-postgresql-manually/14983685#14983685

• https://stackoverflow.com/questions/15849423/combine-two-select-queries-in-postgresql/15850396#15850396

• https://stackoverflow.com/questions/21863163/reuse-computed-select-value/21863334#21863334

• https://stackoverflow.com/questions/35248217/multiple-cte-in-single-query/35249370#35249370

• https://stackoverflow.com/questions/25350431/update-with-results-of-another-sql/25354243#25354243

http://www.postgresql.org/docs/9.2/static/sql-createtable.html

CREATE TEMP TABLE temp1 LIKE ...