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:
How can I fix the statement above?
How can I adapt it to be used in a
Thanks in advance.
Here is Solutions:
We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.
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, in Postgres prior to version 12 a CTE query is always 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.
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;
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) ;
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);
key |val |color | ----|-------|------| 0 |-99999 |Red | 1 |100 |Red | 0 |-99999 |White | 1 |100 |White | 0 |-99999 |Blue | 1 |100 |Blue |
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;
key |val |color |lookup_key | ----|-------|------|-----------| 1 |100 |Red |1 | 0 |-99999 |White |0 | 1 |100 |Blue |1 |
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
WITH u AS ( SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS account (id,name) ) SELECT id, name, length(name) from u;
Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