Postgres `WITH ins AS ...` 将所有内容转换为文本 [英] Postgres `WITH ins AS ...` casting everything as text

问题描述

我在 postgres 中有这个表

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,
)

我想使用

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)

当我按原样运行时，出现错误:column "a";是 json 类型，但表达式是文本类型

When I run this as is I get the error: column "a" is of type json but expression is of type text

我试图通过在 SELECT 语句中添加类型转换来解决这个问题

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'

但后来我收到错误 column "c";是 text[] 类型，但表达式是 text 类型

所以我以同样的方式解决了这个问题:

So I fixed that in the same way:

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

所以现在我收到错误 column b";是整数类型但表达式是文本类型

这个例子有点简化，因为我在原始查询中有更多的列.

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

1. WITH ins ... 语句是否总是将所有内容转换为文本?这对我来说似乎是一种奇怪的行为
2. 有没有一种方法可以在无需手动对每一列进行类型转换的情况下对此进行编码?这似乎不优雅且计算效率低下的数据被转换，例如.从 python int 到 postgres 文本到 postgres 整数.

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.

推荐答案

问题不在于 CTE，但与您如何将值传递到 VALUES 子句中.不知何故，在 VALUES 的 CTE 内创建的所有值都作为文本发送(也许查询是用单引号之间的所有值创建的?).以下示例使用纯 SQL 语句重现了您的查询，并且它按预期工作:

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;

请注意，我将 json 转换为 CTE 内的值，而不是 SELECT 子句中的值.因此，如果来源是正确的，则 postgres 无法在没有您告知的情况下将其转换为文本 ;)

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 ;)

演示:db<>fiddle

这篇关于Postgres `WITH ins AS ...` 将所有内容转换为文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！