Re: PostgreSQL Developer Best Practices

> On Sat, Aug 22, 2015 at 1:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Melvin Davidson <melvin6925(at)gmail(dot)com <mailto:melvin6925(at)gmail(dot)com>> writes:
> >> Best Practice would rather be something along the lines:
> >>>> Avoid coding in a way that triggers "WARNING:
> >>>> nonstandard use of escape in a string literal". If you
> >>>> cannot comply with this rule document your reasons.
>
> > Thanks for the suggestion. For the past few months I've been dealing with
> > an error log that is filled with these warnings simply because
> > the developers do not comprehend how to use ( or the requirement to use)
> > an escape clause.
>
> IMO best practice in this area is "run with standard_conforming_strings = ON".
> If you're seeing this warning at all, it's because you aren't doing that,
> which means your code is unnecessarily unportable to other DBMSes.
> Adopting a coding policy of always using E'' would make that worse.
>
> regards, tom lane
>

On 08/22/2015 02:40 PM, Melvin Davidson wrote:
> Tom,
>
> Thank you for pointing out "run with standard_conforming_strings = ON"..
> However, that is NOT the problem.
> What is occurring is that the developers are sending strings like 'Mr. M\'vey',
> which, if we set standard_conforming_strings = ON, would, and does, result in errors and the statement failing,
> which is a lot less desirable that a simple warning.
>
> Therefore, I am trying to educate the developers in the proper method of escaping strings,
> instead of loading up the error log with annoying warnings.
>

Please dont top post.

But you are not educating them correctly. Using E'' isnt right. The correct way to escape a quote is to double quote it: 'Mr. M''vey'

-Andy