Re: How to remove quotes from COPY TO result?

On 07/18/2010 02:00 AM, Michael Rowan wrote:
> Thanks Joe. That works fine, but gave me a hint to use the escape string syntax for escapes e.g., E'\r\n'.
>
> I will attempt to discover what that means ;-)

Sorry, probably should have mentioned that but it was late when I replied...

See:
http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html
http://www.postgresql.org/docs/8.4/interactive/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS

In particular:

If the configuration parameter standard_conforming_strings is off,
then PostgreSQL recognizes backslash escapes in both regular and
escape string constants. This is for backward compatibility with
the historical behavior, where backslash escapes were always
recognized. Although standard_conforming_strings currently defaults
to off, the default will change to on in a future release for
improved standards compliance. Applications are therefore
encouraged to migrate away from using backslash escapes. If you
need to use a backslash escape to represent a special character,
write the string constant with an E to be sure it will be handled
the same way in future releases.

In addition to standard_conforming_strings, the configuration
parameters escape_string_warning and backslash_quote govern
treatment of backslashes in string constants.

So I would have been better to write it as:
COPY abbreviation FROM 'filename' WITH DELIMITER E'\t' CSV;
but old habits die hard ;-)

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support