Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →

I was hoping someone may be able to help me out. I am trying to insert a python(2.7) list of jsonb elements into a postgresql(9.4) table with a column of datatype: jsonb[].

Here's some code:

import json
anArray = [{"name":"Joe","age":51,"yob":1964,"gender":"male"},{"name":"George","age":41,"dob":1974,"gender":"male"},{"name":"Nick","age":31,"dob":1984,"gender":"male"}]
myArray = []
#here's what I have so far: 
for e in anArray:
    myArray.append(json.dumps(e))
#this gives me
myArray = ['{"name":"Joe","age":51,"yob":1964,"gender":"male"}','{"name":"George","age":41,"dob":1974,"gender":"male"}','{"name":"Nick","age":31,"dob":1984,"gender":"male"}']
#insert commands
insert_sql = "INSERT INTO my_table (data) VALUES (%s);"
insert_data = (myArray, )
cursor.execute(insert_sql, insert_data)

Now when I try to insert myArray, psycopg2 gives me an error

psycopg2.ProgrammingError: column "data" is of type jsonb[] but expression is of type text[]

I'm not quite sure what the correct syntax is to insert this data into the table. Any help/pointers will be appreciated.

Solution Thanks to piro, it was a quick solution.

insert_sql = "INSERT INTO my_table (data) VALUES (%s::json[b]);"
insert_data = (myArray, )
cursor.execute(insert_sql, insert_data)

Cheers.

share|improve this question
    
an array of JSONs – JetJet13 Jul 27 '15 at 3:00
up vote 2 down vote accepted

This is not a psycopg error: it is a PostgreSQL error psycopg is just relying.

The error seems suggesting there is no implicit text[]->jsonb[] cast so you need to add a manual one:

INSERT INTO my_table (data) VALUES (%s::jsonb[]);
share|improve this answer
    
Well then... That worked like a charm. Thanks so much @piro ! – JetJet13 Jul 27 '15 at 16:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.