Re: copy commands and linefeeds

On Tue, 2007-09-18 at 16:49 -0700, Mija Lee wrote:
> Hi -
>
> I have two questions that have arisen as a result of using the copy command to
> create csv files.
>
> 1. Apparently, the table from which I am trying to create the csv file has
> linefeeds/carriage returns in it that don't belong there. UGH! Is there a way to
> restrict the users from inserting linefeeds into the field? They are connecting
> via odbc from some windows app (probably access) so I assume it's a windows
> linefeed, and although I can't see them, I know they are there because the
> output has them, and I get the following warning:
>
> WARNING: CSV fields with embedded linefeed or carriage return characters might
> not be able to be reimported

You can either reject bad data or clean it up.

To reject it, put a constraint on the column:

alter mytable add check (mycolumne !~ E'[\r\n]');

To clean it up, create a trigger to be fired on insert or update:

CREATE FUNCTION clean_data()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.mycolumn = translate(NEW.mycolumn, E'\r\n', ' ');
RETURN NEW;
END;
$$;

CREATE TRIGGER clean_data BEFORE INSERT OR UPDATE
ON mytable FOR EACH ROW
EXECUTE PROCEDURE clean_data();

> 2. I'm not sure I understand the copy sytax. If I want text to be in single
> quotes instead of double quotes, the way I read the doc, the following should
> work:
>
> copy table1 to 'output.txt' with csv quote as ''';
>
> which doesn't seem to work. Am I misreading the doc?

Single quotes in a quoted string need to be doubled: ''''
Alternatively, you can use the escape string format: E'\'' or E'\047'.
(47 is the octal value of the single-quote character.)

In either case, what you get may not be what you want: every
single-quote character in the data will be doubled:

copy mytable to stdin with csv quote as E'\047';
198,Registered
200,'Fred''s Bar'
201,'Tom, Dick and Harry'

--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.