Re: Fatal error when not numeric value - PostgreSQL 9.2 - Mailing list pgsql-general
| From | drum.lucas@gmail.com |
|---|---|
| Subject | Re: Fatal error when not numeric value - PostgreSQL 9.2 |
| Date | |
| Msg-id | CAE_gQfXass98qPwu-RtBsz_P4jvmiTL+JQcnK-HE-x_-3839Mw@mail.gmail.com Whole thread Raw |
| In response to | Re: Fatal error when not numeric value - PostgreSQL 9.2 ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: Fatal error when not numeric value - PostgreSQL 9.2
Re: Fatal error when not numeric value - PostgreSQL 9.2 Re: Fatal error when not numeric value - PostgreSQL 9.2 |
| List | pgsql-general |
On 4 February 2016 at 12:03, David G. Johnston <david.g.johnston@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 youLucasYou 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_stays
FROM id_overlays AS io;ERROR:
ERROR: invalid input syntax for integer: "fe88ff8f-6b4d-4e3d-8020-3475a101d25e" at character 419Sorry about the missing data.
If you need something else let me know.
pgsql-general by date: