Re: Fatal error when not numeric value - PostgreSQL 9.2

På torsdag 04. februar 2016 kl. 00:09:41, skrev drum(dot)lucas(at)gmail(dot)com <
drum(dot)lucas(at)gmail(dot)com <mailto:drum(dot)lucas(at)gmail(dot)com>>:
 

  On 4 February 2016 at 12:03, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com
<mailto: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 <mailto:drum(dot)lucas(at)gmail(dot)com> <drum(dot)lucas(at)gmail(dot)com
<mailto: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_idIS 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_stays FROM
id_overlaysAS 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.

 
Seems like the error-message is from the first CTE.
 
This query works for me in 9.5:
 
SELECT CAST(customer_id AS BIGINT), csv_data, freshbooks_id, myob_id, ppy_id,
qb_id, xero_id, _iidFROM ( 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);
 
 
$ psql
Line style is unicode.
psql (9.5.0)
Type "help" for help.
(andreak(at)[local]:5432) 00:28:59 [andreak]
> SELECT
andreak->     CAST(customer_id AS BIGINT),
andreak->     csv_data,
andreak->     freshbooks_id,
andreak->     myob_id,
andreak->     ppy_id,
andreak->     qb_id,
andreak->     xero_id,
andreak->     _iid
andreak-> FROM
andreak->     (
andreak(>         VALUES
andreak(>             ('3915105', E'\x1A', E'\x1A',
'c59894cb-0ffe-4ad6-823d-73c1392142b6', E'\x1A', E'\x1A', E'\x1A', '44'),
andreak(>             ('3915135', E'\x1A', E'\x1A',
'fe88ff8f-6b4d-4e3d-8020-3475a101d25e', E'\x1A', E'\x1A', E'\x1A',
'fe88ff8f-6b4d-4e3d-8020-3475a101d25e')
andreak(>     ) AS id(customer_id, csv_data, freshbooks_id, myob_id, ppy_id,
qb_id, xero_id, _iid);
 customer_id │ csv_data │ freshbooks_id │              
myob_id                │ ppy_id │ qb_id │ xero_id │                
_iid                 

─────────────┼──────────┼───────────────┼──────────────────────────────────────┼────────┼───────┼─────────┼──────────────────────────────────────
     3915105 │ \x1A     │ \x1A          │
c59894cb-0ffe-4ad6-823d-73c1392142b6 │ \x1A   │ \x1A  │ \x1A    │ 44
     3915135 │ \x1A     │ \x1A          │
fe88ff8f-6b4d-4e3d-8020-3475a101d25e │ \x1A   │ \x1A  │ \x1A    │
fe88ff8f-6b4d-4e3d-8020-3475a101d25e
(2 rows)
 

 
 
Note that you have a comma after the last paren:
 
.... 'fe88ff8f-6b4d-4e3d-8020-3475a101d25e'),
 
I had to remove that to not get this error-message:
 
[42601] ERROR: syntax error at or near ")"
  Position: 428
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>