Обсуждение: copy commands and linefeeds

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


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?

Thanks in advance for any help!

Mija

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@lfix.co.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.

Oliver:

thanks so much for the help. Just for folks who might be looking on the list,
Oliver's suggestion works with 8.1 and above:

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

whereas this works on 8.0:

    alter table mytable add check (mycolumn !~ '\\r\\n');

Maybe everyone already knows this...

Mija

On Wed, 2007-09-19 at 11:19 -0700, Mija Lee wrote:
> Oliver:
>
> thanks so much for the help. Just for folks who might be looking on the list,
> Oliver's suggestion works with 8.1 and above:
>
>     alter table mytable add check (mycolumn !~ E'[\n\r]');
>
> whereas this works on 8.0:
>
>     alter table mytable add check (mycolumn !~ '\\r\\n');

But don't forget the square brackets: '[\\r\\n]'
otherwise you would match only a return followed by a linefeed rather
than either by itself.

> Maybe everyone already knows this...
>
> Mija
>
>
--
Oliver Elphick                                          olly@lfix.co.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.