Re: Quoted string in select and insert

Bob McConnell wrote:
> Alexander Reichstadt wrote:
>> I just migrated from mysql [sic] and am running into an issue I found no
>> solution for when researching. Using the web interface I can insert

Where did you research? The answer is in the Postgres manual.

And the SQL standard.

>> 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.

For that matter, the Fine Manual answers your question, Alexander:
<http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS>

I like to read the manual when I have a syntax question, don't you?

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg