Re: General coding question - Mailing list pgsql-general

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