Thread: Quoted strings on CLI
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
Hello you cannot use a \ escaping when you have standard_conforming_strings enabled. There are four ways: first - use a usual way for using quotes - use a doubling single quotes postgres=# select 'Peter''s toy'; ?column? ───────────── Peter's toy (1 row) or you can use a own string delimiters postgres=# select $$Peter's toy$$; ?column? ───────────── Peter's toy (1 row) or you can disable standard_conforming_strings postgres=# set standard_conforming_strings to off; SET postgres=# select 'Peter\'s toy'; WARNING: nonstandard use of \' in a string literal LINE 1: select 'Peter\'s toy'; ^ HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...'). ?column? ───────────── Peter's toy (1 row) or you can use PostgreSQL enhanced strings postgres=# select E'Peter\'s toy'; ?column? ───────────── Peter's toy (1 row) Regards Pavel Stehule 2012/3/2 Alexander Reichstadt <lxr@mac.com>: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Thanks a lot, I use the '' approach, works for my case. Regards Am 02.03.2012 um 10:14 schrieb Pavel Stehule: > Hello > > you cannot use a \ escaping when you have standard_conforming_strings enabled. > > There are four ways: > > first - use a usual way for using quotes - use a doubling single quotes > > postgres=# select 'Peter''s toy'; > ?column? > ───────────── > Peter's toy > (1 row) > > or you can use a own string delimiters > > postgres=# select $$Peter's toy$$; > ?column? > ───────────── > Peter's toy > (1 row) > > or you can disable standard_conforming_strings > > postgres=# set standard_conforming_strings to off; > SET > postgres=# select 'Peter\'s toy'; > WARNING: nonstandard use of \' in a string literal > LINE 1: select 'Peter\'s toy'; > ^ > HINT: Use '' to write quotes in strings, or use the escape string > syntax (E'...'). > ?column? > ───────────── > Peter's toy > (1 row) > > or you can use PostgreSQL enhanced strings > > postgres=# select E'Peter\'s toy'; > ?column? > ───────────── > Peter's toy > (1 row) > > Regards > > Pavel Stehule > > > 2012/3/2 Alexander Reichstadt <lxr@mac.com>: >> 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 >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general