Backslashitis

I think you need to double the quotes. Its mentioned in the PG documention
http://www.postgresql.org/docs/9.1/static/arrays.html

Eg:-

postgres=# update array_test set name=E'{"meet\\\\ing"}';
UPDATE 2
postgres=# select * from array_test ;
name
---------------
{"meet\\ing"}
{"meet\\ing"}
(2 rows)

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

On Thu, Jun 14, 2012 at 1:47 PM, <hamann.w@t-online.de> wrote:

hamann.w@t-online.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

On Thu, Jun 14, 2012 at 2:19 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:

hamann.w@t-online.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<http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS&gt;

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

On 14 June 2012 10:03, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:

On Thu, Jun 14, 2012 at 2:19 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:

hamann.w@t-online.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

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

Thanks

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/