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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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