[GENERAL] Backslashitis

[hamann . w]

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?

Regards
Wolfgang Hamann

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backslashitis

[Raghavendra]

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'{meeting}';
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, haman...@t-online.de wrote:

 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?

 Regards
 Wolfgang Hamann

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backslashitis

[Thomas Kellerer]

haman...@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





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backslashitis

[Raghavendra]

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

 haman...@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-STRINGShttp://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

Re: [GENERAL] Backslashitis

[Dean Rasheed]

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

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

 haman...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backslashitis

[Raghavendra]

 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/