sqlwrite doesn't play well with generated column

In my Postgresql database there is a generated 'date' column of type datetime that is generated from a timestamp column. If I omit this column when using sqlwrite, I get an error in line 155 of sqlwrite.m, which complains that the column after doesn't have the proper datetime type. This is odd behavior, but looking at the sqlwrite code, it checks the data types from the connection object and I could imagine something fishy is happening here.

If instead I try to pass an NaT to the "date" value, it passes line 155 of sqlwrite, but of course the database is unhappy, because I'm trying to write to a generated value, supposedly with a string "NaT" or something.

Even weirder is that everything works as long as I don't try to write to the "date" column AND don't write to both of the columns on either side of it. If I only write to one of them, regardless which, it works.

This seems like an edge case that wasn't checked or maybe there's a flag or something I need to set for generated columns. Anyone have any ideas?

[Geoff Hayes]

@Gregory - could you show your code and the full error message?

[Gregory]

@Geoff Hayes - thank you for the response.

The table is:

Column | Data type | NotNull

timestamp | timestamp | X

participant_id | int2 | X

exp_number | int2 |

date |varchar | generated

other_number | int2 |

After creating a connection and successfully using sqlread() to read from the table, I perform the following to insert into the table:

clear results
results(1).timestamp = string(datestr(now,30));
results(1).participant_id = 0;
results(1).exp_number = 0;
results(1).date = NaT;  %<-- generated from timestamp in database
results(1).other_number = 0;
results_table = struct2table(results);
sqlwrite(conn,tablename,results_table) % conn and tablename work as confirmed by sqlread()

Error using database.postgre.connection/sqlwrite>checkDataTypes (line 315)

date column value must be a numeric array or cell array of numeric scalars.

Error in database.postgre.connection/sqlwrite (line 155)

data = checkDataTypes(typNames,typeCategories,data);

If I comment out the date, it gives a similar error, but for the next column (which is supposed to be a number):

clear results
results(1).timestamp = string(datestr(now,30));
results(1).participant_id = 0;
results(1).exp_number = 0;
%results(1).date = NaT;  %<-- generated from timestamp in database
results(1).other_number = 0;
results_table = struct2table(results);
sqlwrite(conn,tablename,results_table) % conn and tablename work as confirmed by sqlread()

Error using database.postgre.connection/sqlwrite>checkDataTypes (line 386)

other_number column value must be a cell array of character vectors, string array or categorical array.

Error in database.postgre.connection/sqlwrite (line 155)

data = checkDataTypes(typNames,typeCategories,data);

If I pass an empty string as the date, this passes the check on line 155 of sqlwrite, but then gives the following database error:

clear results
results(1).timestamp = string(datestr(now,30));
results(1).participant_id = 0;
results(1).exp_number = 0;
results(1).date = "";  %<-- generated from timestamp in database
results(1).other_number = 0;
results_table = struct2table(results);
sqlwrite(conn,tablename,results_table) % conn and tablename work as confirmed by sqlread()

Error using postgre.internal.Connection/prepareStatement

Driver Error: ERROR: cannot insert a non-DEFAULT value into column "date"

DETAIL: Column "date" is a generated column.

Error in database.postgre.connection/sqlwrite (line 166)

result = conn.Handle.prepareStatement(statementName,insertStmt,length(columnnames));

Commenting out every column after 'date' results in a successful insertion (but without the data for 'other_number'):

clear results
results(1).timestamp = string(datestr(now,30));
results(1).participant_id = 0;
results(1).exp_number = 0;
% results(1).date = NaT;  %<-- generated from timestamp in database
% results(1).other_number = 0;
results_table = struct2table(results);
sqlwrite(conn,tablename,results_table)

This last one works, but this means that the database would have to be rearranged such that generated columns are always the last column

[Geoff Hayes]

@Gregory - that is a very interesting analysis of the way in which sqlwrite is working. I wonder if the MATLAB code is not making use of the column names (that you provide in the table) and instead rely simply on column order (which is what your analysis seems to suggest) and assumes that the order has to match the table order. Perhaps someone at @MathWorks Support Team can provide some insight.

[Gregory]

@Geoff Hayes: yes, I believe sqlwrite is just reading the column names from the connection object, instead of the column names from the table I provide as an argument, and therefore uses column order to infer which column I want. Thank you for the response; I also hope @MathWorks Support Team will look into this.