fetch

Import results of SQL statement in PostgreSQL database into MATLAB

Since R2020b

collapse all in page

Syntax

results = fetch(conn,sqlquery)

results = fetch(conn,sqlquery,opts)

results = fetch(___,Name,Value)

[results,metadata] = fetch(___)

Description

results = fetch(conn,sqlquery) returns all rows of data after executing the SQL statement sqlquery for the connection object. fetch imports data in batches.

example

results = fetch(conn,sqlquery,opts) customizes options for importing data from an executed SQL query by using the SQLImportOptions object.

example

results = fetch(___,Name,Value) specifies additional options using one or more name-value arguments with any of the previous input argument combinations. For example, specify MaxRows = 5 to import five rows of data.

example

[results,metadata] = fetch(___) also returns the metadata table, which contains metadata information about the imported data.

example

Examples

collapse all

Import All Data Using PostgreSQL Native Interface

Open Live Script

Import all product data from a PostgreSQL database table into MATLAB® using the PostgreSQL native interface and the fetch function. Determine the highest unit cost among products in the table. Then, use a row filter to import only the data for products with a unit cost less than 15.

Create a PostgreSQL native interface database connection to a PostgreSQL database using a data source, username, and password. The database contains the table productTable.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

Import all the data from productTable by using the connection object and SQL query. Then, display the first five rows of the imported data.

sqlquery = "SELECT * FROM productTable";
data = fetch(conn,sqlquery);
head(data,5)

    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13        "Victorian Doll" 
          8          2.1257e+05          1001             5        "Train Set"      
          7          3.8912e+05          1007            16        "Engine Kit"     
          2          4.0031e+05          1002             9        "Painting Set"   
          4          4.0034e+05          1008            21        "Space Cruiser"

Determine the highest unit cost for all products in the table.

max(data.unitcost)

ans = 24

Now, import the data using a row filter. The filter condition is that unitсost must be less than 15.

rf = rowfilter("unitcost");
rf = rf.unitcost < 15;
data = fetch(conn,sqlquery,"RowFilter",rf);

Again, display the first five rows of the imported data.

head(data,5)

    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          9          1.2597e+05          1003            13       "Victorian Doll"  
          8          2.1257e+05          1001             5       "Train Set"       
          2          4.0031e+05          1002             9       "Painting Set"    
          1          4.0034e+05          1001            14       "Building Blocks" 
          5          4.0046e+05          1005             3       "Tin Soldier"

Close the database connection.

close(conn)

Import Data from SQL Query Using Import Options

Open Live Script

Customize import options when importing data from the results of an SQL query on a PostgreSQL database using the PostgreSQL native interface. Control the import options by creating an SQLImportOptions object. Then, customize import options for different columns in the SQL query. Import data using the fetch function.

This example uses the employees_database.mat file, which contains the columns first_name, hire_date, and department_name. The example uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.

Create a PostgreSQL native interface database connection to a PostgreSQL database with a data source name, username, and password.

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

conn = postgresql(datasource,username,password);

Load employee information into the MATLAB® workspace.

employeedata = load("employees_database.mat");

Create the employees and departments database tables using the employee information.

emps = employeedata.employees;
depts = employeedata.departments;

sqlwrite(conn,"employees",emps)
sqlwrite(conn,"departments",depts)

Create an SQLImportOptions object using an SQL query and the databaseImportOptions function. This query retrieves all information for employees who are sales managers or programmers.

sqlquery = strcat("SELECT * from employees e join departments d ", ...
    "on (e.department_id = d.department_id) WHERE ", ...
    "(job_id = 'IT_PROG' or job_id = 'SA_MAN')");
opts = databaseImportOptions(conn,sqlquery)

