Re: General coding question - Mailing list pgsql-general

From Bill Moran
Subject Re: General coding question
Date
Msg-id 20111220134801.dce10369.wmoran@potentialtech.com
Whole thread Raw
In response to General coding question  (jkells <jtkells@verizon.net>)
List pgsql-general
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/

pgsql-general by date:

Previous
From: "David Johnston"
Date:
Subject: Re: General coding question
Next
From: "James B. Byrne"
Date:
Subject: PostgreSQL server architecture