PostgreSQL create temp table from query

Question

When I using query to select values to temp table like this:

drop table if exists tTemp;

select  tt.id, tt.name
into    tTemp
from    test.TestTable tt

It's work great. But when I using this construction in function I have this error:

[42601] ERROR: "tTemp" is not a known variableq

Why this construction don't work in function?

into has a different meaning in PL/pgSQL code. See the documentation — Stefanov.sm
– Stefanov.sm, Commented Nov 22, 2022 at 8:00

user330315 · Accepted Answer · 2022-11-22 08:01:30Z

1

Use the standard compliant CRATE TABLE AS SELECT instead of the discouraged select ... into to avoid the ambiguity between storing the result of a query into a PL/pgSQL variable and creating a new table:

drop table if exists tTemp;

create table ttemp 
as
select  tt.id, tt.name
from    test.TestTable tt

This is one of the reasons the manual recommends to use CREATE TABLE AS instead of select into

answered Nov 22, 2022 at 8:01

user330315

Sign up to request clarification or add additional context in comments.

Comments

masoud rafiee · Accepted Answer · 2022-11-22 07:39:52Z

0

its may be work :

INSERT INTO tTemp
select  tt.id, tt.name
from    test.TestTable tt

answered Nov 22, 2022 at 7:39

masoud rafiee

4471 gold badge3 silver badges9 bronze badges

Collectives™ on Stack Overflow

PostgreSQL create temp table from query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related