在INSERT语句中使用类型转换和条件插入表 [英] Insert into table with type casting and condition in INSERT statement

问题描述

基于上一个问题

我正在尝试使用带有某些数据的psycopg2在Python中编写一个插入语句。该查询在Postgresql控制台中可以正常工作，但是当我从psycopg2尝试该查询时，出现错误。

I am trying to compose an insert statement in Python using psycopg2 with some data. The query works beautifully in postgresql console but when I try it from psycopg2 I get an error.

这是我要插入的表

create_test_table = """CREATE TABLE persons(
   name           VARCHAR,
   age            INT,
   dob            DATE,
   sex            VARCHAR);"""

这是我要插入的数据

d_ = {名称： Gino， age：， dob： na， sex： M}

这是我的查询：

cur.execute("""INSERT INTO persons (age,dob,sex,name) 
               VALUES (CASE WHEN cast(%(age)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(age)s END,
                       CASE WHEN cast(%(dob)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(dob)s END,
                       CASE WHEN cast(%(sex)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(sex)s END,
                       CASE WHEN cast(%(name)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(name)s END)""", d_)

错误消息：

----> 5  CASE WHEN cast(%(name)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(name)s END)""", d_)

ProgrammingError: column "age" is of type integer but expression is of type text
LINE 2:                VALUES (CASE WHEN cast('' as text) IN ('', '#...
                               ^
HINT:  You will need to rewrite or cast the expression.

也许无法在psycopg2中执行此操作。任何帮助将不胜感激：））

Maybe it is not possible to do this in psycopg2. Any help would be appreciated :))

推荐答案

该错误是由于 CASE 表达式（即您要插入的值）的结果是TEXT，而 age 列为INTEGER。

The error is due to the result of the CASE expression (i.e. the value you're trying to insert) being TEXT, whereas the age column is INTEGER.

要直接解决该错误，只需将表达式转换为 INTEGER ：

To address the error directly, you could simply cast the expression to INTEGER:

CASE WHEN cast(%(age)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(age)s END::INTEGER

或

CAST(CASE WHEN cast(%(age)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(age)s END AS INTEGER)

但是，除非当该字段是其他字段时您实际上想要一个无效的强制转换例外而不是整数，而不是您专门排除的（''，'＃'，'-'，'-'，'??'）值之一，您可能希望将以下特定语句重写为：

However, unless you actually want an invalid cast exception when that field is anything other than an integer, and not one of the ('', '#', '-', '--', '??') values you're specifically excluding, you might want to rewrite this particular statement as:

CASE WHEN age::TEXT ~ '^[\d]+$' THEN age ELSE NULL END::INTEGER

因此在尝试投射之前，请先检查它是否为数字它，如果不是数字，则将其为NULL。但是无论哪种方式，您只需要强制转换结果即可。请注意，您可能必须对 dob 列执行相同的操作。

So check that's it actually a number before attempting to cast it, and if it's not a number then NULL it. But either way, you just need to cast the result. Note you may have to do the same for the dob column.

这篇关于在INSERT语句中使用类型转换和条件插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！