Effacer les filtres
Effacer les filtres

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

2 vues (au cours des 30 derniers jours)
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

Réponse acceptée

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

Plus de réponses (0)

Catégories

En savoir plus sur Tables dans Help Center et File Exchange

Produits


Version

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by