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

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?

[Paul Efford]

Hi! did you manage to solve it?

May you post here how?