Re: Fatal error when not numeric value - PostgreSQL 9.2

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Wed, 3 Feb 2016 17:41:55 -0800

On 02/03/2016 03:09 PM, drum.lucas@xxxxxxxxx wrote:

On 4 February 2016 at 12:03, David G. Johnston
<david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>> wrote:

    On Wed, Feb 3, 2016 at 3:48 PM, drum.lucas@xxxxxxxxx
    <mailto:drum.lucas@xxxxxxxxx> <drum.lucas@xxxxxxxxx
    <mailto:drum.lucas@xxxxxxxxx>>wrote:

        Hi all,

        Below is an example of the auto-generated update query, with
        client-supplied keys (_iid). There's a fatal error when _iid is
        not numeric. However; this should accept any value.

        *Question:* How could I do something that would allow _iid to be
        more than just an INT?

        |WITH    in_rows AS(SELECT        CAST(customer_id ASBIGINT),
             csv_data,        freshbooks_id,        myob_id,
        ppy_id,        qb_id,        xero_id,        _iid
        FROM(VALUES('3905',E'\x1A',E'\x1A','c59894c-142b6',E'\x1A',E'\x1A',E'\x1A','44'),('39107',E'\x1A',E'\x1A','6260-2ba1',E'\x1A',E'\x1A',E'\x1A','65e-0f0d-49b4-9ac1-a8752ba1'),|

        |Thank you|
        |Lucas|

    You have a fatal error because the query you provided is
    malformed.  Send something that works, and provokes the relevant
    error, and we might be able to help.

    David J.

QUERY:

|WITHin_rows AS(SELECTCAST(customer_id
ASBIGINT),csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid
FROM(VALUES('3915105',E'\x1A',E'\x1A','c59894cb-0ffe-4ad6-823d-73c1392142b6',E'\x1A',E'\x1A',E'\x1A','44'),('3915135',E'\x1A',E'\x1A','fe88ff8f-6b4d-4e3d-8020-3475a101d25e',E'\x1A',E'\x1A',E'\x1A','fe88ff8f-6b4d-4e3d-8020-3475a101d25e'),)ASid(customer_id,csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid)),id_overlays
AS(SELECTir.customer_id,(tt.customer_id
ISNOTNULL)AStt_matched,(CASEWHEN(ir.csv_data ::TEXT
=E'\x1A')THENtt.csv_data ::TEXT ELSENULLIF(ir.csv_data
::TEXT,E'\x18')END)AScsv_data,(CASEWHEN(ir.freshbooks_id ::TEXT
=E'\x1A')THENtt.freshbooks_id ::TEXT ELSENULLIF(ir.freshbooks_id
::TEXT,E'\x18')END)ASfreshbooks_id,(CASEWHEN(ir.myob_id ::TEXT
=E'\x1A')THENtt.myob_id ::TEXT ELSENULLIF(ir.myob_id
::TEXT,E'\x18')END)ASmyob_id,(CASEWHEN(ir.ppy_id ::TEXT
=E'\x1A')THENtt.ppy_id ::TEXT ELSENULLIF(ir.ppy_id
::TEXT,E'\x18')END)ASppy_id,(CASEWHEN(ir.qb_id ::TEXT
=E'\x1A')THENtt.qb_id ::TEXT ELSENULLIF(ir.qb_id
::TEXT,E'\x18')END)ASqb_id,(CASEWHEN(ir.xero_id ::TEXT
=E'\x1A')THENtt.xero_id ::TEXT ELSENULLIF(ir.xero_id
::TEXT,E'\x18')END)ASxero_id,ir._iid ::TEXT AS_iid FROMin_rows ASir

Well above you are turning _iid into ::TEXT which is fine, though(as 
David pointed out) the receiving field should be that type also. So the 
problem is not your casting of _iid it is where you are sending that value.

LEFTJOINintegrations.customers AStt
USING(customer_id))SELECTio.customer_id,io._iid,io.tt_matched,((io.csv_data
ISNOTNULL)OR(io.freshbooks_id ISNOTNULL)OR(io.myob_id
ISNOTNULL)OR(io.ppy_id ISNOTNULL)OR(io.qb_id ISNOTNULL)OR(io.xero_id
ISNOTNULL))AStt_stays FROMid_overlays ASio;|

ERROR:

|ERROR:invalid input syntax
forinteger:"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"at character 419|

Sorry about the missing data.
If you need something else let me know.

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general