# CREATE TABLE foo ( id serial, val integer );
CREATE TABLE
# INSERT INTO foo (val) VALUES ('1'), (2);
INSERT 0 2
# SELECT * FROM foo WHERE id='1';
id | val
----+-----
1 | 1
(1 row)
在这里,在插入和选择时,postgres都会将带引号的字符串隐式转换为整数类型,而不是引发类型错误,除非带引号的值非常明确地键入为varchar:
# INSERT INTO foo (val) VALUES (varchar '1');
ERROR: column "val" is of type integer
but expression is of type character varying
LINE 1: INSERT INTO foo (val) VALUES (varchar '1');
^
HINT: You will need to rewrite or cast the expression.
这里的问题是针对没有隐式转换的动态类型语言(例如Ruby或Python)
有没有一种方法可以禁用它,并强制将引用的值始终为varchars(除非显式转换)?
编辑:由于人们显然专注于无关紧要的查询,因此这些查询来自参数化语句,psycopg2会将字符串转换为带引号的值,并将带引号的值转换回字符串,因此无论访问方法如何,这种不匹配都存在,这是一个红色鲱鱼。这与参数化语句完全相同:
import psycopg2.extensions
with psycopg2.connect(dbname='postgres') as cn:
cn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
with cn.cursor() as cx:
cx.execute("DROP DATABASE IF EXISTS test")
cx.execute("CREATE DATABASE test")
with psycopg2.connect(dbname='test') as cn:
with cn.cursor() as cx:
cx.execute("CREATE TABLE foo ( id serial, val integer )")
cx.execute("INSERT INTO foo (val) VALUES (%s), (%s)",
(1, '2'))
cx.execute("SELECT * FROM foo WHERE id=%s",
('1',))
print cx.fetchall()
输出:
[(1, 1)]
不,您不能禁用带引号的文字到任何目标类型的隐式转换。PostgreSQL认为此类文字是unknown
类型,除非被强制转换或文字类型说明符覆盖,并且会从转换unknown
为任何类型。没有unknown
类型的转换pg_cast
。它是隐式的。所以你不能丢它。
据我所知,PostgreSQL通过接受带引号的文字作为整数来遵循SQL规范。
对于PostgreSQL的类型引擎,1
是一个integer
,并且'1'
是一个unknown
类型,integer
如果将其传递给integer
函数,运算符或字段,则将其类型推断。如果不直接修改解析器/查询计划器,则无法禁用类型推断unknown
或unknown
将其视为强制类型text
。
您应该做的是使用参数化语句,而不是将文字替换为SQL。如果这样做,则不会有此问题,因为客户端类型是已知的或可以指定的。那当然适用于Python(psycopg2)和Ruby(Pg gem) 我对psycopg2的想法不起作用,请参见下文。
问题澄清后进行更新:在此处描述的狭义情况下,psycopg2的客户端参数化语句虽然正确,但不会产生原始发布者想要的结果。在更新中运行演示表明,psycopg2没有使用PostgreSQL的v3绑定/执行协议,而是在使用简单的查询协议并在本地进行参数替换。因此,尽管在Python中使用参数化语句,但在PostgreSQL中却没有使用参数化语句。上面我在说psycopg2中的参数化语句可以解决此问题时犯了错误。
该演示从PostgreSQL日志中运行以下SQL:
< 2014-07-07 18:17:24.450 WST >LOG: statement: INSERT INTO foo (val) VALUES (1), ('2')
< 2014-07-07 18:17:24.451 WST >LOG: statement: SELECT * FROM foo WHERE id='1'
请注意缺少放置参数。它们被替换为客户端。
因此,如果您想psycopg2
变得更严格,则必须调整客户端框架。
psycopg2
是可扩展的,所以应该是相当实用-你需要重写式处理程序str
,unicode
并且integer
(或者,在Python3, bytes
,str
和integer
)使用psycopg2.extras
,每适应新的类型。甚至还有一个有关覆盖psycopg2处理float
示例的FAQ条目:http : //initd.org/psycopg/docs/faq.html#faq-float
但是,由于无限递归,幼稚的方法将无法工作:
def adapt_str_strict(thestr):
return psycopg2.extensions.AsIs('TEXT ' + psycopg2.extensions.adapt(thestr))
psycopg2.extensions.register_adapter(str, adapt_str_strict)
因此您需要绕过类型适配器注册,以调用的原始基础适配器str
。这会很丑陋:
def adapt_str_strict(thestr):
return psycopg2.extensions.AsIs('TEXT ' + str(psycopg2.extensions.QuotedString(thestr)))
psycopg2.extensions.register_adapter(str, adapt_str_strict)
以此运行演示,您将获得:
psycopg2.ProgrammingError: parameter $1 of type text cannot be coerced to the expected type integer
HINT: You will need to rewrite or cast the expression.
(顺便说一句,使用服务器端PREPARE
和EXECUTE
将无法正常工作,因为你刚刚遭受同样的打字问题传递值时EXECUTE
通过psycopg2
)。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句