Join the Stack Overflow Community
Stack Overflow is a community of 6.8 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up

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.