General coding question

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

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