Thread: Fatal error when not numeric value - PostgreSQL 9.2

Fatal error when not numeric value - PostgreSQL 9.2

From

"drum.lucas@gmail.com"

Date:

04 February 2016, 01:48:50

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

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

"David G. Johnston"

Date:

04 February 2016, 02:03:33

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.

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

"drum.lucas@gmail.com"

Date:

04 February 2016, 02:10:27

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.

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

Andreas Joseph Krogh

Date:

04 February 2016, 02:33:12

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

andreas@visena.com

www.visena.com

Attachment

msg-1549957-3.png

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

"David G. Johnston"

Date:

04 February 2016, 02:43:28

On Wed, Feb 3, 2016 at 4:09 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:

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.

The supplied query is not an update query and as far as I can tell nothing in it compels the system to try and convert your text _iid from the CTE to an integer. So, you are still not showing us the whole picture. But, if whatever target relation you are trying to insert/update this record against defines _iid as integer then you need to change it to be defined as text.

David J.

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

John R Pierce

Date:

04 February 2016, 02:43:56

On 2/3/2016 3:32 PM, Andreas Joseph Krogh wrote:

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);

it works on 9.2 for me here, as well as 9.3, 9.4

-- 
john r pierce, recycling bits in santa cruz

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

Adrian Klaver

Date:

04 February 2016, 04:43:04

On 02/03/2016 03:09 PM, drum.lucas@gmail.com wrote:
>
>
> On 4 February 2016 at 12:03, David G. Johnston
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
>     On Wed, Feb 3, 2016 at 3:48 PM, drum.lucas@gmail.com
>     <mailto:drum.lucas@gmail.com> <drum.lucas@gmail.com
>     <mailto: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 ASBIGINT),
>              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:
>
> |WITHin_rows AS(SELECTCAST(customer_id
> ASBIGINT),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'),)ASid(customer_id,csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid)),id_overlays
> AS(SELECTir.customer_id,(tt.customer_id
> ISNOTNULL)AStt_matched,(CASEWHEN(ir.csv_data ::TEXT
> =E'\x1A')THENtt.csv_data ::TEXT ELSENULLIF(ir.csv_data
> ::TEXT,E'\x18')END)AScsv_data,(CASEWHEN(ir.freshbooks_id ::TEXT
> =E'\x1A')THENtt.freshbooks_id ::TEXT ELSENULLIF(ir.freshbooks_id
> ::TEXT,E'\x18')END)ASfreshbooks_id,(CASEWHEN(ir.myob_id ::TEXT
> =E'\x1A')THENtt.myob_id ::TEXT ELSENULLIF(ir.myob_id
> ::TEXT,E'\x18')END)ASmyob_id,(CASEWHEN(ir.ppy_id ::TEXT
> =E'\x1A')THENtt.ppy_id ::TEXT ELSENULLIF(ir.ppy_id
> ::TEXT,E'\x18')END)ASppy_id,(CASEWHEN(ir.qb_id ::TEXT
> =E'\x1A')THENtt.qb_id ::TEXT ELSENULLIF(ir.qb_id
> ::TEXT,E'\x18')END)ASqb_id,(CASEWHEN(ir.xero_id ::TEXT
> =E'\x1A')THENtt.xero_id ::TEXT ELSENULLIF(ir.xero_id
> ::TEXT,E'\x18')END)ASxero_id,ir._iid ::TEXT AS_iid FROMin_rows ASir

Well above you are turning _iid into ::TEXT which is fine, though(as
David pointed out) the receiving field should be that type also. So the
problem is not your casting of _iid it is where you are sending that value.

> LEFTJOINintegrations.customers AStt
> USING(customer_id))SELECTio.customer_id,io._iid,io.tt_matched,((io.csv_data
> ISNOTNULL)OR(io.freshbooks_id ISNOTNULL)OR(io.myob_id
> ISNOTNULL)OR(io.ppy_id ISNOTNULL)OR(io.qb_id ISNOTNULL)OR(io.xero_id
> ISNOTNULL))AStt_stays FROMid_overlays ASio;|
>
> ERROR:
>
> |ERROR:invalid input syntax
> forinteger:"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"at character 419|
>
>
> Sorry about the missing data.
> If you need something else let me know.


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

"drum.lucas@gmail.com"

Date:

05 February 2016, 00:35:57

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

On Wed, Feb 3, 2016 at 3:48 PM, drum.lucas@gmail.com
<mailto:drum.lucas@gmail.com> <drum.lucas@gmail.com
<mailto: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 ASBIGINT),
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:

