Re: Quoted string in select and insert

Alexander Reichstadt wrote:
> Hi,
>
> I just migrated from mysql and am running into an issue I found no
> solution for when researching. Using the web interface I can insert
> values that contain single-quotes. But using the CLI I found no way
> to select or insert single quotes:
>
> PetWork=# select * from persons where firstname='\'Peter\''; Invalid
> command \'';. Try \? for help. PetWork-# select * from persons where
> firstname='\\'Peter\\''; Invalid command \. Try \? for help.
> PetWork-# select * from persons where firstname='\\\'Peter\\\'';
> Invalid command \. Try \? for help. PetWork-# select * from persons
> where firstname='\\\\'Peter\\\\''; Invalid command \. Try \? for
> help. PetWork-# select * from persons where
> firstname='\\\\'Peter\\\\'';
>
> On different forums the solution suggested is to prepend backslashes.
> But it doesn't matter how many backslashes I prepend, it always
> fails. What does one have to do to achieve that?
>
> Thanks for any help

The SQL specification lists the apostrophe as the official escape
character. This is what Postgres uses. The backslash is a non-standard
extension. If you want to store or match an apostrophe in the database,
you need to double it. The standard suggestion to convert dumps and
scripts is a sed substitution command, something like this:

$ sed "s/\\\'/\'\'/g" > temp.sql

I don't know if that is available on your MAC.

A Google search on the phrase "convert mysql to postgresql sql" will
lead you to a wealth of information on this topic. You are likely to
have some problems with data types as well.

Bob McConnell
N2SPP