opts = 
  SQLImportOptions with properties:

           ExcludeDuplicates: false
          VariableNamingRule: 'preserve'

               VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
               VariableTypes: {'double', 'string', 'string' ... and 13 more}
       SelectedVariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
                  FillValues: { NaN,  <missing>,  <missing>  ... and 13 more }
                   RowFilter: <unconstrained> 

             VariableOptions: Show all 16 VariableOptions

Display the current import options for the variables selected in the SelectedVariableNames property of the SQLImportOptions object.

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)

varOpts = 
    1x16 SQLVariableImportOptions array with properties:

   Variable Options:
                         (1) |          (2) |         (3) |       (4) |            (5) |         (6) |       (7) |      (8) |              (9) |         (10) |            (11) |        (12) |              (13) |              (14) |           (15) |          (16)
         Name: 'employee_id' | 'first_name' | 'last_name' |   'email' | 'phone_number' | 'hire_date' |  'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'department_id_1' | 'department_name' | 'manager_id_1' | 'location_id'
         Type:      'double' |     'string' |    'string' |  'string' |       'string' |  'datetime' |  'string' | 'double' |         'double' |     'double' |        'double' |   'logical' |          'double' |          'string' |       'double' |      'double'
  MissingRule:        'fill' |       'fill' |      'fill' |    'fill' |         'fill' |      'fill' |    'fill' |   'fill' |           'fill' |       'fill' |          'fill' |      'fill' |            'fill' |            'fill' |         'fill' |        'fill'
    FillValue:           NaN |    <missing> |   <missing> | <missing> |      <missing> |         NaT | <missing> |      NaN |              NaN |          NaN |             NaN |           0 |               NaN |         <missing> |            NaN |           NaN

	To access sub-properties of each variable, use getoptions

Change the data types for the hire_date, department_name, and first_name variables using the setoptions function. Then, display the updated import options. For efficiency, change the data type of the hire_date variable to string. Because department_name designates a finite set of repeating values, change the data type of this variable to categorical. Because first_name stores text data, change the data type of this variable to char.

opts = setoptions(opts,"hire_date","Type","string");
opts = setoptions(opts,"department_name","Type","categorical");
opts = setoptions(opts,"first_name","Type","char");

vars = opts.SelectedVariableNames;
varOpts = getoptions(opts,vars)

varOpts = 
    1x16 SQLVariableImportOptions array with properties:

   Variable Options:
                         (1) |          (2) |         (3) |       (4) |            (5) |         (6) |       (7) |      (8) |              (9) |         (10) |            (11) |        (12) |              (13) |              (14) |           (15) |          (16)
         Name: 'employee_id' | 'first_name' | 'last_name' |   'email' | 'phone_number' | 'hire_date' |  'job_id' | 'salary' | 'commission_pct' | 'manager_id' | 'department_id' | 'temporary' | 'department_id_1' | 'department_name' | 'manager_id_1' | 'location_id'
         Type:      'double' |       'char' |    'string' |  'string' |       'string' |    'string' |  'string' | 'double' |         'double' |     'double' |        'double' |   'logical' |          'double' |     'categorical' |       'double' |      'double'
  MissingRule:        'fill' |       'fill' |      'fill' |    'fill' |         'fill' |      'fill' |    'fill' |   'fill' |           'fill' |       'fill' |          'fill' |      'fill' |            'fill' |            'fill' |         'fill' |        'fill'
    FillValue:           NaN |           '' |   <missing> | <missing> |      <missing> |   <missing> | <missing> |      NaN |              NaN |          NaN |             NaN |           0 |               NaN |       <undefined> |            NaN |           NaN

	To access sub-properties of each variable, use getoptions

Select the three modified variables using the SelectVariableNames property.

opts.SelectedVariableNames = ["first_name","hire_date","department_name"];

Set the filter condition to import only the data for the employees hired before January 1, 2006.

opts.RowFilter = opts.RowFilter.hire_date < datetime(2006,01,01)

opts = 
  SQLImportOptions with properties:

           ExcludeDuplicates: false
          VariableNamingRule: 'preserve'

               VariableNames: {'employee_id', 'first_name', 'last_name' ... and 13 more}
               VariableTypes: {'double', 'char', 'string' ... and 13 more}
       SelectedVariableNames: {'first_name', 'hire_date', 'department_name'}
                  FillValues: { NaN, '',  <missing>  ... and 13 more }
                   RowFilter: hire_date < 01-Jan-2006 

             VariableOptions: Show all 16 VariableOptions

Import and display the results of the SQL query using the fetch function.

employees_data = fetch(conn,sqlquery,opts)

employees_data=4×3 table
    first_name           hire_date          department_name
    ___________    _____________________    _______________

    {'David'  }    "2005-06-25 00:00:00"         IT        
    {'Alberto'}    "2005-03-10 00:00:00"         Sales     
    {'Karen'  }    "2005-01-05 00:00:00"         Sales     
    {'John'   }    "2004-10-01 00:00:00"         Sales

Delete the employees and departments database tables using the execute function.

execute(conn,"DROP TABLE employees")
execute(conn,"DROP TABLE departments")

Close the database connection.

close(conn)

Import Data from SQL Query as Structure

Open Live Script

Specify the data return format and the number of imported rows for the results of an SQL query. Import data using the SQL query and the fetch function.

This example uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.

Create a PostgreSQL native interface database connection to a PostgreSQL database with a data source name, user name, and password.

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

conn = postgresql(datasource,username,password);

Load patient information into the MATLAB® workspace.

patients = readtable('patients.xls');

Create the patients database table using the patient information.

tablename = "patients";
sqlwrite(conn,tablename,patients)

Select all data from the patients database table and import five rows from the table as a structure. Use the 'DataReturnFormat' name-value pair argument to specify returning the data as a structure. Also, use the 'MaxRows' name-value pair argument to specify five rows. Display the imported data.

sqlquery = strcat("SELECT * FROM ",tablename);
results = fetch(conn,sqlquery,'DataReturnFormat',"structure", ...
    'MaxRows',5)

results=5×1 struct array with fields:
    lastname
    gender
    age
    location
    height
    weight
    smoker
    systolic
    diastolic
    selfassessedhealthstatus

Delete the patients database table using the execute function.

sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)

Close the database connection.

close(conn)

Retrieve Metadata Information About Imported Data

Open Live Script

Retrieve metadata information when importing data from an SQL query. Import data using the fetch function and explore the metadata information by using dot notation.

This example uses the outages.csv file, which contains outage data. Also, the example uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.

Create a PostgreSQL native interface database connection to a PostgreSQL database with a data source name, user name, and password.

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

conn = postgresql(datasource,username,password);

Load outage information into the MATLAB® workspace.

outages = readtable("outages.csv");

Create the outages database table using the outage information. Use the 'ColumnType' name-value pair argument to customize the data types of the variables in the outages table.

tablename = "outages";
sqlwrite(conn,tablename,outages, ...
    'ColumnType',["varchar(120)","timestamp","numeric(38,16)", ...
    "numeric(38,16)","timestamp","varchar(150)"])

Import the data into the MATLAB workspace and return metadata information about the imported data.

sqlquery = "SELECT * FROM outages";
[results,metadata] = fetch(conn,sqlquery);

View the names of the variables in the imported data.

metadata.Properties.RowNames

ans = 6×1 cell
    {'region'         }
    {'outagetime'     }
    {'loss'           }
    {'customers'      }
    {'restorationtime'}
    {'cause'          }

View the data type of each variable in the imported data.

metadata.VariableType

ans = 6×1 cell
    {'string'  }
    {'datetime'}
    {'double'  }
    {'double'  }
    {'datetime'}
    {'string'  }

View the missing data value for each variable in the imported data.

metadata.FillValue

ans=6×1 cell array
    {1×1 missing}
    {[NaT      ]}
    {[      NaN]}
    {[      NaN]}
    {[NaT      ]}
    {1×1 missing}

