Re: Fatal error when not numeric value - PostgreSQL 9.2

On 4 February 2016 at 12:03, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wed, Feb 3, 2016 at 3:48 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)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 AS BIGINT),
>> 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:

WITH
in_rows AS (
SELECT
CAST(customer_id AS BIGINT),
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'),
) AS id(customer_id, csv_data, freshbooks_id, myob_id, ppy_id,
qb_id, xero_id, _iid)
),
id_overlays AS (
SELECT
ir.customer_id,
(tt.customer_id IS NOT NULL) AS tt_matched,
(CASE WHEN (ir.csv_data :: TEXT = E'\x1A')
THEN tt.csv_data :: TEXT
ELSE NULLIF(ir.csv_data :: TEXT, E'\x18') END) AS csv_data,
(CASE WHEN (ir.freshbooks_id :: TEXT = E'\x1A')
THEN tt.freshbooks_id :: TEXT
ELSE NULLIF(ir.freshbooks_id :: TEXT, E'\x18') END) AS freshbooks_id,
(CASE WHEN (ir.myob_id :: TEXT = E'\x1A')
THEN tt.myob_id :: TEXT
ELSE NULLIF(ir.myob_id :: TEXT, E'\x18') END) AS myob_id,
(CASE WHEN (ir.ppy_id :: TEXT = E'\x1A')
THEN tt.ppy_id :: TEXT
ELSE NULLIF(ir.ppy_id :: TEXT, E'\x18') END) AS ppy_id,
(CASE WHEN (ir.qb_id :: TEXT = E'\x1A')
THEN tt.qb_id :: TEXT
ELSE NULLIF(ir.qb_id :: TEXT, E'\x18') END) AS qb_id,
(CASE WHEN (ir.xero_id :: TEXT = E'\x1A')
THEN tt.xero_id :: TEXT
ELSE NULLIF(ir.xero_id :: TEXT, E'\x18') END) AS xero_id,
ir._iid :: TEXT AS _iid
FROM
in_rows AS ir LEFT JOIN integrations.customers AS tt USING (customer_id)
)SELECT
io.customer_id,
io._iid,
io.tt_matched,
((io.csv_data IS NOT NULL) OR (io.freshbooks_id IS NOT NULL) OR
(io.myob_id IS NOT NULL) OR (io.ppy_id IS NOT NULL) OR
(io.qb_id IS NOT NULL) OR (io.xero_id IS NOT NULL)) AS tt_staysFROM
id_overlays AS io;

ERROR:

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

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