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?

Answer 1

[Leepakshi]

Hi Tim,

While using MATLAB’s sqlwrite with PostgreSQL ENUM columns, sqlwrite sends string data as varchar, but PostgreSQL does not automatically cast varchar to ENUM types in parameterized queries. In contrast, manual SQL statements (like with exec) allow implicit casting of string literals to ENUM.

To solve this, you can either continue using exec for inserts, use a staging table with all columns as varchar and then insert into your real table with explicit casts, or change your ENUM columns to varchar if you do not need the ENUM constraint.

You can refer to below MATLAB Community answer for more clarity:

https://in.mathworks.com/matlabcentral/answers/410688-write-enumerator-enum-type-data-to-postgresql-database-with-sqlwrite-database-toolbox?s_tid=srchtitle

And for postgresql documentation, you can refer below link:

https://www.postgresql.org/docs/current/datatype-enum.html

Hope it helps!