På torsdag 04. februar 2016 kl. 00:09:41, skrev drum.lu...@gmail.com < drum.lu...@gmail.com <mailto:drum.lu...@gmail.com>>:
On 4 February 2016 at 12:03, David G. Johnston <david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> wrote: On Wed, Feb 3, 2016 at 3:48 PM, drum.lu...@gmail.com <mailto:drum.lu...@gmail.com> <drum.lu...@gmail.com <mailto:drum.lu...@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 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@[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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
