Thread: Quoted string in select and insert

Hi,

I just migrated from mysql and am running into an issue I found no solution for when researching. Using the web
interfaceI 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

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

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