Search code examples
sqlpostgresqlddl

Create a temporary table from a selection, or insert if table already exists

How to create a temporary table if it does not already exist, and add selected rows to it?

Solution

  • CREATE TABLE AS

    is the simplest and fastest way:

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

    Do not use SELECT INTO for this purpose. See:

    Not sure whether table already exists

    CREATE TABLE IF NOT EXISTS ... was added with Postgres 9.1. For older versions, see:

    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 to return the dynamically created table name.