Create a temporary table from a selection or insert if table already exist

Question

What is the best way to create a temporary table, if it does not already exist, and add the selected rows to it?

Answer 1

CREATE TABLE AS

is the simplest and fastest way:

CREATE TEMP TABLE tbl AS
SELECT * FROM tbl WHERE ... ;

Do not use SELECT INTO. See:

• Combine two tables into a new one so that select rows from the other one are ignored

Not sure whether table already exists

CREATE TABLE IF NOT EXISTS ... was introduced in version Postgres 9.1.
For older versions, use the function provided in this related answer:

• PostgreSQL create table if not exists

Then:

INSERT INTO tbl (col1, col2, ...)
SELECT col1, col2, ...

Chances are, something is going wrong in your code if the temp table already exists. Make sure you don't duplicate data in the table or something. Or consider the following paragraph ...

Unique names

Temporary tables are only visible within your current session (not to be confused with transaction!). So the table name cannot conflict with other sessions. If you need unique names within your session, you could use dynamic SQL and utilize a SEQUENCE:

Create once:

CREATE SEQUENCE tablename_helper_seq;

You could use a DO statement (or a plpgsql function):

DO
$do$
BEGIN
   EXECUTE
   'CREATE TEMP TABLE tbl' || nextval('tablename_helper_seq'::regclass) || ' AS
    SELECT * FROM tbl WHERE ... ';

   RAISE NOTICE 'Temporary table created: "tbl%"' || ', lastval();
END
$do$;

lastval() and currval(regclass) are instrumental in this return the dynamically created table name.