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:
If you want dynamic, you would be looking for CREATE VIEW
- a completely different story.
The SQL standard also defines, and Postgres also supports: . But its use is discouraged:SELECT INTO
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: