sqlwrite

Insert MATLAB data into PostgreSQL database table

collapse all in page

Syntax

sqlwrite(conn,tablename,data)
sqlwrite(conn,tablename,data,Name,Value)

Description

example

sqlwrite(conn,tablename,data) inserts data from a MATLAB® table into a database table. If the table exists in the database, this function appends the data from the MATLAB table as rows in the existing database table. If the table does not exist in the database, this function creates a table with the specified table name and then inserts the data as rows in the new table. This syntax is the equivalent of executing SQL statements that contain the CREATE TABLE and INSERT INTO ANSI SQL syntaxes.

example

sqlwrite(conn,tablename,data,Name,Value) uses additional options specified by one or more name-value pair arguments. For example, 'Catalog',"toystore_doc" inserts data into a database table that is located in the database catalog named toystore_doc.

Examples

collapse all

Open Live Script

Use a PostgreSQL native interface database connection to append product data from a MATLAB® table into an existing table in a PostgreSQL database.

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

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

To view the existing database table productTable before appending data, import its contents into MATLAB and display the last few rows.

tablename = "productTable";
rows = sqlread(conn,tablename);
tail(rows,3)
ans=3×5 table
    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          6          4.0088e+05          1004             8          "Sail Boat"    
          3            4.01e+05          1009            17          "Slinky"       
         10          8.8865e+05          1006            24          "Teddy Bear"

Create a MATLAB table that contains the data for one product.

data = table(30,500000,1000,25,"Rubik's Cube", ...
    'VariableNames',["productnumber" "stocknumber" ...
    "suppliernumber" "unitcost" "productdescription"]);

Append the product data into the database table productTable.

sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB again and display the last few rows. The results contain a new row for the inserted product.

rows = sqlread(conn,tablename);
tail(rows,4)
ans=4×5 table
    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

          6          4.0088e+05          1004             8         "Sail Boat"     
          3            4.01e+05          1009            17         "Slinky"        
         10          8.8865e+05          1006            24         "Teddy Bear"    
         30               5e+05          1000            25         "Rubik's Cube"

Close the database connection.

close(conn)
Open Live Script

Use a PostgreSQL native interface database connection to insert product data from MATLAB® into a new table in a PostgreSQL database.

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

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

Create a MATLAB table that contains data for two products.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productnumber" ...
    "stocknumber" "suppliernumber" "unitcost" "productdescription"]);

Insert the product data into a new database table named toytable.

tablename = "toytable";
sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)
rows=2×5 table
    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

         30             5e+05            1000            25         "Rubik's Cube"  
         40             6e+05            2000            30         "Doll House"

Close the database connection.

close(conn)
Open Live Script

Use a PostgreSQL native interface database connection to insert product data from MATLAB® into a new table in a PostgreSQL database. Specify the data types of the columns in the new database table.

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

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

Create a MATLAB table that contains data for two products.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productnumber" ...
    "stocknumber" "suppliernumber" "unitcost" "productdescription"]);

Insert the product data into a new database table named toytable. Use the 'ColumnType' name-value pair argument and a string array to specify the data types of all the columns in the database table.

tablename = "toytable";
coltypes = ["numeric" "numeric" "numeric" "numeric" "varchar(255)"];
sqlwrite(conn,tablename,data,'ColumnType',coltypes)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)
rows=2×5 table
    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    _____________    ___________    ______________    ________    __________________

         30             5e+05            1000            25         "Rubik's Cube"  
         40             6e+05            2000            30         "Doll House"

Close the database connection.

close(conn)

Input Arguments

collapse all

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

Database table name, specified as a character vector or string scalar denoting the name of a table in the database.

Example: 'employees'

Data Types: char | string

Data to insert into a database table, specified as a table.

The valid data types in a MATLAB table are:

  • Numeric array

  • Cell array of numeric arrays

  • Cell array of character vectors

  • String array

  • Datetime array

  • Duration array

  • Calendar duration array

  • Logical array

  • Cell array of logical arrays

The numeric array can contain these data types:

  • int8

  • uint8

  • int16

  • uint16

  • int32

  • uint32

  • int64

  • uint64

  • single

  • double

For date and time data, supported formats are:

  • Date — 'yyyy-MM-dd'

  • Time — 'hh:mm:ss'

  • Timestamp — 'yyyy-MM-dd HH:mm:ss'

If the date and time data is specified in an invalid format, then the sqlwrite function automatically converts the data to a supported format.

If the cell array of character vectors or string array is specified in an invalid format, then the sqlwrite function enables the database driver to check the format. If the format is unexpected, then the database driver throws an error.

