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