Bill Moran <wmoran@potentialtech.com> writes:
> If I just do:
> SELECT 'can''t'::text;
> I get "can't" which is what I'd expect. I would then expect
> encode to escape the ' somehow. Even c-style escaping, like
> "can\'t" would have been less surprising to me.
> If there's something I'm missing, I'm still missing it.
The manual says that 'escape' encoding "merely outputs null bytes as
\000 and doubles backslashes". (The reason to double backslashes is to
make \000 unambiguous, of course.) The point of this is to sanitize
bytea data sufficiently to allow it to be transported as text. If you
want to transport it as a SQL literal, that's a tighter constraint that
would require some other escaping method, or at least passing the result
through something like quote_literal.
(Having said that, I wonder though if "escape" doesn't need more
thought. The output is only valid text in SQL_ASCII or single-byte
encodings, otherwise there's risk of encoding violations.)
regards, tom lane