On Tue, 2007-09-18 at 16:49 -0700, Mija Lee wrote:
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()
NEW.mycolumn = translate(NEW.mycolumn, E'\r\n', ' ');
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
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';
201,'Tom, Dick and Harry'
Oliver Elphick email@example.com
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.