1

I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE).

e.g. something along the lines of

WITH a AS (
SELECT 1 foo, 2 bar 
), b AS (
SELECT 4 bar, 5 baz
)
CREATE TEMPORARY TABLE foo AS
SELECT * from a JOIN b ON (a.bar=b.bar)

If I comment out the CREATE TEMPORARY TABLE line, this works. How to create a temporary table using the query result of the SELECT, without rewriting the temporary views into a single query?

3

The WITH goes after the CREATE TABLE

CREATE TEMPORARY TABLE foo 
AS
WITH a AS (
SELECT 1 foo, 2 bar 
), b AS (
SELECT 4 bar, 5 baz
)
SELECT * from a JOIN b ON (a.bar=b.bar)

Note the above will result in "ERROR: column "bar" specified more than once" - but I assume you are using better (distinct) column names in the real query.

  • 2
    @peschü: The simple way to resolve the duplicate column name in the example: ... SELECT * from a JOIN b USING (bar); – Erwin Brandstetter Nov 12 at 16:09
  • Thank you! I could not find this in the Postgres documentation but I thought it should be possible. It works! (if I replace the * by something more reasonable) :-) – peschü Nov 14 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.