Re: Quoted strings on CLI

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(at)mac(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general