Re: Fatal error when not numeric value - PostgreSQL 9.2 - Mailing list pgsql-general

From Andreas Joseph Krogh
Subject Re: Fatal error when not numeric value - PostgreSQL 9.2
Date
Msg-id VisenaEmail.a1.3169012b644e621f.152a978006b@tc7-visena
Whole thread Raw
In response to Re: Fatal error when not numeric value - PostgreSQL 9.2  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
Responses Re: Fatal error when not numeric value - PostgreSQL 9.2
List pgsql-general
På torsdag 04. februar 2016 kl. 00:09:41, skrev drum.lucas@gmail.com <drum.lucas@gmail.com>:
 
 
On 4 February 2016 at 12:03, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wed, Feb 3, 2016 at 3:48 PM, drum.lucas@gmail.com <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 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_stays
FROM  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.
 
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,    _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);
 
 
$ 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
 
Attachment

pgsql-general by date:

Previous
From: "drum.lucas@gmail.com"
Date:
Subject: Re: Fatal error when not numeric value - PostgreSQL 9.2
Next
From: "David G. Johnston"
Date:
Subject: Re: Fatal error when not numeric value - PostgreSQL 9.2