36

I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes.

I looked at the documentation for CREATE TABLE and it says VALUES can be used as a query but gives no example; the documentation for the VALUES clause linked therein does not have an example either?

So, I wrote a simple test as follows:

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup (
  key integer,
  val numeric
) AS
VALUES (0,-99999), (1,100);

But PostgreSQL (9.3) is complaining about

syntax error at or near "AS"

My questions are:

  1. How can I fix the statement above?

  2. How can I adapt it to be used in a WITH block?

Thanks in advance.

45

EDIT: I am leaving the original accepted answer as it is, but please note that the edit below, as suggested by a_horse_with_no_name, is the preferred method for creating a temporary table using VALUES.

If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like:

WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100)) 
SELECT * FROM vals;

To actually create a temporary table in a similar fashion, use:

WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100)) 
SELECT * INTO temporary table temp_table FROM vals;

EDIT: As pointed out by a_horse_with_no_name, in the docs it states that CREATE TABLE AS... is functionally similar to SELECT INTO ..., but that the former is a superset of the latter and that SELECT INTO is used in plpgslq for assigning a value to a temporary variable -- so it would fail in that case. Therefore, while the above examples are valid for plain SQL, the CREATE TABLE form should be preferred.

CREATE TEMP TABLE temp_table AS                                     
WITH t (k, v) AS (
 VALUES
 (0::int,-99999::numeric), 
 (1::int,100::numeric)
)
SELECT * FROM t;

Note, also from the comments by a_horse_with_no_name, and in the OP's original question, this includes a cast to the correct datatypes inside the values list and uses a CTE (WITH) statement.

Also, as pointed out in Evan Carrol's answer, a CTE query is an optimization fence, ie, the CTE is always materialized. There are many good reasons for using CTEs, but there can be quite a significant performance hit, if not used carefully. There are, however, many instances where the optimization fence can actually enhance performance, so this is something to be aware of, not to blindly avoid.

  • 12
    from the docs: "CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax" – a_horse_with_no_name Dec 21 '14 at 23:14
  • The optimization fence is not necessarily a bad thing. I have seen many statements that I could tune to run massively faster because of that. – a_horse_with_no_name Jul 19 at 11:25
  • Sure, I have clarified that also. I use CTEs all the time in a spatial context. If you have a where clause with something like WHERE ST_Intersects(geom, (SELECT geom FROM sometable) or WHERE ST_Intersects(geom, ST_Buffer(anothergeom, 10) then often the query planner does not use the spatial index because the geom column in no longer sargable. If you create your area of interest in an initial CTE, this problem goes away. It is also super convenient, if you want to use the same aoi in multiple further expressions in tthe same query, which is not uncommon in a GIS context. – John Powell Jul 19 at 11:57
24

create table as needs a select statement:

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup 
as 
select *
from (
   VALUES 
    (0::int,-99999::numeric), 
    (1::int, 100::numeric)
) as t (key, value);

You can also re-write this to use a CTE:

create temp table lookup 
as 
with t (key, value) as (
  values 
    (0::int,-99999::numeric), 
    (1::int,100::numeric)
)
select * from t;
  • 1
    Thanks for you comment. You approach is obviously better for the reasons stated in the docs. I have edited my answer, albeit nearly 5 years late. – John Powell Jul 19 at 10:17
10

The issue is the datatypes. If you remove them, the statement will work:

CREATE TEMP TABLE lookup
  (key, val) AS
VALUES 
  (0, -99999), 
  (1, 100) ;

You can define the types by casting the values of the first row:

CREATE TEMP TABLE lookup 
  (key, val) AS
VALUES 
  (0::bigint, -99999::int), 
  (1, 100) ;
3

You really don't need to create a table nor use a CTE, if all you need is to use a few values in your queries. You can inline them:

SELECT  *
FROM    (VALUES(0::INT, -99999::NUMERIC), (1, 100)) AS lookup(key, val)

Then you can get a Cartesian product with a CROSS JOIN (where the other relationship can be, of course, a regular table, view, etc.). e.g.:

SELECT  *
FROM    (VALUES(0::int, -99999::numeric), (1, 100)) AS lookup(key, val)
       ,(VALUES('Red'), ('White'), ('Blue')) AS colors(color);

which yields:

key |val    |color |
----|-------|------|
0   |-99999 |Red   |
1   |100    |Red   |
0   |-99999 |White |
1   |100    |White |
0   |-99999 |Blue  |
1   |100    |Blue  |

Or JOIN the values with another relationship (which again can be a regular table, view, etc.), e.g.:

SELECT  *
FROM    (VALUES(0::int, -99999::numeric), (1, 100)) AS lookup(key, val)
  JOIN  (VALUES('Red', 1), ('White', 0), ('Blue', 1)) AS colors(color, lookup_key)
          ON colors.lookup_key = lookup.key;

which yields:

key |val    |color |lookup_key |
----|-------|------|-----------|
1   |100    |Red   |1          |
0   |-99999 |White |0          |
1   |100    |Blue  |1          |
  • OK but the question was "how to create a temporary table with...?" – ypercubeᵀᴹ Oct 20 '17 at 17:51
  • Yes, but why would you need a temporary table with a few fixed lookup values if not for joining it on another relationship? This solution solves the problem itself, regardless of how the question is worded. – isapir Oct 20 '17 at 18:37
  • 1
    Maybe OP just happened to boil down the example to something that would be easy to post as a question, but the real data has thousands of values? – stannius Jan 25 '18 at 20:56
  • The OP specifically stated using values so my answer still applies as that is exactly what it does – isapir Oct 10 at 15:26
2

First always use the standardized CREATE TABLE AS, SELECT INTO as suggested in other answers has been a deprecated syntax for over a decade. You can use CREATE TABLE AS with a CTE

While many answers here are suggesting using a CTE, that's not preferable. In fact, it's likely somewhat slower. Just wrap it up as a table.

DROP TABLE IF EXISTS lookup;

CREATE TEMP TABLE lookup(key, value) AS
  VALUES
  (0::int,-99999::numeric),
  (1,100);

If you must write a select statement you can do that too (and you don't need a CTE).

CREATE TEMP TABLE lookup(key, value) AS
  SELECT key::int, value::numeric
  FROM ( VALUES
    (0::int,-99999::numeric),
    (1,100)
  ) AS t(key, value);

A CTE in PostgreSQL forces materialization. It's an optimization fence. For that reason, it's generally not a good idea to use them anywhere except when you understand the costs and you know it to provide a performance improvement. You can see the slow down here, for instance,

\timing
CREATE TABLE foo AS
  SELECT * FROM generate_series(1,1e7);
Time: 5699.070 ms

CREATE TABLE foo AS
  WITH t AS ( SELECT * FROM generate_series(1,1e7) ) 
  SELECT * FROM t;
Time: 6484.516 ms
  • I have updated the answer to reflect the standard, and point out how the accepted answer is not always equivalent to CREATE TABLE AS and added a comment on the optimization fence, which is a very good point to bring up. CTEs bring so many advantages, but it is true, if used blindly, can lead to horrible performance. – John Powell Jul 19 at 11:20
-2
WITH u AS (
    SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS account (id,name)
)
SELECT id, name, length(name) from u;

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.