Обсуждение: postgres 9.2 error whit apostrophes

Hello, excuse my English first

we recently move from pg9.0 to pg 9.2 whit this method:

from a new server PG9.2 we did:
pg_dump -h server -p 5432 -U user -Fc -i -b base-name > bk.backup

after that in the new server
createdb -T template0 -E LATIN1 basename
pg_restore -h server -p 5432 -U user -d basename bk.backup

now we are having problems with updates on tables  whit apostrophes like  >> o'higgins << on any character fields
First question:
            is there  any configurable parameter for PG9.2 that make 
            update tu02t00 set tu02pfusua = 'D'AGOSTINO'  work...
            the server error said   

ERROR: syntax error at or near "AGOSTINO" at character 187

Second question:
If there is no way to configure PG9.2 to accept this Cain of update
which is the best method to do a downgrade from 9.2 to 9.0 (we are not using any new function)


thanks in advance is ok?

On Tue, Jun 11, 2013 at 2:57 PM, Alejandro Brust
<alejandrob@pasteleros.org.ar> wrote:
> Hello, excuse my English first
>
> we recently move from pg9.0 to pg 9.2 whit this method:
>
> from a new server PG9.2 we did:
> pg_dump -h server -p 5432 -U user -Fc -i -b base-name > bk.backup
>
> after that in the new server
> createdb -T template0 -E LATIN1 basename
> pg_restore -h server -p 5432 -U user -d basename bk.backup
>
> now we are having problems with updates on tables  whit apostrophes like  >>
> o'higgins << on any character fields
> First question:
>             is there  any configurable parameter for PG9.2 that make
>             update tu02t00 set tu02pfusua = 'D'AGOSTINO'  work...
>             the server error said
>
> ERROR: syntax error at or near "AGOSTINO" at character 187
>
> Second question:
> If there is no way to configure PG9.2 to accept this Cain of update
> which is the best method to do a downgrade from 9.2 to 9.0 (we are not using
> any new function)

That shouldn't work in any version of postgresql.

Two ways to insert that.

1: escape it:
update tu02t00 set tu02pfusua = 'D''AGOSTINO' ...

2: Use $$ quotes:

update tu02t00 set tu02pfusua = $$D'AGOSTINO$$ ...

El 11/06/2013 18:42, Scott Marlowe escribió:
> On Tue, Jun 11, 2013 at 2:57 PM, Alejandro Brust
> <alejandrob@pasteleros.org.ar> wrote:
>> Hello, excuse my English first
>>
>> we recently move from pg9.0 to pg 9.2 whit this method:
>>
>> from a new server PG9.2 we did:
>> pg_dump -h server -p 5432 -U user -Fc -i -b base-name > bk.backup
>>
>> after that in the new server
>> createdb -T template0 -E LATIN1 basename
>> pg_restore -h server -p 5432 -U user -d basename bk.backup
>>
>> now we are having problems with updates on tables  whit apostrophes like  >>
>> o'higgins << on any character fields
>> First question:
>>             is there  any configurable parameter for PG9.2 that make
>>             update tu02t00 set tu02pfusua = 'D'AGOSTINO'  work...
>>             the server error said
>>
>> ERROR: syntax error at or near "AGOSTINO" at character 187
>>
>> Second question:
>> If there is no way to configure PG9.2 to accept this Cain of update
>> which is the best method to do a downgrade from 9.2 to 9.0 (we are not using
>> any new function)
> That shouldn't work in any version of postgresql.
>
> Two ways to insert that.
>
> 1: escape it:
> update tu02t00 set tu02pfusua = 'D''AGOSTINO' ...
>
> 2: Use $$ quotes:
>
> update tu02t00 set tu02pfusua = $$D'AGOSTINO$$ ...
>
YES, from psql that work great
I will explain more,
we were using a jdbc7 from genexus(developers IDE), with PG9.0 and it works
now we upgrade to pg9.2 without migrate the jdbc7 from genexus because
it seem donsnt work on genexus(developers problem, i know), but
this is the actual scenario
so, is any clue?
Server encoding?
client encoding?

thank in advance!!

Alejandro Brust wrote
>>
>> Two ways to insert that.
>>
>> 1: escape it:
>> update tu02t00 set tu02pfusua = 'D''AGOSTINO' ...
>>
>> 2: Use $$ quotes:
>>
>> update tu02t00 set tu02pfusua = $$D'AGOSTINO$$ ...
>>
> YES, from psql that work great
> I will explain more,
> we were using a jdbc7 from genexus(developers IDE), with PG9.0 and it
> works
> now we upgrade to pg9.2 without migrate the jdbc7 from genexus because
> it seem donsnt work on genexus(developers problem, i know), but
> this is the actual scenario
> so, is any clue?
> Server encoding?
> client encoding?

You should provide the actual string query you are supplying to JDBC as well
as the server's log message containing the entirety of the failing query.

In short, if the JDBC is sending a malformed query to PostgreSQL there is
not likely anything you can do to make PostgreSQL accept it.

The main change along these lines between 9.0 and 9.2 is the setting of the
GUC "standard_conforming_strings" to on by default (this happened in 9.1).
You might try changing this to "off" and see what happens.

see:
http://www.postgresql.org/docs/9.1/interactive/release-9-1.html
Section E.10.2.1
for details.

Again, it is impossible to really provide help without knowing exactly what
is being sent to, and more importantly received by, the PostgreSQL server.
The error message you provided is insufficient.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-9-2-error-whit-apostrophes-tp5758840p5758862.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Alejandro Brust wrote:
> we recently move from pg9.0 to pg 9.2 whit this method:
> 
> from a new server PG9.2 we did:
> pg_dump -h server -p 5432 -U user -Fc -i -b base-name > bk.backup
> 
> after that in the new server
> createdb -T template0 -E LATIN1 basename
> pg_restore -h server -p 5432 -U user -d basename bk.backup
> 
> now we are having problems with updates on tables  whit apostrophes like  >> o'higgins << on any
> character fields
> First question:
>             is there  any configurable parameter for PG9.2 that make
>             update tu02t00 set tu02pfusua = 'D'AGOSTINO'  work...
>             the server error said
> 
> ERROR: syntax error at or near "AGOSTINO" at character 187
>
> Second question:
> If there is no way to configure PG9.2 to accept this Cain of update
> which is the best method to do a downgrade from 9.2 to 9.0 (we are not using any new function)

This statement didn't work in any version of PostgreSQL.

I'm guessing that the statement really was:
   UPDATE tu02t00 SET tu02pfusua = 'D\'AGOSTINO'
(notice the backslash).

That would have worked in PostgreSQL 9.0 with default configuration
(and given you a warning), but won't work with PostgreSQL 9.2.

If that is your problem, you can set standard_confirming_strings = off
to restore the old behaviour.

It would be much better, though, to adapt your program to use
standard conforming strings, like this:
   UPDATE tu02t00 SET tu02pfusua = 'D''AGOSTINO'

Yours,
Laurenz Albe

ok
developers = bad guys
no way to modify the source code of applications or use the correct
JDBC........  so,

standard_conforming_string = off
backslash_quote = on
client_encoding = "same as DB"

its no the best way but.... it works
thank U all for yours help (this really guide me)






El 12/06/2013 04:03, Albe Laurenz escribió:
> Alejandro Brust wrote:
>> we recently move from pg9.0 to pg 9.2 whit this method:
>>
>> from a new server PG9.2 we did:
>> pg_dump -h server -p 5432 -U user -Fc -i -b base-name > bk.backup
>>
>> after that in the new server
>> createdb -T template0 -E LATIN1 basename
>> pg_restore -h server -p 5432 -U user -d basename bk.backup
>>
>> now we are having problems with updates on tables  whit apostrophes like  >> o'higgins << on any
>> character fields
>> First question:
>>             is there  any configurable parameter for PG9.2 that make
>>             update tu02t00 set tu02pfusua = 'D'AGOSTINO'  work...
>>             the server error said
>>
>> ERROR: syntax error at or near "AGOSTINO" at character 187
>>
>> Second question:
>> If there is no way to configure PG9.2 to accept this Cain of update
>> which is the best method to do a downgrade from 9.2 to 9.0 (we are not using any new function)
> This statement didn't work in any version of PostgreSQL.
>
> I'm guessing that the statement really was:
>    UPDATE tu02t00 SET tu02pfusua = 'D\'AGOSTINO'
> (notice the backslash).
>
> That would have worked in PostgreSQL 9.0 with default configuration
> (and given you a warning), but won't work with PostgreSQL 9.2.
>
> If that is your problem, you can set standard_confirming_strings = off
> to restore the old behaviour.
>
> It would be much better, though, to adapt your program to use
> standard conforming strings, like this:
>    UPDATE tu02t00 SET tu02pfusua = 'D''AGOSTINO'
>
> Yours,
> Laurenz Albe
>