SELECT INTO - Creating Temporary Table - SQL Server to PostgreSQL Migration

In SQL Server you can use a SELECT INTO statement to create a temporary table based on the query results.

In PostgreSQL you can also use SELECT INTO TEMPORARY statement, but only as a standalone SQL statement (not PL/pgSQL). If you need to create a temporary table in PL/pgSQL function or procedure you have to use CREATE TEMPORARY TABLE AS SELECT statement.

SQL Server:

  -- Create a new temporary table
  SELECT 'A' AS c1 INTO #tmp;
 
  -- Query the temporary table
  SELECT * FROM #tmp;
  # A

PostgreSQL:

  -- Create a new temporary table
  SELECT 'A' AS c1 INTO TEMPORARY tmp;
 
  -- Query the temporary table
  SELECT * FROM tmp;
  # A

SQL Server allows you to use the same SELECT INTO statement to create a temporary table in Transact-SQL procedure:

SQL Server:

  CREATE PROCEDURE sp_create_temp
  AS
    SELECT 'A' AS c1 INTO #tmp2;
  GO

But you cannot use SELECT INTO TEMPORARY in PostgreSQL PL/pgSQL code, it will rise ERROR: “temporary” is not a known variable.

PostgreSQL:

  CREATE OR REPLACE FUNCTION sp_create_temp()
  RETURNS VOID AS $$
  BEGIN
    CREATE TEMPORARY TABLE tmp3 AS 
       SELECT 'A' AS c1;
  END;
  $$ LANGUAGE plpgsql;

For more information, see SQL Server to PostgreSQL Migration.