SQL 错误 [22P02] 错误：整数的无效输入语法：“”；

ERROR [22P02] ERROR: invalid input syntax for integer: "";

提问by Wine Too

Never seen such error:

从未见过这样的错误：

ERROR [22P02] ERROR: invalid input syntax for integer: ""; Error while executing the query

错误 [22P02] 错误：整数的无效输入语法：“”；执行查询时出错

Creating table:

创建表：

    Public Function PrimkCreate(ByVal myPrimkTable As String, ByVal nCon As OdbcConnection) As Integer
    Dim ans As Integer
    Dim cCommand As OdbcCommand = New OdbcCommand("CREATE TABLE IF NOT EXISTS " + myPrimkTable + "(" & _
            "prm_id int NOT NULL, " & _
            "pkni text, " & _
            "pdatum text, " & _
            "pdatumnaplate text, " & _
            "pdanaodgode int, " & _
            "puldok text, " & _
            "puldokbroj text, " & _
            "pdatumk text, " & _
            "pvrijemek text, " & _
            "pdobid int, " & _
            "pdoboib text, " & _
            "pnabc double precision, " & _
            "purab double precision, " & _
            "ppdv double precision, " & _
            "ppnak double precision, " & _
            "pprodc double precision, " & _
            "pvrstaprimke int, " & _
            "pzapisniktekst text, " & _
            "prez text, " & _
            "CONSTRAINT " & myPrimkTable & "_pkey PRIMARY KEY(prm_id))", nCon)

    ans = cCommand.ExecuteNonQuery()
    cCommand.Dispose()
    Return ans
End Function

Update code:

更新代码：

    Public Function update_LPrimk(ByRef primk As Integer, ByVal mCon As OdbcConnection) As Integer

    Dim retval As Integer
    Dim uCmd As OdbcCommand = New OdbcCommand("UPDATE " & myPrimkTable & " SET " & _
                "prm_id=" & primk & ", " & _
                "pkni='" & prm.pKni & "', " & _
                "pdatum='" & prm.pDatum & "', " & _
                "pdatumnaplate='" & prm.pDatumNaplate & "', " & _
                "pdanaodgode=" & prm.pDanaodgode & ", " & _
                "puldok='" & prm.pUlDok & "', " & _
                "puldokbroj='" & prm.pUlDokBroj & "', " & _
                "pdatumk='" & prm.pDatumk & "', " & _
                "pvrijemek='" & prm.pVrijemek & "', " & _
                "pdobid='" & prm.pDobID & "', " & _
                "pdoboib='" & prm.pDobOib & "', " & _
                "pnabc='" & Replace(prm.pNabc.ToString, ",", ".") & "', " & _
                "purab='" & Replace(prm.pURab.ToString, ",", ".") & "', " & _
                "ppdv='" & Replace(prm.pPdv.ToString, ",", ".") & "', " & _
                "ppnak='" & Replace(prm.pPnak.ToString, ",", ".") & "', " & _
                "pprodc='" & Replace(prm.pProdc.ToString, ",", ".") & "', " & _
                "pvrstaprimke=" & prm.pVrstaPrimke & ", " & _
                "pzapisniktekst='" & prm.pZapisnikTekst & "', " & _
                "prez='" & prm.pRez & "' " & _
                "WHERE prm_id=" + primk.ToString, mCon)

    retval = uCmd.ExecuteNonQuery()
    uCmd.Dispose()
    Return retval
End Function

Query looks exactly like this:

查询看起来完全像这样：

UPDATE primke SET prm_id=1, pkni='U', pdatum='07.01.2013', pdatumnaplate='10.01.2013',
pdanaodgode=3, puldok='ghkzug gugug', puldokbroj='jkhk', pdatumk='', pvrijemek='', 
pdobid='', pdoboib='', pnabc='0', purab='0', ppdv='0', ppnak='0', pprodc='0', 
pvrstaprimke=0, pzapisniktekst='', prez='' WHERE prm_id=1

I have many tables where I run similar commands but have never seen such an error.
What might be the problem?

我有很多表在其中运行类似的命令，但从未见过这样的错误。
可能是什么问题？

采纳答案by Erwin Brandstetter

I would advice to read the chapter Constantsin the manual. It's a brief and informative read.
The cause for the error message is that ''is an empty stringthat has no representation in a numeric type like integer.

我建议阅读手册中的常量一章。这是一个简短而翔实的读物。
错误消息的原因''是一个空字符串，没有像integer.

@a_horse_with_no_name: To be precise, '0'is a stringconstant to PostgreSQL that can be cast to integerjust as well as it can be cast to text, only textis the default for string constants. Consider this demo:

@a_horse_with_no_name：准确地说，'0'是PostgreSQL 的一个字符串常量，它可以被强制转换为 ，integer就像它可以被强制转换为 一样text，只是text字符串常量的默认值。考虑这个演示：

CREATE TEMP TABLE t (i int);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES ('2');        -- single row inserts to make sure ..
INSERT INTO t VALUES ('3'::int);   -- .. type is not coerced to type
INSERT INTO t VALUES (4::bigint);  -- .. of first row by VALUES expression.
INSERT INTO t VALUES (5::numeric);
INSERT INTO t VALUES (6);

UPDATE t SET i = '0' WHERE i = '6';
SELECT * FROM t;

SQL Fiddle.

SQL小提琴。