Write Enumerator/ENUM type data to PostgreSQL database with sqlwrite (Database Toolbox)

1 次查看（过去 30 天）
Tim Szostakowski
Tim Szostakowski 2018-7-17
评论： Tim Szostakowski 2018-7-20
Hi everyone, I created an ENUM type in my PostgreSQL database:
CREATE TYPE public.producttype AS ENUM
('FCR', 'aFRR', 'mFRR');
And then I created an object in Matlab:
testdata = table();
testdata.producttype = {'FCR'};
testdata.value = 20;
If I upload my table to my PostgreSQL database with sqlwrite I get an error:
sqlwrite(conn, 'de_regelleistung', testdata)
Error message:
Error using database.jdbc.connection/sqlwrite (line 172)
JDBC JDBC/ODBC Error: Batch entry 0 INSERT INTO de_regelleistung ( producttype, value ) VALUES ( 'FCR', 20 ) 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..

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

回答（1 个）

Nikilesh Chilkuru
Nikilesh Chilkuru 2018-7-19
编辑：Nikilesh Chilkuru 2018-7-19
The problem lies with setting the "producttype" to {'FCR'}, this is treated as a cell and not enum type. To overcome this create an enum say "products" which holds all the enumerated values. Then while adding data to the table, set the "producttype" to products.FCR. Just make sure that the "products" enum is specified in the same directory. 
Step- 1) Create an Enum
classdef products
enumeration
FCR, aFRR, mFRR
end
end
For more information on Enums refer:  https://www.mathworks.com/help/matlab/matlab_oop/enumerations.html
2) Use this enum while adding data to the table:
>> testdata = table();
>> testdata.producttype = products.FCR;
>> testdata.value = 20;
You can use "varfun" function to get the data types of the columns of a table. 
>> varfun(@class,testdata,'OutputFormat','cell')
For more information on "varfun" function, refer:   https://www.mathworks.com/help/matlab/ref/varfun.html
  1 个评论
Tim Szostakowski
Tim Szostakowski 2018-7-20
Hi Nikilesh,
thank you for your answer!
I created an Enum in Matlab as you described:
classdef products
enumeration
FCR, aFRR, mFRR
end
end
And I also created the table:
testdata = table();
testdata.producttype = products.FCR;
testdata.value = 20;
No problem so far. But when I tried to upload the data to my PostgreSQL database using
sqlwrite(conn, 'de_regelleistung', testdata)
I got another error message:
Error using database.internal.utilities.TypeMapper.dataTypeConverter (line 198)
producttype column value must be a cell array of character vectors or string array. .
Do you know how to solve this?

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Reporting and Database Access 的更多信息

标签

产品


版本

R2018a

Community Treasure Hunt

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

Start Hunting!

Translated by