Postgres `WITH ins AS ...` casting everything 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, data)

When I run this as is 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

         'SELECT '
            'ins.a::json '
            'ins.b '
            'ins.c '
            'other_table.id'

but then I get the error column "c" is of type text[] but expression is of type text

So I fixed that in the same way:

         'SELECT '
            'ins.a::json '
            'ins.b '
            'ins.c::text[]'
            'other_table.id'

so now I am getting the error column "b" is of type integer but expression is of type text

This example is somewhat simplified as I have many more columns in the original query.

1. Does the WITH ins ... statement always convert everything to text? This seems like an odd behavior to me
2. Is there a way to code this without having to manually typecast every column? This seems inelegant and computationally inefficient data gets converted eg. from python int to postgres text to postgres integer.

Answer 1

The problem isn't with the CTE, but with how you're passing values into the VALUES clause. Somehow all values created inside of the CTE at VALUES are being sent as text (perhaps the query is created with all values between single quotes?). The following example reproduces your query with pure SQL statements, and it works as it should:

WITH ins (a, b, c, id) AS (
  VALUES ('{"answer":42}'::json,42,array['foo','bar'],1)
) 
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.id = other_table.id;

Note that I cast the json the value inside of the CTE, not in the SELECT clause. So, if the origin is correct, there is no way that postgres will cast it to text without you telling it to do so ;)

Demo: db<>fiddle