Thread: 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.