Thread: Escape double-quotes in text[]?
Hi all,
Probably a silly question, but I'm having trouble figuring out the
answer... if I'm constructing an string representation of a value to go
into a text[] column, and one of the text literals includes
double-quotes, do I need to escape the literal?
For example, can I insert something like this into a text[] column
directly? -
'{"abc", "de"f"}'
Or would I need to do this? -
E'{"abc", "de"f"}'
....or something different again? I'm doing this from PHP via the Zend
framework (v.1) if it makes any difference.
Thanks,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
On 29/04/2014 22:10, Raymond O'Donnell wrote:
> Hi all,
>
> Probably a silly question, but I'm having trouble figuring out the
> answer... if I'm constructing an string representation of a value to go
> into a text[] column, and one of the text literals includes
> double-quotes, do I need to escape the literal?
>
> For example, can I insert something like this into a text[] column
> directly? -
>
> '{"abc", "de"f"}'
>
> Or would I need to do this? -
>
> E'{"abc", "de"f"}'
>
> ....or something different again? I'm doing this from PHP via the Zend
> framework (v.1) if it makes any difference.
....and I should have mentioned that I'm on PostgreSQL 9.3.
R.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Raymond O'Donnell wrote
> Hi all,
>
> Probably a silly question, but I'm having trouble figuring out the
> answer... if I'm constructing an string representation of a value to go
> into a text[] column, and one of the text literals includes
> double-quotes, do I need to escape the literal?
>
> For example, can I insert something like this into a text[] column
> directly? -
>
> '{"abc", "de"f"}'
>
> Or would I need to do this? -
>
> E'{"abc", "de"f"}'
>
> ....or something different again? I'm doing this from PHP via the Zend
> framework (v.1) if it makes any difference.
>
> Thanks,
>
> Ray.
The easy way:
SELECT ARRAY['ab"c','de"f']::varchar[] => {"ab"c","de"f"}
Knowing the above; reverse-engineer the literal input syntax
SELECT {"ab"c","de"f"}::varchar[] #Nope "bare {"
SELECT '{"ab"c","de"f"}'::varchar[] #Nope "malformed array literal"
SELECT '{"ab\"c","de\"f"}'::varchar[] #Yay!
SELECT E'{"ab\"c","de\"f"}'::varchar[] #hmmm.....
SELECT E'{"ab\\"c","de\\"f"}'::varchar[] #yeah, double-escape (literal
first, then array)
This is all documented but it does not seem to be centrally summarized; you
need to check few different array-related areas to pick up the rules and/or
capabilities (namely, use ARRAY[...] syntax if at all possible).
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Escape-double-quotes-in-text-tp5801913p5801917.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Raymond O'Donnell wrote
> '{"abc", "de"f"}'
>
> Or would I need to do this? -
>
> E'{"abc", "de"f"}'
Do you realize that both of the above expressions are effectively identical?
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Escape-double-quotes-in-text-tp5801913p5801918.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 29/04/2014 22:54, David G Johnston wrote:
> Raymond O'Donnell wrote
>> Hi all,
>>
>> Probably a silly question, but I'm having trouble figuring out the
>> answer... if I'm constructing an string representation of a value to go
>> into a text[] column, and one of the text literals includes
>> double-quotes, do I need to escape the literal?
>>
>> For example, can I insert something like this into a text[] column
>> directly? -
>>
>> '{"abc", "de"f"}'
>>
>> Or would I need to do this? -
>>
>> E'{"abc", "de"f"}'
>>
>> ....or something different again? I'm doing this from PHP via the Zend
>> framework (v.1) if it makes any difference.
>>
>> Thanks,
>>
>> Ray.
>
> The easy way:
>
> SELECT ARRAY['ab"c','de"f']::varchar[] => {"ab"c","de"f"}
>
> Knowing the above; reverse-engineer the literal input syntax
>
> SELECT {"ab"c","de"f"}::varchar[] #Nope "bare {"
> SELECT '{"ab"c","de"f"}'::varchar[] #Nope "malformed array literal"
> SELECT '{"ab\"c","de\"f"}'::varchar[] #Yay!
> SELECT E'{"ab\"c","de\"f"}'::varchar[] #hmmm.....
> SELECT E'{"ab\\"c","de\\"f"}'::varchar[] #yeah, double-escape (literal
> first, then array)
>
> This is all documented but it does not seem to be centrally summarized; you
> need to check few different array-related areas to pick up the rules and/or
> capabilities (namely, use ARRAY[...] syntax if at all possible).
Thanks a million David - that's very helpful.
ARRAY[] doesn't work for me, unfortunately; I'm using parametrised
queries in Zend framework, and all the parameters get put in as strings,
so I need to build the array literals before submitting them.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Raymond O'Donnell wrote
> On 29/04/2014 22:54, David G Johnston wrote:
>> Raymond O'Donnell wrote
>>> Hi all,
>>>
>>> Probably a silly question, but I'm having trouble figuring out the
>>> answer... if I'm constructing an string representation of a value to go
>>> into a text[] column, and one of the text literals includes
>>> double-quotes, do I need to escape the literal?
>>>
>>> For example, can I insert something like this into a text[] column
>>> directly? -
>>>
>>> '{"abc", "de"f"}'
>>>
>>> Or would I need to do this? -
>>>
>>> E'{"abc", "de"f"}'
>>>
>>> ....or something different again? I'm doing this from PHP via the Zend
>>> framework (v.1) if it makes any difference.
>>>
>>> Thanks,
>>>
>>> Ray.
>>
>> The easy way:
>>
>> SELECT ARRAY['ab"c','de"f']::varchar[] => {"ab"c","de"f"}
>>
>> Knowing the above; reverse-engineer the literal input syntax
>>
>> SELECT {"ab"c","de"f"}::varchar[] #Nope "bare {"
>> SELECT '{"ab"c","de"f"}'::varchar[] #Nope "malformed array literal"
>> SELECT '{"ab\"c","de\"f"}'::varchar[] #Yay!
>> SELECT E'{"ab\"c","de\"f"}'::varchar[] #hmmm.....
>> SELECT E'{"ab\\"c","de\\"f"}'::varchar[] #yeah, double-escape (literal
>> first, then array)
>>
>> This is all documented but it does not seem to be centrally summarized;
>> you
>> need to check few different array-related areas to pick up the rules
>> and/or
>> capabilities (namely, use ARRAY[...] syntax if at all possible).
>
> Thanks a million David - that's very helpful.
>
> ARRAY[] doesn't work for me, unfortunately; I'm using parametrised
> queries in Zend framework, and all the parameters get put in as strings,
> so I need to build the array literals before submitting them.
SELECT regexp_split_to_array('val"1|val"2|val"3','\|');
SELECT regexp_split_to_array(?,'\|');
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Escape-double-quotes-in-text-tp5801913p5802008.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 30/04/2014 17:32, David G Johnston wrote:
> Raymond O'Donnell wrote
>> On 29/04/2014 22:54, David G Johnston wrote:
>>> Raymond O'Donnell wrote
>>>> Hi all,
>>>>
>>>> Probably a silly question, but I'm having trouble figuring out the
>>>> answer... if I'm constructing an string representation of a value to go
>>>> into a text[] column, and one of the text literals includes
>>>> double-quotes, do I need to escape the literal?
>>>>
>>>> For example, can I insert something like this into a text[] column
>>>> directly? -
>>>>
>>>> '{"abc", "de"f"}'
>>>>
>>>> Or would I need to do this? -
>>>>
>>>> E'{"abc", "de"f"}'
>>>>
>>>> ....or something different again? I'm doing this from PHP via the Zend
>>>> framework (v.1) if it makes any difference.
>>>>
>>>> Thanks,
>>>>
>>>> Ray.
>>>
>>> The easy way:
>>>
>>> SELECT ARRAY['ab"c','de"f']::varchar[] => {"ab"c","de"f"}
>>>
>>> Knowing the above; reverse-engineer the literal input syntax
>>>
>>> SELECT {"ab"c","de"f"}::varchar[] #Nope "bare {"
>>> SELECT '{"ab"c","de"f"}'::varchar[] #Nope "malformed array literal"
>>> SELECT '{"ab\"c","de\"f"}'::varchar[] #Yay!
>>> SELECT E'{"ab\"c","de\"f"}'::varchar[] #hmmm.....
>>> SELECT E'{"ab\\"c","de\\"f"}'::varchar[] #yeah, double-escape (literal
>>> first, then array)
>>>
>>> This is all documented but it does not seem to be centrally summarized;
>>> you
>>> need to check few different array-related areas to pick up the rules
>>> and/or
>>> capabilities (namely, use ARRAY[...] syntax if at all possible).
>>
>> Thanks a million David - that's very helpful.
>>
>> ARRAY[] doesn't work for me, unfortunately; I'm using parametrised
>> queries in Zend framework, and all the parameters get put in as strings,
>> so I need to build the array literals before submitting them.
>
> SELECT regexp_split_to_array('val"1|val"2|val"3','\|');
>
> SELECT regexp_split_to_array(?,'\|');
>
> David J.
Ahhhh.... very nice! Thank you!
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie