Fatal error when not numeric value - PostgreSQL 9.2
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
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.
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
LucasYou 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_staysFROM
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.
På torsdag 04. februar 2016 kl. 00:09:41, skrev drum.lucas@gmail.com <
drum.lucas@gmail.com <mailto:drum.lucas@gmail.com>>:
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 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 andreas@visena.com <mailto:andreas@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
Position: 428
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
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.
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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, _iidFROM(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,_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'),)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 ASirWell 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|
<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.
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, _iidFROM(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 ASirWell 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general