Re: General coding question

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of jkells
Sent: Tuesday, December 20, 2011 3:42 PM
To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of jkells Sent:
> Tuesday, December 20, 2011 12:33 PM To: pgsql-general(at)postgresql(dot)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?

-----------------------------------------------------

You really need to include details like "my problems comes from .... I am testing some replication software ..." in your original posting. In this case your replication system is broken. Mostly likely the issue stems from changes in how PostgreSQL deals with string literals. There are two valid ways to write a string literal, one which escapes and one which 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 and leave only the original "\" in place. However, if the sever is configured such that the second form behavior is in effect for unadorned literals (i.e., lacking the E prefix) then the added "\" will remain and the result column with have each instance of "\" duplicated.

You fail to mention your server versions (and any configuration changes thereto) but in older versions (<= 9.0) strings in the second form would be escaped (and logged) whereas, starting in 9.1, only strings in the first form have their contents analyzed and escaped.

This behavior can be changed in the configuration files of PostgreSQL but your replication software should be able to cope with either situation, ideally by querying the server for its current configuration and acting accordingly.

David J.