Create a temporary table from a selection or inser

问题:

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

回答1:

CREATE TABLE AS

is the simplest and fastest way:

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

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 (don't confuse 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 case to get the name actually used.