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

Question

Never seen such error:

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

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?

Solution

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

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

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.

Comments

[a_horse_with_no_name]

The SQL standard calls 'foobar' a character string literal :) I tend to avoid the name "string" in combination with SQL as it is not a regular datatype. And I strongly advice against using implicit datatype converstion. That is simply calling for trouble. Better to always use the proper literal (constant) syntax.

[a_horse_with_no_name]

I'm actually surprised that i = '0' works. I thought those implicit casts had been removed with 8.3

[Erwin Brandstetter]

@a_horse_with_no_name: I think what has been removed with 8.3 is automatic coercion of a numeric constant to a character type. (0 -> '0'::text). But a generic string constant ('0') can be coerced to any type.

[Erwin Brandstetter]

@a_horse_with_no_name: I think "string" is just the right term for a string constant like '0', because "string" is not a regular data type - since the literal has not been cast to any data type yet.

[a_horse_with_no_name]

OK, I see. But I still think it's bad practise to use the wrong literal "type" and rely on implicit data type conversion.

[Wine Too]

Thank you Ervin for a "brief casting course". Yes, I all time use doubles as strings and back with PostgreSQL where '0' is often input. I noted that PG won't take decimal coma which forces my locale on windows and linux too. Changing locale often on the fly don't seem's lucky solution anyway.

[Erwin Brandstetter]

@a_horse_with_no_name: Well, I agree. If you can be more specific, you should be. But it's helpful at times to have string constants (literals) that can be cast to any type.

[Erwin Brandstetter]

@user973238: You can use the function to_number() to convert a string to a numeric type, independent of locale.

[Wine Too]

Didn't know that, thanks. So I can save much of my "replace work" :)