View the indices of the missing data for each variable in the imported data.

metadata.MissingRows

ans=6×1 cell array
    {  0×1 double}
    {  0×1 double}
    {604×1 double}
    {328×1 double}
    { 29×1 double}
    {  0×1 double}

Display the first eight rows of the imported data that contain missing restoration time values. data contains restoration time values in the fifth variable. Use the numeric indices to find the rows with missing data.

index = metadata.MissingRows{5,1};
nullrestoration = results(index,:);
head(nullrestoration)

ans=8×6 table
      region            outagetime          loss     customers     restorationtime          cause       
    ___________    ____________________    ______    __________    _______________    __________________

    "SouthEast"    23-Jan-2003 00:49:00    530.14    2.1204e+05          NaT          "winter storm"    
    "NorthEast"    18-Sep-2004 05:54:00         0             0          NaT          "equipment fault" 
    "MidWest"      20-Apr-2002 16:46:00     23141           NaN          NaT          "unknown"         
    "NorthEast"    16-Sep-2004 19:42:00      4718           NaN          NaT          "unknown"         
    "SouthEast"    14-Sep-2005 15:45:00    1839.2    3.4144e+05          NaT          "severe storm"    
    "SouthEast"    17-Aug-2004 17:34:00     624.1    1.7879e+05          NaT          "severe storm"    
    "SouthEast"    28-Jan-2006 23:13:00    498.78           NaN          NaT          "energy emergency"
    "West"         20-Jun-2003 18:22:00         0             0          NaT          "energy emergency"

Delete the outages database table using the execute function.

sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)

Close the database connection.

close(conn)

Input Arguments

collapse all

`conn` — PostgreSQL native interface database connection
`connection` object

PostgreSQL native interface database connection, specified as a connection object.

`sqlquery` — SQL statement
character vector | string scalar

SQL statement, specified as a character vector or string scalar. The SQL statement can be any valid SQL statement, including nested queries. The SQL statement can be a stored procedure, such as {call sp_name (parm1,parm2,...)}. For stored procedures that return one or more result sets, use the fetch function.

Data Types: char | string

`opts` — Database import options
`SQLImportOptions` object

Database import options, specified as an SQLImportOptions object.

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.

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

Example: results = fetch(conn,sqlquery,'MaxRows',50,'DataReturnFormat','structure') imports 50 rows of data as a structure.

`MaxRows` — Maximum number of rows to return
positive numeric scalar

Maximum number of rows to return, specified as the comma-separated pair consisting of 'MaxRows' and a positive numeric scalar. By default, the fetch function returns all rows from the executed SQL query. Use this name-value pair argument to limit the number of rows imported into MATLAB^®.

Example: 'MaxRows',10

Data Types: double

`DataReturnFormat` — Data return format
`'table'` (default) | `'cellarray'` | `'numeric'` | `'structure'`

Data return format, specified as the comma-separated pair consisting of 'DataReturnFormat' and one of these values:

'table'
'cellarray'
'numeric'
'structure'

Use the 'DataReturnFormat' name-value pair argument to specify the data type of the results data. To specify integer classes for numeric data, use the opts input argument.

You can specify the value using a character vector or string scalar.

Example: 'DataReturnFormat','cellarray' imports data as a cell array.

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

Variable naming rule, specified as the comma-separated pair consisting of 'VariableNamingRule' and one of these values:

"preserve" — Preserve most variable names when the fetch function imports data. For details, see the Limitations section.
"modify" — Remove non-ASCII characters from variable names when the fetch function imports data.

Example: 'VariableNamingRule',"modify"

Data Types: string

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

Row filter condition, specified as a matlab.io.RowFilter object.

Example: rf = rowfilter("productnumber"); rf = rf.productnumber <= 5; fetch(conn,sqlquery,"RowFilter",rf)

Output Arguments

collapse all

`results` — Result data
table (default) | cell array | structure | numeric matrix

Result data, returned as a table, cell array, structure, or numeric matrix. The result data contains all rows of data from the executed SQL statement by default.

Use the 'MaxRows' name-value pair argument to specify the number of rows of data to import. Use the 'DataReturnFormat' name-value pair argument to specify the data type of the result data.

When the executed SQL statement does not return any rows, the result data is an empty table.

When you import data, the fetch function converts the data type of each column from the PostgreSQL database to the MATLAB data type. This table maps the data type of a database column to the converted MATLAB data type. The first and second columns contain the scalar data type mappings, whereas the third and fourth columns contain the array data type mappings.

Details for how MATLAB reads PostgreSQL array types consist of the following:

MATLAB reads columns of PostgreSQL array types into cell arrays. The dimensions of the array in each cell match the dimension of the array stored in the PostgreSQL database table row.
The data of the arrays in the MATLAB cell arrays depends on the underlying data in the PostgreSQL arrays.
The default data type of each PostgreSQL array type matches its scalar equivalent.

Scalar Data Type Mappings		Array Data Type Mappings (since R2024b)
PostgreSQL	MATLAB	PostgreSQL	MATLAB
`Boolean`	`logical`	`Boolean []`	Cell array of logical values
`Smallint`	`double`	`Smallint []`	Cell array of double values
`Integer`	`double`	`Integer []`	Cell array of double values
`Bigint`	`double`	`Bigint []`	Cell array of double values
`Decimal`	`double`	`Decimal []`	Cell array of double values
`Numeric`	`double`	`Numeric []`	Cell array of double values
`Real`	`double`	`Real []`	Cell array of double values
`Double precision`	`double`	`Double precision []`	Cell array of double values
`Smallserial`	`double`	N/A	N/A
`Serial`	`double`	N/A	N/A
`Bigserial`	`double`	N/A	N/A
`Money`	`double`	`Money []`	Cell array of double values
`Varchar`	`string`	`Varchar []`	Cell array of strings
`Char`	`string`	`Char []`	Cell array of strings
`Text`	`string`	`Text []`	Cell array of strings
`Bytea`	`string`	`Bytea []`	Cell array of strings
`Timestamp`	`datetime`	`Timestamp []`	Cell array of datetimes
`Timestampz`	`datetime`	`Timestampz []`	Cell array of datetimes
`Abstime`	`datetime`	`Abstime []`	Cell array of datetimes
`Date`	`datetime`	`Date []`	Cell array of datetimes
`Time`	`duration`	`Time []`	Cell array of durations
`Timez`	`duration`	`Timez []`	Cell array of durations
`Interval`	`calendarDuration`	`Interval []`	Cell array of calendarDurations
`Reltime`	`calendarDuration`	`Reltime []`	Cell array of calendarDurations
`Enum`	`categorical`	`Enum []`	Cell array of categoricals
`Cidr`	`string`	`Cidr []`	Cell array of strings
`Inet`	`string`	`Inet []`	Cell array of strings
`Macaddr`	`string`	`Macaddr []`	Cell array of strings
`Uuid`	`string`	`Uuid []`	Cell array of strings
`Xml`	`string`	`Xml []`	Cell array of strings

`metadata` — Metadata information
table

Metadata information, returned as a table with these variables.

Variable Name	Variable Description	Variable Data Type
`VariableType`	Data type of each variable in the imported data	Cell array of character vectors
`FillValue`	Value of missing data for each variable in the imported data	Cell array of missing data values
`MissingRows`	Indices for each occurrence of missing data in each variable of the imported data	Cell array of numeric indices

By default, the fetch function imports text data as a character vector and numeric data as a double. FillValue is an empty character array (for text data) or NaN (for numeric data) by default. To change the missing data value to another value, use the SQLImportOptions object.

