postgresql - Create PostgreSQL native interface database connection - MATLAB (original) (raw)

Create PostgreSQL native interface database connection

Since R2020b

Syntax

Description

`conn` = postgresql([datasource](#mw%5F267a729c-56d3-4753-9b33-3b198a77d13b%5Fsep%5Fmw%5Fa2f973e8-1e16-42fd-9c7f-03c3bb265185),[username](#mw%5F267a729c-56d3-4753-9b33-3b198a77d13b%5Fsep%5Fbtcmi%5F9-username),[password](#mw%5F267a729c-56d3-4753-9b33-3b198a77d13b%5Fsep%5Fbtcmi%5F9-password)) creates a PostgreSQL native interface database connection using the specified data source, user name, and password. conn is a connection object.

example

`conn` = postgresql([username](#mw%5F267a729c-56d3-4753-9b33-3b198a77d13b%5Fsep%5Fbtcmi%5F9-username),[password](#mw%5F267a729c-56d3-4753-9b33-3b198a77d13b%5Fsep%5Fbtcmi%5F9-password),[Name,Value](#namevaluepairarguments)) creates a PostgreSQL native interface database connection using the specified user name and password, with additional options specified by one or more name-value pair arguments. For example, "Server","dbtb00" specifies the database server name asdbtb00.

example

Examples

collapse all

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.

Close the database connection 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 pair 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 user name and password shown. Specify the database server name dbtb00, database name toystore_doc, and port number 5432 by setting the corresponding name-value pair 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 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.

Close the database connection conn.

Input Arguments

collapse all

datasource — Data source name

character vector | string scalar

Data source name, specified as a character vector or string scalar. Specify the name of an existing data source.

Example: "myDataSource"

Data Types: char | string

username — User name

character vector | string scalar

User name required to access the database, specified as a character vector or string scalar. If no user name is required, specify an empty value "".

Data Types: char | string

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 "".

Data Types: char | string

Name-Value Arguments

Specify optional pairs of arguments asName1=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.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

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

Server — Database server name

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

Database server name or address, specified as the comma-separated pair consisting of 'Server' and a string scalar or character vector.

Example: "dbtb00"

Data Types: char | string

PortNumber — Port number

5432 (default) | numeric scalar

Port number, specified as the comma-separated pair consisting of'PortNumber' and a numeric scalar.

Example: 5432

Data Types: double

DatabaseName — Database name

"" (default) | string scalar | character vector

Database name, specified as the comma-separated pair consisting of'DatabaseName' and 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

Version History

Introduced in R2020b