DATA Step, Macro, Functions and more

LIBNAME connection to PostgreSQL - Table Listing

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

LIBNAME connection to PostgreSQL - Table Listing

Hi,

I'm trying to connect to a PostgreSQL database using the LIBNAME statement.

This is what I'm using:

libname core odbc datasrc = 'PostGreSQL30' schema = Public;

This connects successfully according to the log, but I have no idea what the tables are.

When I connect to a Microsoft SQL database I use schema = dbo and in Enterprise Guide, the library is expandable and I can see the tables and I know what to query/interogate.

The obvious answer is to ask the DBA as to what tables are available, but he's not here.  Is there a way that I can setup the library such that I can see the contents?  I would want this feature even if the DBA could give me a listing of all the tables available.

We are on Enterprise Guide 5.1 (5.100.0.12269) Hot fix 7 (32-bit), and SAS 9.3 (TS1M2), Rev. 930_12w41, on Windows Server 2008 64 bit R2 SP1.

Thanks.


Accepted Solutions
Solution
‎11-24-2013 04:29 PM
Frequent Contributor
Posts: 75

Re: LIBNAME connection to PostgreSQL - Table Listing

Hi

Here's my log:

15        libname core odbc datasrc = 'PostGreSQL30' schema = report_target;
NOTE: Libref CORE was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: PostGreSQL30
16       
17        proc contents data=core._all_;
18        run;

NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 9 table(s) have not been displayed/returned.
WARNING: No matching members in directory.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time          0.14 seconds
      cpu time            0.00 seconds

I get nothing in the results tab when I run this.

Having said that, this is the first time I've paid attention to the log.

I've successfully done it !

libname core odbc datasrc = 'PostGreSQL30' schema = report_target PRESERVE_TAB_NAMES=yes;

proc contents data=core._all_ ;
run;


And now I can see the datasets in the CORE library.  I wonder why I never noticed the hint in the log when I was originally trying to do this.

Thanks.

View solution in original post


All Replies
Valued Guide
Posts: 3,208

Re: LIBNAME connection to PostgreSQL - Table Listing

It are not the tables in a DBMS you are refering to but to containers, the schemas, that contain those tables.

Within SAS I do not knon an option to see the schema-s.
One excpeption was the ODBC interface missing the schema name as using SAS/Share.  As SAS/SHare is SAS is not useful info.

There are a lot of developers tools like SQL developer that will show the Schema-names when being autorized

---->-- ja karman --<-----
Respected Advisor
Posts: 3,886

Re: LIBNAME connection to PostgreSQL - Table Listing

I might be missing something but if you say that the libname as such executes successfully then the following code will show you all tables available via schema "Public" (which could be none).

proc contents data=core._all_;

run;

Solution
‎11-24-2013 04:29 PM
Frequent Contributor
Posts: 75

Re: LIBNAME connection to PostgreSQL - Table Listing

Hi

Here's my log:

15        libname core odbc datasrc = 'PostGreSQL30' schema = report_target;
NOTE: Libref CORE was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: PostGreSQL30
16       
17        proc contents data=core._all_;
18        run;

NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 9 table(s) have not been displayed/returned.
WARNING: No matching members in directory.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time          0.14 seconds
      cpu time            0.00 seconds

I get nothing in the results tab when I run this.

Having said that, this is the first time I've paid attention to the log.

I've successfully done it !

libname core odbc datasrc = 'PostGreSQL30' schema = report_target PRESERVE_TAB_NAMES=yes;

proc contents data=core._all_ ;
run;


And now I can see the datasets in the CORE library.  I wonder why I never noticed the hint in the log when I was originally trying to do this.

Thanks.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 2741 views
  • 0 likes
  • 3 in conversation