The RowNames property of the metadata table contains the names of the variables in the imported data.

Limitations

The name-value argument VariableNamingRule has these limitations:

The fetch function returns an error if you specify the VariableNamingRule name-value argument and set the DataReturnFormat name-value argument to "cellarray", "structure", or "numeric".
The fetch function returns a warning if you set the VariableNamingRule property of the SQLImportOptions object to "preserve" and set the DataReturnFormat name-value argument to "structure".
The fetch function returns an error if you use the VariableNamingRule name-value argument with the SQLImportOptions object opts.
If you set the VariableNamingRule name-value argument to the value "modify":
- These variable names are reserved identifiers for the table data type: Properties, RowNames, and VariableNames.
- The length of each variable name must be less than the number returned by namelengthmax.

The name-value argument RowFilter has this limitation:

The fetch function returns an error if you specify the RowFilter name-value argument with the SQLImportOptions object opts. It is ambiguous which of the RowFilter object to use in this case, especially if the filter conditions are different.

Alternative Functionality

App

The fetch function imports data using the command line. To import data interactively, use the Database Explorer app.

Version History

Introduced in R2020b

expand all

R2024b: Support for reading array types

You can now read array types without any extra data conversions.

R2023a: Selectively import rows of data based on filter condition

You can use the RowFilter name-value argument to selectively import rows of data from a database table.

fetch

Syntax

Description

Examples

Import All Data Using PostgreSQL Native Interface

Import Data from SQL Query Using Import Options

Import Data from SQL Query as Structure

Retrieve Metadata Information About Imported Data

Input Arguments

`conn` — PostgreSQL native interface database connection
`connection` object

`sqlquery` — SQL statement
character vector | string scalar

`opts` — Database import options
`SQLImportOptions` object

Name-Value Arguments

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`DataReturnFormat` — Data return format
`'table'` (default) | `'cellarray'` | `'numeric'` | `'structure'`

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

Output Arguments

`results` — Result data
table (default) | cell array | structure | numeric matrix

`metadata` — Metadata information
table

Limitations

Alternative Functionality

App

Version History

R2024b: Support for reading array types

R2023a: Selectively import rows of data based on filter condition

See Also

Functions

Topics

External Websites

fetch

Syntax

Description

Examples

Import All Data Using PostgreSQL Native Interface

Import Data from SQL Query Using Import Options

Import Data from SQL Query as Structure

Retrieve Metadata Information About Imported Data

Input Arguments

conn — PostgreSQL native interface database connection connection object

sqlquery — SQL statement character vector | string scalar

opts — Database import options SQLImportOptions object

Name-Value Arguments

MaxRows — Maximum number of rows to return positive numeric scalar

DataReturnFormat — Data return format 'table' (default) | 'cellarray' | 'numeric' | 'structure'

VariableNamingRule — Variable naming rule "preserve" (default) | "modify"

RowFilter — Row filter condition <unconstrained> (default) | matlab.io.RowFilter object

Output Arguments

results — Result data table (default) | cell array | structure | numeric matrix

metadata — Metadata information table

Limitations

Alternative Functionality

App

Version History

R2024b: Support for reading array types

R2023a: Selectively import rows of data based on filter condition

See Also

Functions

Topics

External Websites

`conn` — PostgreSQL native interface database connection
`connection` object

`sqlquery` — SQL statement
character vector | string scalar

`opts` — Database import options
`SQLImportOptions` object

`MaxRows` — Maximum number of rows to return
positive numeric scalar

`DataReturnFormat` — Data return format
`'table'` (default) | `'cellarray'` | `'numeric'` | `'structure'`

`VariableNamingRule` — Variable naming rule
`"preserve"` (default) | `"modify"`

`RowFilter` — Row filter condition
`<unconstrained>` (default) | `matlab.io.RowFilter` object

`results` — Result data
table (default) | cell array | structure | numeric matrix

`metadata` — Metadata information
table