On 02/04/2016 01:35 PM, drum.lucas@xxxxxxxxx wrote:
On 4 February 2016 at 12:03, David G. Johnston
<david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>
<mailto: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>
<mailto:drum.lucas@xxxxxxxxx <mailto:drum.lucas@xxxxxxxxx>>
<drum.lucas@xxxxxxxxx <mailto:drum.lucas@xxxxxxxxx>
<mailto: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|
<mailto:adrian.klaver@xxxxxxxxxxx>
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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
