Thread: Insert text to column using copy command
I want to import text to a specific column and for other columns to specify literals/constants myself, using same copy command. For example, I have three columns: DocID(Int), SentenceID(serial), Sentence(varchar). I want with one COPY
command to specify DocID value, SentenceID will be autoincremented and to populate Sentence column with data from text file. To show visually, 2339.txt file looks like this(Numbers at the beginning are sentence coordinates.):
0,189 A few years ago , when I was looking at middle schools for my daughter...
190,412 Having reported on a vogue for single-sex classrooms in the nineties...
After using COPY I want table entries to look like this:
DocID | SentenceID | Sentence
2339 | 1 | A few years ago , when I was looking at middle ...
2339 | 2 | Having reported on a vogue for single-sex classrooms in the nineties...
I was not able to find any way to use copy command to do so. Also is there possibility to omit sentence coordinates when importing data into table?
Shmagi Kavtaradze <kavtaradze.s@gmail.com> writes:
> I want to import text to a specific column and for other columns to specify
> literals/constants myself, using same copy command.
COPY is not bright enough to do what you want. I'd suggest copying into
a temporary table that just receives the data literally as it appears in
the file, and then using INSERT ... SELECT ... to transform the data and
put it into the final table.
regards, tom lane