connection

PostgreSQL native interface database connection

Since R2020b

Description

Create a connection to a PostgreSQL database using the PostgreSQL native interface. Configure a PostgreSQL native interface data source using the databaseConnectionOptions function.

Creation

Create a connection object by using the postgresql function.

Properties

DataSource — Data source name

string scalar

This property is read-only.

Data source name, specified as a string scalar.

Example: "PostgreSQLDataSource"

Data Types: string

Database — Database name

"" (default) | string scalar

This property is read-only.

Database name, specified as a string scalar.

If you use the 'DatabaseName' name-value pair argument of the postgresql function, the postgresql function sets the Database property of the connection object to the specified value.

Example: "toystore_doc"

Data Types: string

Server — Server name

localhost (default) | string scalar

This property is read-only.

Server name, specified as a string scalar.

If you use the 'Server' name-value pair argument of the postgresql function, the postgresql function sets the Server property of the connection object to the specified value.

Example: "dbtb00"

Data Types: string

PortNumber — Port number

5432 (default) | numeric scalar

This property is read-only.

Port number, specified as a numeric scalar.

If you use the 'PortNumber' name-value pair argument of the postgresql function, the postgresql function sets the PortNumber property of the connection object to the specified value.

Example: 5432

Data Types: double

UserName — User name

"" (default) | string scalar

This property is read-only.

User name, specified as a string scalar.

Data Types: string

DefaultCatalog — Default catalog

"" (default) | string scalar

This property is read-only.

Default catalog, specified as a string scalar.

Example: "toy_store"

Data Types: string

Catalogs — Catalogs in database

"" (default) | string array

This property is read-only.

Catalogs in the database, specified as a string array.

Example: ["information", "postgresql"]

Data Types: string

Schemas — Schemas in database

"" (default) | string array

This property is read-only.

Schemas in the database, specified as a string array.

Example: ["information_schema", "toys"]

Data Types: string

AutoCommit — Flag to autocommit transactions

string scalar

Flag to autocommit transactions, specified as one of these values:

• "on" — Database transactions are automatically committed to the database.

• "off" — Database transactions must be committed to the database manually.

You can set this property by using dot notation.

LoginTimeout — Login timeout

0 (default) | positive numeric scalar

This property is read-only.

Login timeout, specified as a positive numeric scalar. The login timeout specifies the number of seconds that the driver waits while trying to connect to a database before throwing an error.

When no login timeout for the connection attempt is specified, the value is 0.

When a login timeout is not supported by the database, the value is -1.

Data Types: double

MaxDatabaseConnections — Maximum number of database connections

-1 (default) | positive numeric scalar

This property is read-only.

Maximum number of database connections, specified as a positive numeric scalar.

When the database has no upper limit to the maximum number of database connections, the value is 0.

When a maximum number of database connections is not supported by the database, the value is -1.

Data Types: double

DatabaseProductName — Database product name

"" (default) | string scalar

This property is read-only.

Database product name, specified as a string scalar.

When the database connection is invalid, the value is an empty string scalar "".

Example: "PostgreSQL"

Data Types: string

DatabaseProductVersion — Database product version

"" (default) | string scalar

This property is read-only.

Database product version, specified as a string scalar.

When the database connection is invalid, the value is an empty string scalar "".

Example: "9.4.5"

Data Types: string

DriverName — Driver name

"" (default) | string scalar

This property is read-only.

Driver name of the PostgreSQL driver, specified as a string scalar.

When the database connection is invalid, the value is an empty string scalar "".

Example: "libpq"

Data Types: string

DriverVersion — Driver version

"" (default) | string scalar

This property is read-only.

Driver version of the PostgreSQL driver, specified as a string scalar.

When the database connection is invalid, the value is an empty string scalar "".

Example: "10.12"

Data Types: string

Object Functions

Manage PostgreSQL Database Connection

close	Close PostgreSQL native interface database connection
isopen	Determine if PostgreSQL native interface database connection is open

Import Data from PostgreSQL Database

fetch	Import results of SQL statement in PostgreSQL database into MATLAB
sqlinnerjoin	Inner join between two PostgreSQL database tables
sqlouterjoin	Outer join between two PostgreSQL database tables
sqlfind	Find information about all table types in PostgreSQL database
sqlread	Import data into MATLAB from PostgreSQL database table
executeSQLScript	Execute SQL script on PostgreSQL database

Export Data to PostgreSQL Database

sqlwrite

Insert MATLAB data into PostgreSQL database table

PostgreSQL Database Operations

execute	Execute SQL statement using PostgreSQL native interface database connection
commit	Make changes to PostgreSQL database permanent
rollback	Undo changes to PostgreSQL database
sqlupdate	Update rows in PostgreSQL database table

Examples

Connect to PostgreSQL Database Using PostgreSQL Native Interface

Create a PostgreSQL native interface connection to a PostgreSQL database. Then, import data from the database into MATLAB® and perform simple data analysis. Close the database connection.

This example assumes that you are connecting to a PostgreSQL database version 9.405 using the libpq driver version 10.12.

Connect to the database using the data source name, user name, and password.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";

conn = postgresql(datasource,username,password)

conn = 
  connection with properties:

                  DataSource: "PostgreSQLDataSource"
                    UserName: "dbdev"

  Database Properties:

                  AutoCommit: "on"
                LoginTimeout: 0
      MaxDatabaseConnections: 100

  Catalog and Schema Information:

              DefaultCatalog: "toystore_doc"
                    Catalogs: "toystore_doc"
                     Schemas: ["pg_toast", "pg_temp_1", "pg_toast_temp_1" ... and 3 more]

  Database and Driver Information:

         DatabaseProductName: "PostgreSQL"
      DatabaseProductVersion: "9.405"
                  DriverName: "libpq"
               DriverVersion: "10.12"

The property sections of the connection object are:

• Database Properties — Information about the database configuration

• Catalog and Schema Information — Names of catalogs and schemas in the database

• Database and Driver Information — Names and versions of the database and driver

Import all data from the table inventoryTable into MATLAB using the sqlread function. Display the first three rows of data.

tablename = "inventoryTable";
data = sqlread(conn,tablename);
head(data,3)

ans=3×4 table
    productnumber    quantity    price        inventorydate    
    _____________    ________    _____    _____________________

          1            1700      14.5     "2014-09-23 09:38:34"
          2            1200         9     "2014-07-08 22:50:45"
          3             356        17     "2014-05-14 07:14:28"

Determine the highest product quantity from the table.

max(data.quantity)

ans = 9000

Close the database connection conn.

close(conn)

Version History

Introduced in R2020b