Thread: General coding question

General coding question

From
jkells
Date:
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

Re: General coding question

From
"David Johnston"
Date:
-----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.



Re: General coding question

From
Bill Moran
Date:
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/

Re: General coding question

From
jkells
Date:
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,

Re: General coding question

From
"David Johnston"
Date:
-----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.