Thread: importing using COPY, text and large objects.
Let me set this up. I have a list of text files. Say the list might look like this:
usr/doc/fileA-1.2.3
usr/doc/fileB_extra-1.4.5
usr/doc/fileA-13.2.3
usr/doc/fileC_more-3.4.6
etc.
etc.
etc. till I get to about 5000 or more of these files
I want to extract part of the names of the files from these paths and create the first column in a postgres table. Easy enough to do with a search and replace in a text file with an editor to get something like the following:
fileA
fileB_extra
fileA
fileC_more
and then using a COPY FROM to import it into the table. But maybe there is a better way that is less manual.
But the main problem I'm looking for answers for is that I also want to put the associated full path name in the 2nd column, in the correctly associated row, and the correctly associated Large Object reference in the 3rd column which correctly references the path in Column 2 and again, in the same row of course.
name path lobref smalltxt
Column 1 Column 2 Column 3 Column 4
fileA usr/doc/fileA-1.2.3 large object reference small text string from column 3
fileB usr/doc/fileB_extra-1.4.5 large object reference small text string from column 3
fileA usr/doc/fileA-13.2.3 large object reference small text string from column 3
file C usr/doc/fileC_more-3.4.6 large object reference small text string from column 3
In Column 4, I would like to have room for small edited text extracts from the large object file in Column 3 consisting of maybe 2 or 3 descriptive statements or 6 or 7 lines of text. I might also have Columns 5, 6, 7 and 8 hold similar text strings.
Maybe this is too much for one table. I don't know. Maybe doing it with OIDs and Links is better. I'd like your insights. But once again, the big thing I'd like to know is how do I get this kind of arrangement from what is essentially just the list of file paths as listed in Column 2. I want some way to make sure I don't get the file name and path and large objects and small text strings hooked up to items on the wrong rows. Much appreciated. Keith Fletcher
Come to think of it. Is there a way to automate taking small blocks of text from associated large objects and putting them into columns of small text strings? If each large object text file has a 5-7 line section that begins with START and ends with END could this be done? Well, here's to new guys getting their feet wet. :-)