|WITHin_rows AS(SELECTCAST(customer_id
ASBIGINT),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'),)ASid(customer_id,csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid)),id_overlays
AS(SELECTir.customer_id,(tt.customer_id
ISNOTNULL)AStt_matched,(CASEWHEN(ir.csv_data ::TEXT
=E'\x1A')THENtt.csv_data ::TEXT ELSENULLIF(ir.csv_data
::TEXT,E'\x18')END)AScsv_data,(CASEWHEN(ir.freshbooks_id ::TEXT
=E'\x1A')THENtt.freshbooks_id ::TEXT ELSENULLIF(ir.freshbooks_id
::TEXT,E'\x18')END)ASfreshbooks_id,(CASEWHEN(ir.myob_id ::TEXT
=E'\x1A')THENtt.myob_id ::TEXT ELSENULLIF(ir.myob_id
::TEXT,E'\x18')END)ASmyob_id,(CASEWHEN(ir.ppy_id ::TEXT
=E'\x1A')THENtt.ppy_id ::TEXT ELSENULLIF(ir.ppy_id
::TEXT,E'\x18')END)ASppy_id,(CASEWHEN(ir.qb_id ::TEXT
=E'\x1A')THENtt.qb_id ::TEXT ELSENULLIF(ir.qb_id
::TEXT,E'\x18')END)ASqb_id,(CASEWHEN(ir.xero_id ::TEXT
=E'\x1A')THENtt.xero_id ::TEXT ELSENULLIF(ir.xero_id
::TEXT,E'\x18')END)ASxero_id,ir._iid ::TEXT AS_iid FROMin_rows ASir

Well above you are turning _iid into ::TEXT which is fine, though(as David pointed out) the receiving field should be that type also. So the problem is not your casting of _iid it is where you are sending that value.

LEFTJOINintegrations.customers AStt
USING(customer_id))SELECTio.customer_id,io._iid,io.tt_matched,((io.csv_data
ISNOTNULL)OR(io.freshbooks_id ISNOTNULL)OR(io.myob_id
ISNOTNULL)OR(io.ppy_id ISNOTNULL)OR(io.qb_id ISNOTNULL)OR(io.xero_id
ISNOTNULL))AStt_stays FROMid_overlays ASio;|

ERROR:

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

I believe that's what culling out the record from being inserted/updated, it's the prepass before safedatainjector. There is no update persay. It selects records that it thinks it needs to either insert/update/delete defined by tt_matched with some php logic that then splits the collections.

Re: Fatal error when not numeric value - PostgreSQL 9.2

From

Adrian Klaver

Date:

05 February 2016, 00:52:15

On 02/04/2016 01:35 PM, drum.lucas@gmail.com wrote:
>
>         On 4 February 2016 at 12:03, David G. Johnston
>         <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>
>         <mailto:david.g.johnston@gmail.com
>         <mailto:david.g.johnston@gmail.com>>> wrote:
>
>              On Wed, Feb 3, 2016 at 3:48 PM, drum.lucas@gmail.com
>         <mailto:drum.lucas@gmail.com>
>              <mailto:drum.lucas@gmail.com <mailto:drum.lucas@gmail.com>>
>         <drum.lucas@gmail.com <mailto:drum.lucas@gmail.com>
>              <mailto:drum.lucas@gmail.com
>         <mailto: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
>         ASBIGINT),
>                       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:
>
>         |WITHin_rows AS(SELECTCAST(customer_id
>         ASBIGINT),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'),)ASid(customer_id,csv_data,freshbooks_id,myob_id,ppy_id,qb_id,xero_id,_iid)),id_overlays
>         AS(SELECTir.customer_id,(tt.customer_id
>         ISNOTNULL)AStt_matched,(CASEWHEN(ir.csv_data ::TEXT
>         =E'\x1A')THENtt.csv_data ::TEXT ELSENULLIF(ir.csv_data
>         ::TEXT,E'\x18')END)AScsv_data,(CASEWHEN(ir.freshbooks_id ::TEXT
>         =E'\x1A')THENtt.freshbooks_id ::TEXT ELSENULLIF(ir.freshbooks_id
>         ::TEXT,E'\x18')END)ASfreshbooks_id,(CASEWHEN(ir.myob_id ::TEXT
>         =E'\x1A')THENtt.myob_id ::TEXT ELSENULLIF(ir.myob_id
>         ::TEXT,E'\x18')END)ASmyob_id,(CASEWHEN(ir.ppy_id ::TEXT
>         =E'\x1A')THENtt.ppy_id ::TEXT ELSENULLIF(ir.ppy_id
>         ::TEXT,E'\x18')END)ASppy_id,(CASEWHEN(ir.qb_id ::TEXT
>         =E'\x1A')THENtt.qb_id ::TEXT ELSENULLIF(ir.qb_id
>         ::TEXT,E'\x18')END)ASqb_id,(CASEWHEN(ir.xero_id ::TEXT
>         =E'\x1A')THENtt.xero_id ::TEXT ELSENULLIF(ir.xero_id
>         ::TEXT,E'\x18')END)ASxero_id,ir._iid ::TEXT AS_iid FROMin_rows ASir
>
>
>     Well above you are turning _iid into ::TEXT which is fine, though(as
>     David pointed out) the receiving field should be that type also. So
>     the problem is not your casting of _iid it is where you are sending
>     that value.
>
>         LEFTJOINintegrations.customers AStt
>         USING(customer_id))SELECTio.customer_id,io._iid,io.tt_matched,((io.csv_data
>         ISNOTNULL)OR(io.freshbooks_id ISNOTNULL)OR(io.myob_id
>         ISNOTNULL)OR(io.ppy_id ISNOTNULL)OR(io.qb_id ISNOTNULL)OR(io.xero_id
>         ISNOTNULL))AStt_stays FROMid_overlays ASio;|
>
>         ERROR:
>
>         |ERROR:invalid input syntax
>         forinteger:"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"at character 419|
>         <mailto:adrian.klaver@aklaver.com>
>

>
> I believe that's what culling out the record from being
> inserted/updated, it's the prepass before safedatainjector. There is no
> update persay. It selects records that it thinks it needs to either
> insert/update/delete defined by tt_matched with some php logic that then
> splits the collections.

So at some point a database table is being INSERTed or UPDATEd into and
given this:

test=> \d tbl_a
      Table "public.tbl_a"
  Column |  Type   | Modifiers
--------+---------+-----------
  fld_1  | integer |


test=> insert into tbl_a values ('fe88ff8f-6b4d-4e3d-8020-3475a101d25e');
ERROR:  invalid input syntax for integer:
"fe88ff8f-6b4d-4e3d-8020-3475a101d25e"

I would say the code is trying to put a non-integer value into a table
field that is an integer. So you need to look at the table that is being
operated on and see if the PHP code is correctly matching the fields.
When I have run into this it is because of an indexing issue, usually
caused by either a change to the table schema or a one off error in the
indexing code.


--
Adrian Klaver
adrian.klaver@aklaver.com