sqlwrite datatype error when trying to upload a table with a column of datetimes

Hi - I'm getting a checkDataTypes error when I try and upload a table into postgreSQL with a column of dates saved as datetimes.

I create a timetable, turn it into a table with timetable2table(). Then I try and upload that new table into PostgreSQL.

Here is a version of the code.

TimeTbl = timetable([1;2;3], 'RowTimes', datetime('1/31/2022'):calmonths(1):datetime('3/31/2022'), ...
    'VariableNames', {'Var1'});
Tbl = timetable2table(TimeTbl, "ConvertRowTimes", true);
Tbl.Properties.VariableNames{1} = 'Date';
conn = postgresql(username, password, 'DatabaseName', 'testdb');
sqlwrite(conn, 'testdb', Tbl);

Here's the error I'm getting:

Error using database.postgre.connection/sqlwrite>checkDataTypes (line 315)

Date column value must be a numeric array or cell array of numeric scalars.

Error in database.postgre.connection/sqlwrite (line 155)

data = checkDataTypes(typNames,typeCategories,data);

Thanks! Bill

Answer 1

[MathWorks Computational Finance Team]

Hi Bill,

I tried running a the same code on my end and it worked as intended. In my case the table named "testdb" didn't exist yet, so sqlwrite first creates the table with the appropriate SQL types to represent the data in the table. It looks like in your case "testdb" already exists on the database. In that case sqlwrite first checks to see if the MATLAB types in the table variable are compatible with the SQL data types in the database table. From the error message it looks like the existing "testdb" table has a "Date" property with a numeric data type instead of a timestamp. Perhaps these are numerical values representing POSIX time. I would double check the table definition on the database to see if it's really storing a date/timestamp type.

Best,

Kevin