You can insert data in an existing database table or a new database table. The data types of variables in data vary depending on whether the database table exists. For valid data types, see Data Types for Existing Table and Data Types for New Table.

Note

The sqlwrite function supports only the table data type for the data input argument. To insert data stored in a structure, cell array, or numeric matrix, convert the data to a table by using the struct2table, cell2table, and array2table functions, respectively.

To insert missing data, see Accepted Missing Data.

Example: table([10;20],{'M';'F'})

Data Types for Existing Table

The variable names of the MATLAB table must match the column names in the database table. The sqlwrite function is case-sensitive.

When you insert data into a database table, use the data types shown in the following table to ensure that the data has the correct data type. This table matches the valid data types of the MATLAB table variable to the data types of the database column. For example, when you insert data into a database column that has the BIT data type, ensure that the corresponding variable in the MATLAB table is a logical array or cell array of logical arrays.

Data Type of MATLAB Table VariableData Type of Existing Database Column
Numeric array or cell array of numeric arrays

  • smallint

  • integer

  • bigint

  • decimal

  • numeric

  • real

  • double precision

  • smallserial

  • serial

  • bigserial

Cell array of character vectors, string array, datetime array, or duration array

  • date

  • time

  • timestamp

Calendar duration arrayinterval
Logical array or cell array of logical arraysbit
Cell array of character vectors or string array

  • char

  • varchar

Data Types for New Table

The specified table name for the new database table must be unique across all tables in the database.

The valid data types in a MATLAB table are:

  • Numeric array

  • Cell array of character vectors

  • String array

  • Datetime array

  • Duration array

  • Calendar duration array

  • Logical array

The sqlwrite function ignores any invalid data types and inserts only the valid variables from MATLAB as columns in a new database table.

The sqlwrite function converts the data type of the variable into the default data type of the column in the database table. The following table matches the valid data types of the MATLAB table variable to the default data types of the database column.

Data Type of MATLAB Table VariableDefault Data Type of Database Column

int8 array

smallint

int16 array

smallint

int32 array

integer

int64 array

bigint

logical array

boolean

single or double array

numeric

datetime array

timestamp

duration array

time

calendarDuration array

interval

cell array of character vectors or string array

varchar

Note

The size of this column equals the sum of the maximum length of a string in the string array and 100.

To specify database-specific column data types instead of the defaults, use the 'ColumnType' name-value pair argument. For example, you can specify 'ColumnType',"bigint" to create a BIGINT column in the new database table.

Also, using the 'ColumnType' name-value pair argument, you can specify other data types that are not in the default list. For example, to insert currency, specify 'ColumnType',"money".

Accepted Missing Data

The accepted missing data for inserting data into a database depends on the data type of the MATLAB table variable and the data type of the column in the database. The following table matches the data type of the MATLAB table variable to the data type of the database column and specifies the accepted missing data to use in each case.

Data Type of MATLAB Table VariableData Type of Database ColumnAccepted Missing Data
datetime arraydate or timestampNaT
duration arraytimeNaN
calendarDuration arrayintervalNaN
double or single array or cell array of double or single arrays

numeric

NaN, [], or ''
cell array of character vectorsdate or timestamp'NaT' or ''
cell array of character vectorstime'NaN' or ''
cell array of character vectorschar, varchar, or other text data type''
string arraydate or timestamp"", "NaT", or missing
string arraytime"", "NaN", or missing
string arraychar, varchar, or other text data typemissing

Data Types: table

Name-Value Pair Arguments

Specify optional comma-separated pairs of Name,Value arguments. Name is the argument name and Value is the corresponding value. Name must appear inside quotes. You can specify several name and value pair arguments in any order as Name1,Value1,...,NameN,ValueN.

Example: sqlwrite(conn,"tablename",data,'ColumnType',["numeric" "timestamp" "money"]) inserts data into a new database table named tablename by specifying data types for all columns in the new database table.

Database catalog name, specified as the comma-separated pair consisting of 'Catalog' and a character vector or string scalar. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have numerous catalogs.

Example: 'Catalog','toy_store'

Data Types: char | string

Database schema name, specified as the comma-separated pair consisting of 'Schema' and a character vector or string scalar. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.

Example: 'Schema','dbo'

Data Types: char | string

Database column types, specified as the comma-separated pair consisting of 'ColumnType' and a character vector, string scalar, cell array of character vectors, or string array. Use this name-value pair argument to define custom data types for the columns in a database table. Specify a column type for each column in the table.

Example: 'ColumnType',["numeric" "varchar(400)"]

Data Types: char | string | cell

See Also

| | | | |

Topics

Introduced in R2020b

Database Toolbox Documentation

Support