Re: Quoted strings on CLI

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

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@xxxxxxx>:
> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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