Jak utworzyć tabelę tymczasową za pomocą VALUES w PostgreSQL

Uczę się PostgreSQL i próbuję wymyślić, jak utworzyć tymczasową tabelę lub WITHdeklarację, której można użyć zamiast zwykłej tabeli do celów debugowania.

Przejrzałem dokumentację CREATE TABLE i mówi, że VALUESmożna ją wykorzystać jako zapytanie, ale nie podaje żadnego przykładu; dokumentacja VALUESklauzuli w niej zawartej również nie ma przykładu?

Napisałem więc prosty test w następujący sposób:

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

Ale PostgreSQL (9.3) narzeka

błąd składniowy przy „AS” lub w jego pobliżu

Moje pytania to:

1. Jak mogę naprawić powyższe stwierdzenie?

2. Jak mogę go dostosować do użycia w WITH block?

Z góry dziękuję.

EDYCJA: Pozostawiam oryginalną zaakceptowaną odpowiedź taką, jaka jest, ale pamiętaj, że poniższa edycja, sugerowana przez a_horse_w_no_name, jest preferowaną metodą tworzenia tabeli tymczasowej przy użyciu VALUES.

Jeśli chcesz tylko wybrać jedną z wartości, zamiast tworzyć tabelę i wstawiać do niej, możesz zrobić coś takiego:

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

Aby faktycznie utworzyć tymczasowy stół w podobny sposób, użyj:

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

EDYCJA: Jak wskazano przez a_horse_with_no_name, w dokumentach stwierdza, że CREATE TABLE AS...jest funkcjonalnie podobny SELECT INTO ..., ale że ten pierwszy jest nadzbiorem drugiego i który SELECT INTOjest używany w plpgslq do przypisywania wartości do zmiennej tymczasowej - więc nie powiodłaby się ta walizka. Dlatego, chociaż powyższe przykłady dotyczą zwykłego SQL, CREATE TABLEforma powinna być preferowana.

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

Uwaga: również z komentarzy autorstwa_nazwa_nazwy_nazwa oraz w pierwotnym pytaniu PO, obejmuje to rzutowanie na prawidłowe typy danych na liście wartości i użycie instrukcji CTE (Z).

Ponadto, jak wskazano w odpowiedzi Evana Carrola, zapytanie CTE jest ogrodzeniem optymalizacyjnym , tj. CTE jest zawsze zmaterializowane. Istnieje wiele dobrych powodów, aby używać CTE, ale może być dość znaczący spadek wydajności, jeśli nie zostanie zastosowany ostrożnie. Istnieje jednak wiele przypadków, w których płot optymalizacyjny może rzeczywiście poprawić wydajność, więc należy o tym pamiętać, a nie ślepo unikać.

create table as potrzebuje instrukcji select:

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);

Możesz także ponownie napisać to, aby użyć CTE:

create temp table lookup 
as 
with t (key, value) as (
  values 
    (0::int,-99999::numeric), 
    (1::int,100::numeric)
)
select * from t;

Problemem są typy danych. Jeśli je usuniesz, instrukcja będzie działać:

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

Możesz zdefiniować typy, rzutując wartości z pierwszego wiersza:

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

Naprawdę nie musisz tworzyć tabeli ani używać CTE, jeśli wszystko, czego potrzebujesz, to użyć kilku wartości w zapytaniach. Możesz je wstawić:

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

Następnie możesz uzyskać produkt kartezjański z CROSS JOIN(gdzie drugą relacją może być oczywiście zwykły stół, widok itp.). na przykład:

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

co daje:

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

Lub JOINwartości z inną relacją (która może być zwykłą tabelą, widokiem itp.), Np .:

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;

co daje:

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

Najpierw zawsze używaj standardowego CREATE TABLE AS, SELECT INTOjak sugerowano w innych odpowiedziach, jest to przestarzała składnia od ponad dekady. Możesz używaćCREATE TABLE AS z CTE

Chociaż wiele odpowiedzi tutaj sugeruje użycie CTE, nie jest to preferowane. W rzeczywistości jest to nieco wolniejsze. Po prostu zawiń w stół.

DROP TABLE IF EXISTS lookup;

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

Jeśli musisz napisać instrukcję select, możesz to zrobić (i nie potrzebujesz 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);

CTE w PostgreSQL wymusza materializację. To płot optymalizacyjny. Z tego powodu generalnie nie jest dobrym pomysłem stosowanie ich w dowolnym miejscu, z wyjątkiem sytuacji, gdy rozumiesz koszty i wiesz, że zapewnia to poprawę wydajności. Możesz zobaczyć tutaj spowolnienie, na przykład

\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;