Details

Use DataConnectionObject to represent data located in a PostgreSQL database in Wolfram Language.
Import can be used to retrieve data from a DataConnectionObject .
RelationalDatabase can be used to represent the schema of a "PostgreSQL" database.

Properties

DataConnectionObject [assoc] creates a reference to a data source. The source does not need to exist yet.

General keys typically found in assoc are:

"ConnectionName" "PostgreSQL"

"Location" SQL query given as a string

"Redownload" whether to rerun the query and overwrite cached results

Required keys in assoc are:

"DatabaseName" string giving the database to access

"Host" the host servicing the database

Typical keys in assoc are:

"Port" the server port to access the database

Possible forms of "Location" when connecting to a PostgreSQL database include:

"query" SQL query given as a string

Supported Types

BOOLEAN True or False

BYTEA ByteArray

DATE DateObject with day granularity

TIMESTAMP DateObject with instant granularity and time zone support

NUMERIC Real with fixed precision

BIGINT, INTEGER, SMALLINT Integer

DOUBLE_PRECISION, FLOAT, REAL Real

CHAR, TEXT, VARCHAR String

TIME TimeObject

Account Setting & Authentication

Possible values for Authentication are:

Automatic attempt to use saved credentials or give a dialog

"Dialog" give authentication parameters via a dialog

SystemCredential ["key"] retrieve authentication information from secure storage

SystemCredentialData […] stored authentication data

assoc provide explicit credentials

Use the form Authentication :>SystemCredential ["key"] to delay evaluating securely stored credentials.

The following keys are accepted by Authentication assoc:

"Username" username

"Password" password

Examples

Basic Examples (2)

Create a DataConnectionObject to data from a PostgreSQL query:

Wolfram Language code:

DataConnectionObject[<|"ConnectionName" -> "PostgreSQL", "DatabaseName" -> "dbname", "Host" -> "my.host.com", "Location" -> "SELECT * FROM users"|>, Authentication -> <|"Username" -> "user", "Password" -> "pass"|>]

Retrieve the data using Import :

Wolfram Language code:

Import@DataConnectionObject[<|"ConnectionName" -> "PostgreSQL", "DatabaseName" -> "dbname", "Host" -> "my.host.com", "Location" -> "SELECT * FROM table"|>, Authentication -> <|"Username" -> "user", "Password" -> "pass"|>]