Re: Backslashitis

On 14 June 2012 10:03, Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> wrote:
>
> On Thu, Jun 14, 2012 at 2:19 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>>
>> hamann(dot)w(at)t-online(dot)de, 14.06.2012 10:17:
>>
>>> Hi,
>>>
>>> I have a column declared as array of text. I can get a single backslash
>>> into one of the array elements by
>>> update ... set mycol[1] = E'blah \\here'
>>> If I try to update the whole array
>>> update ... set mycol = E'{"blah \\here"}'
>>> the backslash is missing. I can get two backslashes there.
>>> Is there a good way to solve the problem, other than rewriting my update
>>> script to do array updates one element at a time?
>>>
>>
>> Setting
>>       standard_conforming_strings = true
>>
>> should do the trick.
>>
>>
>> http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS
>>
>> In that case you don't need any escaping inside the string literals.
>>
>> Regards
>> Thomas
>>
>
> Nope..
>
> postgres=# show standard_conforming_strings ;
>  standard_conforming_strings
> -----------------------------
>  on
> (1 row)
> postgres=# set standard_conforming_strings =on;
> SET
> postgres=# show standard_conforming_strings ;
>  standard_conforming_strings
> -----------------------------
>  on
> (1 row)
> postgres=# update array_test set name=E'{"meet\\ing"}';
> UPDATE 2
> postgres=# select * from array_test ;
>    name
> -----------
>  {meeting}
>  {meeting}
> (2 rows)
>
> Correct me, if anything wrong.
>
> --Raghav
>

With standard conforming strings on, you could use any of the following:

update foo set a= E'{"blah \\\\here"}';
update foo set a= '{"blah \\here"}';
update foo set a= ARRAY[E'blah \\here'];
update foo set a= ARRAY['blah \here'];

I tend to prefer the ARRAY[...] constructor syntax because it doesn't
require any additional escaping of individual elements.

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

Regards,
Dean