Help to connect to postgresql database
I created a table in postgresql using the built-in superuser postgres
Install postgresql
https://www.postgresql.org/download/windows/
/-- add a table in template1 for testing ----/
cmd.exe
C:\WINDOWS\system32>psql -U postgres -d template1
Password for user postgres: xxxxxxxxxxx
template1=# CREATE TABLE example_table (
template1(# id SERIAL PRIMARY KEY,
template1(# name VARCHAR(50) NOT NULL,
template1(# created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
CREATE TABLE
template1-# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | example_table | table | postgres
(1 row)
I then tried to use this personal slc to read the table
%utlfkil(%sysfunc(pathname(WPSWBHTM)));
&init;
data zipcode;
set wpshelp.zipcode(obs=3);
run;quit;
libname pgdb postgres
server="localhost"
port=5432
database="template1"
user="postgres"
password="xxxxxxxx";
proc contents data=pgdb.all;
run;quit;
proc print data=pgdb.example_table;
run;quit;
LISTING FOR LOCAL SERVER
Altair SLC
The CONTENTS Procedure
Directory
Libref PGDB
Engine POSTGRESQL
LOG
131 ODS ALL CLOSE;
132 FILENAME WPSWBHTM TEMP;
NOTE: Writing HTML(WBHTML) BODY file d:\wpswrk_TD4464#LN00014
133 ODS HTML(ID=WBHTML) BODY=WPSWBHTM GPATH="d:\wpswrk_TD4464";
134 %utlfkil(%sysfunc(pathname(WPSWBHTM)));
135 &init;
136
137 data zipcode;
138 set wpshelp.zipcode(obs=3);
139 run;
NOTE: 3 observations were read from "WPSHELP.zipcode"
NOTE: Data set "WORK.zipcode" has 3 observation(s) and 15 variable(s)
NOTE: The data step took :
real time : 0.003
cpu time : 0.000
139 ! quit;
140
141 libname pgdb postgres
142 server="localhost"
143 port=5432
144 database="template1"
145 user="postgres"
NOTE: Library pgdb assigned as follows:
Engine: POSTGRESQL
Physical Name: localhost
146 password=XXXXXXXXXX;
147
148 proc contents data=pgdb.all;
149 run;quit;
NOTE: Due to PRESERVE_TAB_NAMES=NO, 1 table(s) and view(s) were not returned. Set PRESERVE_TAB_NAMES=YES to see these items.
NOTE: No matching members in directory
NOTE: Procedure contents step took :
real time : 0.087
cpu time : 0.031
150
151 proc print data=pgdb.example_table;
^
ERROR: Invalid table name example_table
NOTE: Procedure PRINT was not executed because of errors detected
152 run;
NOTE: Procedure print step took :
real time : 0.001
cpu time : 0.000
152 ! quit;
153 quit; run;
154 ODS ALL CLOSE;
155 FILENAME WPSWBHTM CLEAR;
