Re: Fatal error when not numeric value - PostgreSQL 9.2
От | Adrian Klaver |
---|---|
Тема | Re: Fatal error when not numeric value - PostgreSQL 9.2 |
Дата | |
Msg-id | 56B2AC63.50305@aklaver.com обсуждение исходный текст |
Ответ на | Re: Fatal error when not numeric value - PostgreSQL 9.2 ("drum.lucas@gmail.com" <drum.lucas@gmail.com>) |
Ответы |
Re: Fatal error when not numeric value - PostgreSQL 9.2
("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
|
Список | pgsql-general |
On 02/03/2016 03:09 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>> wrote: > > On Wed, Feb 3, 2016 at 3:48 PM, drum.lucas@gmail.com > <mailto:drum.lucas@gmail.com> <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| > > > Sorry about the missing data. > If you need something else let me know. -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: John R PierceДата:
Сообщение: Re: Fatal error when not numeric value - PostgreSQL 9.2