Creating temporary tables in SQL

by

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:

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:

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:

More Related Contents:

  1. PostgreSQL “Column does not exist” but it actually does
  2. Is SELECT or INSERT in a function prone to race conditions?
  3. Any downsides of using data type “text” for storing strings?
  4. Index for finding an element in a JSON array
  5. Calculating Cumulative Sum in PostgreSQL
  6. Change PostgreSQL columns used in views
  7. How to set auto increment primary key in PostgreSQL?
  8. List columns with indexes in PostgreSQL
  9. PostgreSQL IF statement
  10. Split given string and prepare case statement
  11. Are there any way to execute a query inside the string value (like eval) in PostgreSQL?
  12. Double colon `::` notation in SQL
  13. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  14. I keep getting the error “relation [TABLE] does not exist”
  15. Export specific rows from a PostgreSQL table as INSERT SQL script
  16. PostgreSQL Where count condition
  17. How to write a constraint concerning a max number of rows in postgresql?
  18. SQL Transpose Rows as Columns
  19. PostgreSQL create table if not exists
  20. How to return two columns with function
  21. Count cumulative total in Postgresql
  22. How to retrieve field names from temporary table (SQL Server 2008)
  23. Prepared Statement on Postgresql in Rails
  24. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  25. PostgreSQL 9.3: Dynamic pivot table
  26. PostgreSQL how to create a copy of a database or schema?
  27. PostgreSQL: fill NULL values in timeserie query with previous value
  28. Postgres: convert single row to multiple rows (unpivot)
  29. Get most common value for each value of another column in SQL
  30. PostgreSQL: top n entries per item in same table

Leave a Comment