current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
    
Thanks everyone. Both solutions worked. –  tinlyx Dec 21 '14 at 22:32

2 Answers 2

up vote 4 down vote accepted

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  temp (k,v) AS (VALUES (0,-9999), (1, 100)) 
SELECT * FROM temp;

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 FROM vals;
share|improve this answer
    
select .. into to create a new table is an old non-standard way of doing this and is only supported for backward compatibility. Using the standard compliant create table as should be preferred. –  a_horse_with_no_name Dec 21 '14 at 22:08
    
@a_horse_with_no_name. OK, thanks for that. It's in the docs, but will avoid it going forward. –  John Barça Dec 21 '14 at 22:24
    
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
up vote 3 down vote

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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