sqlwrite datatype error when trying to upload a table with a column of datetimes
5 ビュー (過去 30 日間)
古いコメントを表示
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
0 件のコメント
採用された回答
MathWorks Computational Finance Team
2022 年 3 月 3 日
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 件のコメント
その他の回答 (0 件)
参考
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!