malformed array literal in 8beta1
Some changed behavior in 8beta1 bit me here and I'm not sure what it is I am
doing wrong. This works fine on 7.4.
Chris
LOG: statement: select
insert_customers('{poi2-10000064,poi2,test1,10000064,1,MC,,John,Doe,,,123
abc
st,mytown,WA,98101,,chris@paymentonline.com,US,XFsWsbpBwQJHNK7z41QMv5bpoOLDefsljIXpaWMLeETb4DmStkfkTl1pQ2GT9Uxwe2Mip61TLOUI
y8iFwEQV7/Jz3eqvhbwAe6Bc8IQqmQOm84/rPRYRxWZ/SGhc014P7luhSQkV9FHzOJcdvxDzOCCS
4siOC1Q1wETqhfs5fe0VCcydzahhgspRWeyKyboHWq61B032CW53SD0p3tOTo0JLe+qTRA8EGnrA
Kx6eWXn98wjyHJa7gGyHNQEJK+tlgkTxZmT7BYtkYGxhPOqsbj+MsyBDOFw7vZBXEVAi8TrZ2NdQ
AvDg6XSJBG9qDa9S954afLv9L7Z922bkt55krA==
,1,4,545454,123 abc st}')
ERROR: malformed array literal:
"{poi2-10000064,poi2,test1,10000064,1,MC,,John,Doe,,,123 abc
st,mytown,WA,98101,,chris@paymentonline.com,US,X
FsWsbpBwQJHNK7z41QMv5bpoOLDefsljIXpaWMLeETb4DmStkfkTl1pQ2GT9Uxwe2Mip61TLOUI
y8iFwEQV7/Jz3eqvhbwAe6Bc8IQqmQOm84/rPRYRxWZ/SGhc014P7luhSQkV9FHzOJcdvxDzOCCS
4siOC1Q1wETqhfs5fe0VCcydzahhgspRWeyKyboHWq61B032CW53SD0p3tOTo0JLe+qTRA8EGnrA
Kx6eWXn98wjyHJa7gGyHNQEJK+tlgkTxZmT7BYtkYGxhPOqsbj+MsyBDOFw7vZBXEVAi8TrZ2NdQ
AvDg6XSJBG9qDa9S954afLv9L7Z922bkt55krA==
,1,4,545454,123 abc st}"
LOG: statement: rollback
After looking closer at the logs, what seems to be happening is that it
doesn't like the empty array values. All functions that I call with an
array that have one or more empty elements are returning this error. Should
I be setting the value to NULL instead of just leaving it empty?
Chris
----- Original Message -----
From: "Chris" <chris@paymentonline.com>
To: <pgsql-general@postgresql.org>
Sent: Sunday, August 29, 2004 2:35 PM
Subject: malformed array literal in 8beta1
Show quoted text
Some changed behavior in 8beta1 bit me here and I'm not sure what it is I
am doing wrong. This works fine on 7.4.Chris
LOG: statement: select
insert_customers('{poi2-10000064,poi2,test1,10000064,1,MC,,John,Doe,,,123
abc
st,mytown,WA,98101,,chris@paymentonline.com,US,XFsWsbpBwQJHNK7z41QMv5bpoOLDefsljIXpaWMLeETb4DmStkfkTl1pQ2GT9Uxwe2Mip61TLOUIy8iFwEQV7/Jz3eqvhbwAe6Bc8IQqmQOm84/rPRYRxWZ/SGhc014P7luhSQkV9FHzOJcdvxDzOCCS
4siOC1Q1wETqhfs5fe0VCcydzahhgspRWeyKyboHWq61B032CW53SD0p3tOTo0JLe+qTRA8EGnrA
Kx6eWXn98wjyHJa7gGyHNQEJK+tlgkTxZmT7BYtkYGxhPOqsbj+MsyBDOFw7vZBXEVAi8TrZ2NdQ
AvDg6XSJBG9qDa9S954afLv9L7Z922bkt55krA==
,1,4,545454,123 abc st}')
ERROR: malformed array literal:
"{poi2-10000064,poi2,test1,10000064,1,MC,,John,Doe,,,123 abc
st,mytown,WA,98101,,chris@paymentonline.com,US,X
FsWsbpBwQJHNK7z41QMv5bpoOLDefsljIXpaWMLeETb4DmStkfkTl1pQ2GT9Uxwe2Mip61TLOUIy8iFwEQV7/Jz3eqvhbwAe6Bc8IQqmQOm84/rPRYRxWZ/SGhc014P7luhSQkV9FHzOJcdvxDzOCCS
4siOC1Q1wETqhfs5fe0VCcydzahhgspRWeyKyboHWq61B032CW53SD0p3tOTo0JLe+qTRA8EGnrA
Kx6eWXn98wjyHJa7gGyHNQEJK+tlgkTxZmT7BYtkYGxhPOqsbj+MsyBDOFw7vZBXEVAi8TrZ2NdQ
AvDg6XSJBG9qDa9S954afLv9L7Z922bkt55krA==
,1,4,545454,123 abc st}"
LOG: statement: rollback
Import Notes
Resolved by subject fallback
"Chris" <chris@paymentonline.com> writes:
After looking closer at the logs, what seems to be happening is that it
doesn't like the empty array values.
Right, you need to explicitly write "" for an empty-string array element
now. (This was always legal, but is now required.) The array value
parser is stricter in some other ways too --- one that might be
important to you is that trailing whitespace isn't significant unless
quoted.
regards, tom lane
"Chris" <chris@paymentonline.com> writes:
After looking closer at the logs, what seems to be happening is that it
doesn't like the empty array values. All functions that I call with an array
that have one or more empty elements are returning this error. Should I be
setting the value to NULL instead of just leaving it empty?
You can't store null values in arrays (yet).
Assuming your arrays are text[] or varchar[] you can store the empty string
represented by "" though. You might find it safest to put "" around all your
parameters and quote "s inside them.
Or better yet (imho) is to use the new array[...] constructor. That you can do
using the existing quoting functions without worrying about someone sneaking a
double quote inside your values. They also play nicer with placeholders.
--
greg