Message Boards Message Boards

2 Replies
0 Total Likes
View groups...
Share this post:

How to insert rows into SQL database with column data type bigint ARRAY[]?

Posted 9 years ago

Hello, I am trying to insert rows of data into a PostgreSQL database table with column data-type BIGINT ARRAY[9]. The Wolfram Language Documentation offers the following solution for multiple-column inserts, but does not seem to offer guidance on inserts for the Array data-type. I realize this data type is not available with all relational database systems, and that may be the reason for the omission. However, this feature would be very convenient for inserting a nested list of integer values from Mathematica.

The 'regular SQL' insert statements for my RDBMS system are as follows. I will also provide the CREATE TABLE expression for my example.

CREATE TABLE test_scores ( id bigserial NOT NULL, Question character varying(255), Response_Options bigint [], Selected_Response bigint [], Score real [] ) ;

INSERT INTO test_ scores (question, response_ options,selected_ response, score) VALUES ('Q3','{1,2,3,4,5}','{4,5}','{60.}');

The Wolfram Language Documentation suggests, for multi-row inserts (of non-array data-types):

SQLInsert[conn, "TEST", {"COL1", "COL2"}, {{10, 10.5}, {20, 55.1}}]

I find no specific guidance on inserts into "array" column-types, and the following does not seem to work for my situation:

in:= testColumnHeaders= {"Question", "Response_Options", "Selected_Response", "Score"};

in:= testData = {{"Q3", {1, 2, 3, 4, 5}, {1, 2, 4, 5, 3}, {1.`, 0.75`, 0.25`, 
    0.`, 0.5`}}, 
    {Q4", {1, 2, 3, 4, 5}, {3}, {0.5`}}};

in:= SQLInsert[conn, "test_scores", testColumnHeaders, testData]

This produces the following output:

out:= JDBC:: error : Illegal value {1,2,3,4,5}

I haven't yet ruled-out a limitation of the JDBC driver, or some problem with type mismatch between the array data-type in the target table, and my test data-type. I will test further. In the meantime, does anyone have a working example of inserting into a SQL "Array" column (in particular, Postgres) from the SQLInsert[] function? That would at least prove that it is possible in concept, and rule out a limitation with the either the driver or SQLInsert[] functionality. Much appreciated!

POSTED BY: Caitlin Ramsey
2 Replies
Posted 9 years ago

Thank you, Udo. A useful suggestion, as usual. Indeed, I found that converting the nested list into a big string, and inserting into a VARCHAR column works quite nicely.

(I wouldn't want to be the one to query it on the other end, but it certainly gets the hot-potato out of my hands, and into the database!)

POSTED BY: Caitlin Ramsey

Nearest possibility at a glance to data type mapping seems to by SQLExpr, but using it one should define the data type of the corresponding attributes in the data base CLOB-like.

POSTED BY: Udo Krause
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract