Inserting list of dict as json array in Postgres from psycopg2

Question

I have this table in postgres

CREATE TABLE target (
    a json
)

Which I would like to insert data to from psycopg2 using

import psycopg2
import psycopg2.extras as extras

# a is list of list of dicts
a = [[{'a':1,'b':2}, {'d':3,'e':2}],
      [{'a':3,'b':1}, {'e':2,'f':6}]]

# convert a to list of tuples containing extras.Json objects
a = [(extras.Json(x),) for x in a]

# insert a to the database
query = ('WITH ins (a) AS '
         '(VALUES %s) '
         'INSERT INTO target (a) '
         'SELECT ins.a '
         'FROM ins;')

cursor = conn.cursor()

extras.execute_values(cursor, query, a)

However I get the error: column "a" is of type json but expression is of type text

This example is simplified to make the question clearer (which is why the example is using the WITH ins statement as I need to join a table to it. I would have thought that this should work but for some reason it doesn't. My suspicion is the problem is that I load it in to ins first and maybe it loads it as a text instead of json there by default for some reason.

How do I solve this?

Adrian Klaver · Accepted Answer · 2021-05-24 14:52:59Z

1

Something like this:

CREATE TABLE target (
    a json
)

query = "INSERT INTO target VALUES %s"  

b = [[extras.Json(row)] for row in a] 
cur = con.cursor()
extras.execute_values(cur, query, b)
con.commit()

select * from target;
                  a                   
--------------------------------------
 [{"a": 1, "b": 2}, {"d": 3, "e": 2}]
 [{"a": 3, "b": 1}, {"e": 2, "f": 6}]

Simplified to just INSERT directly from the input values. I created them as a list as execute_values can take a sequence of sequences or a sequence of dictionaries(if using named parameters).

UPDATE:

query = ('WITH ins (a) AS '
         '(VALUES %s) '
         'INSERT INTO target (a) '
         'SELECT ins.a::json '
         'FROM ins;')

edited May 24, 2021 at 14:52

answered May 23, 2021 at 19:57

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sev Over a year ago

Thank you so much for your answer. Unfortunately the WITH ins statement is necessary as I need to join a table to it. Maybe I oversimplified too much. Is there anyway to adjust your method to work the WITH ins ...

sev Over a year ago

Am I correct in assumning that this solution would load the json as a text into ins and then cast it from text to json when selecting?

Adrian Klaver Over a year ago

Yes, that is the case.

Collectives™ on Stack Overflow

Inserting list of dict as json array in Postgres from psycopg2

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related