On Tue, Sep 12, 2006 at 09:27:55AM -0700, rloefgren@forethought.net wrote:
> I have a pipe delimited text file I'm trying to copy to a table. The
> file has 17 fields per line. The table has 18, with that last field
> (record) a serial with sequence. I have done:
> select setval('sequence_name_seq', 555, 'TRUE')
> but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter
> '|'
> the copy stops at the first row, insisting that it's missing data for
> the field record. Well, yeah...
> I can make this work with inserts but not with COPY FROM. What I've
> been doing is dumping it into a mysql table with an auto_increment
> field and then dumping that into a text file and using that for the
> COPY FROM; certainly clumsy. How might this be done?
You could provide a column list:
COPY tablename (col1name, col2name, ..., col17name) FROM ...
Or, easier than loading/dumping through another database, run the
file through a filter that adds the numbering:
perl -lne 'print "$_|$."' file.txt > file_numbered.txt
If the file were large and you didn't want to make a copy of it
then you could use a script like this:
#!/usr/bin/perl -ln
BEGIN {print "copy tablename from stdin delimiter '|';";}
print "$_|$.";
END {print "\\."} # should also work without this line
Run the script and pipe the output into psql:
script_name file.txt | psql database_name
--
Michael Fuhr
