This section describes the LIBNAME statement that SAS/ACCESS Interface to PostgreSQL supports. For general information about this feature, see LIBNAME Statement for Relational Databases.
Here is the LIBNAME statement syntax for accessing PostgreSQL.
For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.
specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.
postgresspecifies the SAS/ACCESS engine name for the PostgreSQL interface.
provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. Here is how these options are specified.
specifies the server name or IP address of the PostgreSQL server to which you want to connect. This server accesses the database that contains the tables and views that you want to access. If the server name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
specifies the name of the database on the PostgreSQL server that contains the tables and views that you want to access. If the database name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
Alias: DB=
specifies the port number that is used to connect to the specified PostgreSQL server.
Default: 5432
specifies the PostgreSQL user name (also called the user ID) that you use to connect to your database. If the user name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
Alias: UID=
specifies the password that is associated with your PostgreSQL user name. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.
Alias: PASS=, PW=, PWD=
specifies the configured PostgreSQL ODBC data source to which you want to connect. Use this option if you have existing PostgreSQL ODBC data sources that are configured on your client. This method requires additional setup—either through the Administrator control panel on Windows platforms or through the odbc.ini file or a similarly named configuration file on UNIX platforms. So it is recommended that you use this connection method only if you have existing, functioning data sources that have been specified.
specifies connection information for your database for PCs only. Separate multiple options with a semicolon. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, you are prompted with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the database. See your PostgreSQL documentation for more details.
This option is not available on UNIX platforms.
specifies connection options for your database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. PROMPT= does not immediately try to connect to the DBMS. Instead, it displays a dialog box in SAS Display Manager that contains the values that you entered in the PROMPT= connection string. You can edit values or enter additional values in any field before you connect to the database.
Restriction: This option is not available on UNIX platforms.
specifies connection options for your database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned. No dialog box is displayed to help you with the connection string.
specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to PostgreSQL with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure. For details, see LIBNAME Options for Relational Databases.
|
Option |
Default Value |
Valid in CONNECT |
|---|---|---|
|
none |
|
|
|
none |
● |
|
|
NO |
● |
|
|
10000 |
|
|
|
current SAS session encoding |
|
|
|
SHAREDREAD |
● |
|
|
none |
● |
|
|
none |
● |
|
|
DYNAMIC |
● |
|
|
matches the maximum number of bytes per single character of the SAS session encoding |
● |
|
|
1000 (when inserting rows), 0 (when updating rows) |
|
|
|
none |
● |
|
|
none |
● |
|
|
none |
|
|
|
DBMS |
● |
|
|
NO |
|
|
|
none |
|
|
|
none |
|
|
|
1024 |
● |
|
|
NO |
|
|
|
YES |
|
|
|
NO |
● |
|
|
NO |
● |
|
|
NO |
● |
|
|
none |
|
|
|
YES |
|
|
|
NO |
|
|
|
YES |
||
|
1 |
|
|
|
0 |
● |
|
|
0 |
● |
|
|
NONE |
|
|
|
none |
|
|
|
NO |
|
|
|
NO |
|
|
|
none |
|
|
|
0 |
● |
|
|
none |
● |
|
|
RC (see Locking in the PostgreSQL Interface) |
● |
|
|
ROW |
● |
|
|
0 |
● |
|
|
NO |
● |
|
|
none |
|
|
|
YES |
|
|
|
none |
|
|
|
none |
|
|
|
none |
|
|
|
prefer |
|
|
|
NO |
● |
|
|
none |
|
|
|
NO |
● |
|
|
none |
● |
|
|
● |
||
|
ROW |
● |
|
|
NO |
|
In this example, SERVER=, DATABASE=, USER=, and PASSWORD= are connection options. No DSN style is specified. This is the default method, which is recommended.
libname A1 postgres server=mysrv1 port=5432
user=myusr1 password='mypwd1' database=mydb1;This example requires that you specify a DSN style.
libname B1 postgres dsn=ptgtest user=myusr1 password=mypwd1;