%% Customize Options for Importing Data from PostgreSQL Database into MATLAB
% This example shows how to customize import options when importing data from a database table using the PostgreSQL native interface. Control the import options by creating an SQLImportOptions object. Then, customize the import options for multiple database columns. Import data using the sqlread function.
% The example uses the patients.xls spreadsheet, which contains patient information. Also, the example uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.
%
%% Create Database Connection
% 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);

%
%% Load Example Data
% 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)

%
%% Create SQLImportOptions Object
% Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.

opts = databaseImportOptions(conn,tablename);

% Display the default data types of the variables by accessing the VariableNames and VariableTypes properties of the SQLImportOptions object using dot notation.

disp([opts.VariableNames' opts.VariableTypes'])

%
%% Customize Import Options
% Change the data types of multiple variables. Convert the data type for all text variables to char. Also, convert the data type for all numeric variables to single.

textvars = ["lastname" "gender" "location" "selfassessedhealthstatus"];
opts = setoptions(opts,textvars,'Type',"char");

numvars = ["age" "height" "weight" "systolic" "smoker" "diastolic"];
opts = setoptions(opts,numvars,'Type',"single");

%
% Display the updated data types of the variables.

disp([opts.VariableNames' opts.VariableTypes'])

%
% Set the import options to replace missing data in the specified variables with the fill value unknown.

varnames = ["lastname" "location"];
opts = setoptions(opts,varnames,'FillValue',"unknown");

%
% Set the import options to omit rows with missing data in the lastname variable.

varname = "lastname";
opts = setoptions(opts,varname,'MissingRule',"omitrow");

%
%% Preview Data Before Importing
% Before importing the data, preview it by using the customized import options.

T = preview(opts)

%
%% Import Data Using Import Options
% Import the variables with the customized data types by using the sqlread function, and display the first eight rows of imported data.

T = sqlread(conn,tablename,opts);
head(T)

%
%% Delete Example Data and Close Database Connection
% Delete the patients database table using the execute function.

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

% Close the database connection.

close(conn)

% Copyright 2020 The MathWorks, Inc.