Inserting a Matlab Float Array into postgresql float[] column

Question

I am using JDBC to access a postgresql database through Matlab, and have gotten hung up when trying to insert an array of values that I would rather store as an array instead of individual values. The Matlab code that I'm using is as follows:

insertCommand = 'INSERT INTO neuron (classifier_id, threshold, weights, neuron_num) VALUES     (?,?,?,?)';
statementObject = dbhandle.prepareStatement(insertCommand);
statementObject.setObject(1,1);
statementObject.setObject(2,output_thresholds(1));
statementObject.setArray(3,dbHandle.createArrayOf('"float8"',outputnodes(1,:)));
statementObject.setObject(4,1);

statementObject.execute;
close(statementObject);

Everything functions properly except for the line dealing with Arrays. The object outputnodes is a <5x23> double matrix, so I'm attempting to put the first <1x23> into my table.

I've tried several different combinations of names and quotes for the '"float8"' part of the createArrayof call, but I always get this error:

??? Java exception occurred:
org.postgresql.util.PSQLException: Unable to find server array type for provided name     "float8".
at org.postgresql.jdbc4.AbstractJdbc4Connection.createArrayOf(AbstractJdbc4Connection.java:82)
at org.postgresql.jdbc4.Jdbc4Connection.createArrayOf(Jdbc4Connection.java:19)

Error in ==> Databasetest at 22
statementObject.setArray(3,dbHandle.createArrayOf('"float8"',outputnodes(1,:)));

Answer 1

Performance of JDBC connector for arrays

I'd like to note that in the case you have to export rather big volumes of data containing arrays JDBC may not be the best choice. Firstly, its performance degrades due to the overhead caused by a conversion of native Matlab arrays into org.postgresql.jdbc.PgArray objects. Secondly, this may lead to a shortage of Java heap memory (and simply increasing Java heap memory size may not be a panacea). Both these points can be seen on the following picture illustrating the performance of datainsert method from Matlab Database Toolbox (it works with PostgreSQL exactly through a direct JDBC connection):

The blue graph displays the performance of batchParamExec command from PgMex library (see https://pgmex.alliedtesting.com/#batchparamexec for details). The endpoint of the red graph corresponds to a certain maximum data volume passed into the database by datainsert without any error. A data volume greater than that maximum causes “out of Java heap memory” problem (Java heap size is specified at the top of the figure). For further details of experiments please see the following paper with full benchmarking results for data insertion.

Example reworked

As can be seen PgMex based on libpq (the official C application programmer's interface to PostgreSQL) has greater performance and able to process volumes at least up to more than 2Gb. Using this library your code can be rewritten as follows (we assume below that all the parameters marked by <> signs are properly filled, that the table neuron already exists in the database and have fields classifier_id of int4, threshold of float8, weights of float8[] and neuron_num of int4 and, at last, that the variables classfierIdVec, output_thresholds, outputnodes and neuronNumVec are already defined and are numerical arrays of sizes shown in the comments in the code below; in the case the types of table fields are different you need to appropriately fix the last command of the code):

% Create the database connection
dbConn = com.allied.pgmex.pgmexec('connect',[...
    'host=<yourhost> dbname=<yourdb> port=<yourport> '...
    'user=<your_postgres_username> password=<your_postgres_password>']);

insertCommand = ['INSERT INTO neuron '...
    '(classifier_id, threshold, weights, neuron_num) VALUES ($1,$2,$3,$4)'];
SData = struct();
SData.classifier_id = classifierIdVec(:); % [nTuples x 1]
SData.threshold = output_thresholds(:); % [nTuples x 1]
SData.weights = outputnodes; % [nTuples x nWeights]
SData.neuron_num = neuronNumVec; % [nTuples x 1]
com.allied.pgmex.pgmexec('batchParamExec',dbConn,insertCommand,...
     '%int4 %float8 %float8[] %int4',SData);

It should be noted that outputnodes needs not to be cut along rows on separate arrays because the latter ones are of the same length. In the case of arrays for different tuples having different sizes it is necessary to pass them as a column cell array with each cell containing its own array for each tuple.

EDIT: Currently PgMex has free academic licensing.

Answer 2

I was getting confused with the documentation which all used double quotes, which Matlab doesn't allow, using only single quotes actually resolved this. The correct line was:

statementObject.setArray(3,dbHandle.createArrayOf('float8',outputnodes(1,:)));

instead of

 statementObject.setArray(3,dbHandle.createArrayOf('"float8"',outputnodes(1,:)));

I originally thought that the problem was with the alias that I was using for double precision was incorrect, but as Craig pointed out in the comment above this isn't the case.