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