Write Enumerator/ENUM type data to PostgreSQL database with sqlwrite (Database Toolbox)
1 回表示 (過去 30 日間)
古いコメントを表示
Hi everyone,
I have a problem uploading data to my PostgreSQL database with "sqlwrite" if the database contains an enumerated type. I created three ENUMs in my PostgreSQL database:
CREATE TYPE public.producttype AS ENUM
('FCR', 'aFRR', 'mFRR');
CREATE TYPE public.productname AS ENUM
('NEGPOS_00_24', 'NEG_HT', 'NEG_NT', 'POS_HT', 'POS_NT', 'NEG_00_04', 'NEG_04_08', 'NEG_08_12', 'NEG_12_16', 'NEG_16_20', 'NEG_20_24', 'POS_00_04', 'POS_04_08', 'POS_08_12', 'POS_12_16', 'POS_16_20', 'POS_20_24');
CREATE TYPE public.producttype AS ENUM
('FCR', 'aFRR', 'mFRR');
And then I created a test dataset in Matlab:
testdata.producttype = {'FCR'};
testdata.date = {'2018-06-05 00:00:00'};
testdata.productname = {'NEG_HT'};
testdata.capacity = 10;
testdata.capacityprice = 250;
testdata.energyprice = 1111;
testdata.acceptancerate = 1;
testdata.country = {'DE'};
testdata.kernanteilskennzeichnung = 0;
testdata= struct2table(testdata);
tablename = 'de_regelleistung';
Uploading this data with the function "exec" works:
exec(conn, ['INSERT INTO de_regelleistung (producttype, date, productname, capacity, capacityprice, energyprice, acceptancerate, country, kernanteilskennzeichnung)', ...
'VALUES (''', testdata.producttype{1}, ''', ''', testdata.date{1}, ''', ''', testdata.productname{1}, ''', ', num2str(testdata.capacity(1)), ', ', num2str(testdata.capacityprice(1)), ...
', ', num2str(testdata.energyprice(1)), ', ', num2str(testdata.acceptancerate(1)), ', ''', testdata.country{1}, ''', ', num2str(testdata.kernanteilskennzeichnung(1)), ');'])
But if I upload the table with the function "sqlwrite" ...
sqlwrite(conn, 'de_regelleistung', testdata)
... I get an error message:
Error using database.jdbc.connection/sqlwrite (line 172)
JDBC JDBC/ODBC Error: Batch entry 0 INSERT INTO de_regelleistung ( producttype, date, productname, capacity, capacityprice, energyprice, acceptancerate,
country, kernanteilskennzeichnung ) VALUES ( 'FCR','2018-06-05 00:00:00+02','NEG_HT',10.0,250.0,1111.0,1.0,'DE',0.0 ) was aborted: ERROR: column
"producttype" is of type producttype but expression is of type character varying
Hinweis: You will need to rewrite or cast the expression.
Position: 163 Call getNextException to see other errors in the batch..
I think this is due to the ENUMS. Can anyone help?
1 件のコメント
回答 (0 件)
参考
カテゴリ
Help Center および File Exchange で Reporting and Database Access についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!