1

I joined a new company and they are using DBVisualizer. For the last 5 years, I used SQL Server and I am now appreciating SQL Server for its intelligent execution of queries.

One of the problems I am facing is executing multiple temp tables at a time. For example, trying to execute the following throws me an error saying #abc is not available. This was not a problem in SQL Server as it used to execute it smartly.

drop table if exists #abc;
create table #abc as
select a.ID, a.EMP
from sandbox.table1 as a

drop table if exists #cde;
create table #cde as
select a2.ID, sum(a1.sum) as Rev
from sandbox.table2 as a1
join #abc as a2 on a1.ID = a2.ID
group by a2.ID
Dale K
  • 25,246
  • 15
  • 42
  • 71
Pavan Koundinya Kaipa
  • 21
  • 2
  • 1
    If you're using SQL Server why tag [tag:postgresql] too? – Thom A May 20 '21 at 07:16
  • 2
    `#abc` is an invalid table name in standard SQL and Postgres - so which DBMS are you **really** using? –  May 20 '21 at 07:35
  • Perhaps the real problem is that you apply a common pattern (select into temp, do something trivial with temp, select from temp) without any particular reason. And now that you have changed database engines, your pattern fails. Unlearn the bad habit. – SMor May 20 '21 at 11:56
  • This is just a sample code i shared. I will be working with 10-15 temp tables to summarize metrics by each customer. #abc is not an invalid table name. I have used it in SQL server and Postgresql and it is working fine. – Pavan Koundinya Kaipa May 20 '21 at 16:51
  • Your tables #abc and #cde are acceptable in Postgres if you double quote (") but otherwise they are invalid. BTW I hate SQL Server for its dumb dump execution of queries. Guess that just depends on what you are familiar with. – Belayer May 21 '21 at 05:12

1 Answers1

1

if you are looking to use certain queries as a table on runtime try WITH it will help you create a temporary view at runtime for a perticular query. then you can use this query output as a table and use it.

Example -

with userTBL as ( 
  select * 
  from user  
  where activated=true
),
usertiming as (
  select date,userID
  from timingtabls
  where date=currentdate
) 
select * from userTBL left join timing t on t.userID=id;
conol
  • 415
  • 4
  • 11