Re: Fatal error when not numeric value - PostgreSQL 9.2

On 02/04/2016 01:35 PM, drum.lucas@gmail.com wrote:
>
>         On 4 February 2016 at 12:03, David G. Johnston
>         <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>
>         <mailto:david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>>> wrote:
>
>              On Wed, Feb 3, 2016 at 3:48 PM, drum.lucas@gmail.com
>         <mailto:drum.lucas@gmail.com>
>              <mailto:drum.lucas@gmail.com <mailto:drum.lucas@gmail.com>>
>         <drum.lucas@gmail.com <mailto:drum.lucas@gmail.com>
>              <mailto:drum.lucas@gmail.com
>         <mailto:drum.lucas@gmail.com>>>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|
>         <mailto:adrian.klaver@aklaver.com>
>

>
> I believe that's what culling out the record from being
> inserted/updated, it's the prepass before safedatainjector. There is no
> update persay. It selects records that it thinks it needs to either
> insert/update/delete defined by tt_matched with some php logic that then
> splits the collections.

So at some point a database table is being INSERTed or UPDATEd into and
given this:

test=> \d tbl_a
      Table "public.tbl_a"
  Column |  Type   | Modifiers
--------+---------+-----------
  fld_1  | integer |


test=> insert into tbl_a values ('fe88ff8f-6b4d-4e3d-8020-3475a101d25e');
ERROR:  invalid input syntax for integer:
"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"

I would say the code is trying to put a non-integer value into a table
field that is an integer. So you need to look at the table that is being
operated on and see if the PHP code is correctly matching the fields.
When I have run into this it is because of an indexing issue, usually
caused by either a change to the table schema or a one off error in the
indexing code.


--
Adrian Klaver
adrian.klaver@aklaver.com