Skip site navigation (1) Skip section navigation (2)

importing using COPY, text and large objects.

From: "Howard Keith Fletcher" <hkfletcher(at)mail(dot)socket(dot)net>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: importing using COPY, text and large objects.
Date: 2000-07-07 22:12:40
Message-ID: 003101bfe860$780410a0$84246ad8@hkfletcher (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Let me set this up.  I have a list of text files.  Say the list might look like this:

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:


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

pgsql-novice by date

Next:From: Stephen LawrenceDate: 2000-07-08 02:37:43
Subject: Auto Increment
Previous:From: Stephen LawrenceDate: 2000-07-07 19:27:37
Subject: Re: Another DBD Problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group