topic Re: Create a temp table in Postgres DB in SAS Programming

Create a temp table in Postgres DB

PeteSAS — Thu, 13 Jan 2022 16:21:26 GMT

I'm struggling with the syntax of creating a temp table in Postgres, as I do not have rights to create a permanent table. Here's what I tried:

libname mypg odbc DATAsrc=<data-source>
authdomain="<authdomain>"
schema=<schema>;

Libname works fine, and I'm able to query tables.

Temp table creation is where I get stuck:

proc sql;
create table mypg.'#pathway_set_tmp'n (pathwayid int);

Error received:

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: syntax error at or near "#"; Error while executing
the query.

Thanks in advance!

Re: Create a temp table in Postgres DB

r_behata — Thu, 13 Jan 2022 17:30:15 GMT

Have you tried the SQL Pass-through facility ?

proc sql noerrorstop;
 connect to postgres as x1(server=mysrv1 port=5432 
    user=mysur1 password='mypwd1' database=mydb1);

 select * from connection to x1 (<Your P-SQL Query Here>);

 disconnect from x1;
 quit;

Re: Create a temp table in Postgres DB

HB — Thu, 13 Jan 2022 17:30:25 GMT

Perhaps create the table in SAS and then export when you are done.

Re: Create a temp table in Postgres DB

PeteSAS — Thu, 13 Jan 2022 17:35:16 GMT

Thanks for the reply, I played around with that a little along the way - is there a way to specify the schema, and use windows authentication so I don't have to hard-code my user/pwd?

Re: Create a temp table in Postgres DB

PeteSAS — Thu, 13 Jan 2022 17:43:17 GMT

Thanks, could you be a little more specific in regard to exporting the table to Postgres as a temp table? Example would be much appreciated.

Re: Create a temp table in Postgres DB

SASKiwi — Thu, 13 Jan 2022 21:37:53 GMT

This link shows you how to create Postgres temporary tables: https://www.postgresqltutorial.com/postgresql-temporary-table/

If you use SQL Passthru and the Postgres examples in the link it should work. Something like this?

libname mypg odbc DATAsrc=<data-source>
authdomain="<authdomain>"
schema=<schema>; 

proc sql noprint;
  connect using mypg;
execute (CREATE TEMPORARY TABLE temp_table_name(MyTempTable);) by mypg;
quit;