postgresql

R2026b

Create PostgreSQL native interface database connection

Syntax

conn = postgresql(DataSourceName,username,password)

conn = postgresql(username,password,Name=Value)

Description

conn = postgresql(DataSourceName,username,password) creates a PostgreSQL^® native interface database connection using the specified data source, username, and password. conn is a connection object.

conn = postgresql(username,password,Name=Value) creates a PostgreSQL native interface database connection using the specified username and password, with additional options specified by one or more name-value arguments. For example, Server="dbtb00" specifies the database server name as dbtb00.

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, username, 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
    1    1700    14.5000    "2014-09-23 09:38:34"
    2    1200     9.0000    "2014-07-08 22:50:45"
    3     356    17.0000    "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)

Connect to PostgreSQL Database Using PostgreSQL Native Interface and Additional Options

Create a PostgreSQL native interface connection to a PostgreSQL database using name-value arguments. 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 username and password shown. Specify the database server name dbtb00, database name toystore_doc, and port number 5432 by setting the corresponding name-value arguments.

username = "dbdev";
password = "matlab";

conn = postgresql(username,password,Server="dbtb00", ...
    DatabaseName="toystore_doc",PortNumber=5432)

conn = 
  connection with properties:

                    Database: "toystore_doc"
                    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
    1    1700    14.5000    "2014-09-23 09:38:34"
    2    1200     9.0000    "2014-07-08 22:50:45"
    3     356    17.0000    "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)

Input Arguments

DataSourceName — Data source name

character vector | string scalar

Data source name, specified as a character vector or string scalar. Database Explorer creates this name when you use the app to make a connection and store your credentials as part of the data source.

username — Username

character vector | string scalar

Username required to access the database, specified as a character vector or string scalar. If no username is required, specify an empty value "". Provide this username if you do not store it as part of the data source when using the Database Explorer app to make a connection.

password — Password

character vector | string scalar

Password required to access the database, specified as a character vector or string scalar. If no password is required, specify an empty value "". Provide this password if you do not store it as part of the data source when using the Database Explorer app to make a connection.

Data Types: char | string

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Example: conn = postgresql(username,password,Server="dbtb00",PortNumber=5432,DatabaseName="toystore_doc") creates a PostgreSQL native interface database connection using the database server dbtb00, port number 5432, and database name toystore_doc.

Server — Database server name

"localhost" (default) | string scalar | character vector

Database server name or address, specified as a string scalar or character vector.

Example: "dbtb00"

Data Types: char | string

PortNumber — Port number

5432 (default) | numeric scalar

Port number, specified as a numeric scalar.

Example: 5432

Data Types: double

DatabaseName — Database name

"" (default) | string scalar | character vector

Database name, specified as a string scalar or character vector. If you do not specify a database name, the postgresql function connects to the default database on the database server.

Example: "toystore_doc"

Data Types: char | string

AutoCommit — Flag to autocommit transactions

"on" (default) | "off"

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.

Example: AutoCommit="off"

LoginTimeout — Login timeout

0 (default) | positive numeric scalar

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.

To specify no login timeout for the connection attempt, set the value to 0.

When login timeout is unsupported by the database, the value is -1.

Example: LoginTimeout=5

Extended Capabilities

Thread-Based Environment

Run code in the background using MATLAB® backgroundPool or accelerate code with Parallel Computing Toolbox™ ThreadPool.

Database Toolbox™ supports thread-based environments when creating PostgreSQL connection objects with the postgresql function.

Execute code on the Parallel Computing Toolbox™ ThreadPool for improved performance. For more information, see Run MATLAB Functions in Thread-Based Environment.

For an example of using DuckDB™ connection objects in a thread‑based environment, see Import Data from DuckDB Database File with Thread Workers.

Note

Create a dedicated database connection on each thread to maintain thread safety.

Version History

Introduced in R2020b

R2026a: Create PostgreSQL database connections with thread workers

You can now use thread workers when creating PostgreSQL connection objects for data import and export workflows. For more information, see Import Data from DuckDB Database File with Thread Workers.