Re: Escape double-quotes in text[]?

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.