Обсуждение: 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
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 >