Thread: General coding question
General coding question. Can I insert a text string into a character
varying column that contains a \ as is, meaning no escaping of the
character or is this a bad practice?
I.e: Column data
========== ====================================
description SUBDIV LOT 13 & N1\2 LOT 14
Thanks
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of jkells
Sent: Tuesday, December 20, 2011 12:33 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] General coding question
General coding question. Can I insert a text string into a character varying column that contains a \ as is, meaning
noescaping of the character or is this a bad practice?
I.e: Column data
========== ====================================
description SUBDIV LOT 13 & N1\2 LOT 14
Thanks
-----------------------------------------------------
You never would actually store an "escaping" black-slash in the data. The need for an escape symbol occurs only during
dataentry and strictly depends on how you are entering data . As you have not provided those details further advice
cannotbe given.
David J.
In response to jkells <jtkells@verizon.net>: > General coding question. Can I insert a text string into a character > varying column that contains a \ as is, meaning no escaping of the > character or is this a bad practice? > > I.e: Column data > ========== ==================================== > description SUBDIV LOT 13 & N1\2 LOT 14 This is a moderately confusing issue because standards are involved, and standards frequently muddle things. According to the SQL standard, there is nothing special about \. It's just another character and is not treated specially in any way. PostgreSQL, for a long while, treated the \ as starting an escape character sequence, because this was common in many databases an generally useful for adding things like newlines. At some point (don't know when) the escaping syntax was added. This made it possible for PostgreSQL to be 100% ANSI SQL compliant while still supporting the old method of using the \ to start an escape sequence. The two critical tools for working with this are the standard_conforming_strings config parameter and the E'' syntax for strings. Documentation is here: http://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS Unfortunately, it's just a _bit_ more complicated than that, even, because the language you're using on the client side (which you don't mention) may also use the \ as a special character, so it may be converted to something before it's even transmitted to PostgreSQL. So, the direct answer to your question is, "There's nothing wrong or bad form about putting \ in your strings, but it can be difficult to do correctly, depending on the circumstances." -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Tue, 20 Dec 2011 13:32:32 -0500, David Johnston wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of jkells Sent:
> Tuesday, December 20, 2011 12:33 PM To: pgsql-general@postgresql.org
> Subject: [GENERAL] General coding question
>
> General coding question. Can I insert a text string into a character
> varying column that contains a \ as is, meaning no escaping of the
> character or is this a bad practice?
>
> I.e: Column data
> ========== ====================================
> description SUBDIV LOT 13 & N1\2 LOT 14
>
> Thanks
>
> -----------------------------------------------------
>
> You never would actually store an "escaping" black-slash in the data.
> The need for an escape symbol occurs only during data entry and strictly
> depends on how you are entering data . As you have not provided those
> details further advice cannot be given.
>
> David J.
David Thanks
My problem comes from 6 records containing a backslash in several columns
out of a million plus rows in many different tables. I am testing some
replication software and have found that for these 6 records the
destination tables contain two backslashes after being replicated.
Source (master) record
I.e: Column data
========== ====================================
description SUBDIV LOT 13 & N1\2 LOT 14
Destination (slave) becomes the following
description SUBDIV LOT 13 & N1\\2 LOT 14
My question was more generic since I cant see why a '\' character cant be
used in a character string (I.e. storage path etc.. ). How would you
escape a \ character that is needed to be stored in a string and is there
anything special that one would have to do when retrieving it?
Regards,
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of jkells
Sent: Tuesday, December 20, 2011 3:42 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] General coding question
On Tue, 20 Dec 2011 13:32:32 -0500, David Johnston wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of jkells Sent:
> Tuesday, December 20, 2011 12:33 PM To: pgsql-general@postgresql.org
> Subject: [GENERAL] General coding question
>
> General coding question. Can I insert a text string into a character
> varying column that contains a \ as is, meaning no escaping of the
> character or is this a bad practice?
>
> I.e: Column data
> ========== ====================================
> description SUBDIV LOT 13 & N1\2 LOT 14
>
> Thanks
>
> -----------------------------------------------------
>
> You never would actually store an "escaping" black-slash in the data.
> The need for an escape symbol occurs only during data entry and
> strictly depends on how you are entering data . As you have not
> provided those details further advice cannot be given.
>
> David J.
David Thanks
My problem comes from 6 records containing a backslash in several columns out of a million plus rows in many different
tables. I am testing some replication software and have found that for these 6 records the destination tables contain
twobackslashes after being replicated.
Source (master) record
I.e: Column data
========== ====================================
description SUBDIV LOT 13 & N1\2 LOT 14
Destination (slave) becomes the following
description SUBDIV LOT 13 & N1\\2 LOT 14
My question was more generic since I cant see why a '\' character cant be used in a character string (I.e. storage path
etc.. ). How would you escape a \ character that is needed to be stored in a string and is there
anything special that one would have to do when retrieving it?
-----------------------------------------------------
You really need to include details like "my problems comes from .... I am testing some replication software ..." in
youroriginal posting. In this case your replication system is broken. Mostly likely the issue stems from changes in
howPostgreSQL deals with string literals. There are two valid ways to write a string literal, one which escapes and
onewhich does not.
1) E'some string with possible back-slash escapes'
2) ' some string where back-slashes are treated as literals'
Old Way) 'some string with back-slash escapes and log-file warnings'
Your software is assuming that when it embeds a "\" to escape a contained "\" that PostgreSQL will process the escape
andleave only the original "\" in place. However, if the sever is configured such that the second form behavior is in
effectfor unadorned literals (i.e., lacking the E prefix) then the added "\" will remain and the result column with
haveeach instance of "\" duplicated.
You fail to mention your server versions (and any configuration changes thereto) but in older versions (<= 9.0) strings
inthe second form would be escaped (and logged) whereas, starting in 9.1, only strings in the first form have their
contentsanalyzed and escaped.
This behavior can be changed in the configuration files of PostgreSQL but your replication software should be able to
copewith either situation, ideally by querying the server for its current configuration and acting accordingly.
David J.