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

6 次查看（过去 30 天）
Bill
Bill 2022-3-3
回答： MathWorks Computational Finance Team 2022-3-3
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

请先登录，再回答此问题。

采纳的回答

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

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 PostgreSQL Native Interface 的更多信息

标签

产品


版本

R2021b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by