Psycopg2 execute_values sending all values as text

Question

I have this table in postgres

CREATE TABLE target (
    a json
    b integer
    c text []
    id integer
    CONSTRAINT id_fkey FOREIGN KEY (id)
        REFERENCES public.other_table(id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
)

Which I would like to insert data to from psycopg2 using

import psycopg2
import psycopg2.extras as extras

# data is of the form dict, integer, list(string), string <- used to get fkey id
data = [[extras.Json([{'a':1,'b':2}, {'d':3,'e':2}]), 1, ['hello', 'world'], 'ident1'],
        [extras.Json([{'a':4,'b':3}, {'d':1,'e':9}]), 5, ['hello2', 'world2'], 'ident2']]


# convert data to list of tuples containing objects
x = [tuple(u) for u in data]

# insert data to the database
query = ('WITH ins (a, b, c, ident) AS '
         '(VALUES %s) '
         'INSERT INTO target (a, b, c, id) '
         'SELECT '
            'ins.a '
            'ins.b '
            'ins.c '
            'other_table.id'
        'FROM '
            'ins '
            'LEFT JOIN other_table ON ins.ident = other_table.ident;')

cursor = conn.cursor()

extras.execute_values(cursor, query, x)

When I run this I get the error: column "a" is of type json but expression is of type text. I tried to solve this by adding a type cast in the SELECT statement but then I got the same error for c and then for b.

Originally I thought the problem lies in the WITH statement but based on the answers to my previous question this seems to not be the case Postgres `WITH ins AS ...` casting everything as text

It seems that execute_values is sending all the values as text with ' '.

Main Question: How can I get execute_values to send the values based on their python data type rather than just as text?

Sub questions:
How can I confirm that execute_values is in fact sending the values as text with quotation marks?
What is the purpose of the template argument of execute_values https://www.psycopg.org/docs/extras.html and could that be of help?

Answer 1

The issue, as Adrian Klaver points out in their comment, and also seen in this answer, is that the typing is lost in the CTE.

We can show this with an example in the psql shell:

CREATE TABLE test (col1 json);

WITH cte (c) AS (VALUES ('{"a": 1}'))
INSERT INTO test (col) SELECT c FROM cte; 

resulting in

ERROR:  column "col" is of type json but expression is of type text

whereas this version, with the type specified, succeeds:

WITH cte(c)  AS  (VALUES ('{"a": 1}'::json))
INSERT INTO test (col) SELECT c FROM cte;

We can mimic this in execute_valuesby providing the typing information in the template argument:

extras.execute_values(cursor, query, data, template='(%s::json, %s, %s, %s)')