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