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

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..

Answer 1

[Nikilesh Chilkuru]

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

[Tim Szostakowski]

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?