Re: importing db as text files

On Thu, 14 Aug 2003 08:45:20 -0500
Bruno Wolff III <bruno(at)wolff(dot)to> wrote:

> On Wed, Aug 13, 2003 at 20:45:55 -0700,
> expect <expect(at)ihubbell(dot)com> wrote:
> > On Wed, 13 Aug 2003 16:30:04 -0500
> > Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
> >
> > > On Wed, Aug 13, 2003 at 14:14:20 -0700,
> > > expect <expect(at)ihubbell(dot)com> wrote:
> > > >
> > > > What's the big deal with importing text files? I have a 70 MB file to import
> > > > and it's been one problem after another. I used the copy command and it appears
> > > > that it's just not possible. I finally massaged the file into a .sql file and
> > >
> > > That is unlikely, but without more details it is hard to say what you need
> > > to do.
> >
> >
> > What's unlikely? That it didn't work? But it didn't work.
>
> That it isn't possible to load your data using the copy command.
> You may need to do some transformation before sending it to copy.
> Using copy is probably going to give you the fastest load time.
>
> > From what I've read it should use the default value when there is no value.
> > Is that not true? Is this a known issue?
>
> Where did you read that? What do you mean by 'no value'?

The data is really very simple. Here's a pseudo-example of 2 rows of data:

191922C,Bob Cobb,D'Obbalina Sr.,312 Elm Street,Yountville,CA,94599,5,062001,082009
339111C,Elma Thelma,Velma,98 Oak Lane,St. Louis,MO,63119-2065,,,

>
> The syntax for the insert command is given in the manual:
> INSERT INTO table [ ( column [, ...] ) ]
> { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
>
> >
> > No you've come to the wrong impression. I believe that the problem lies in the
> > fact that pg will not default to the default value when no value is present.
>
> And how do you think 'no value' is represented? If you use default to
> respresent 'no value' then things will work as you expect for insert
> statements. When you are using copy, defaults can't be used on a row
> by row basis (but unlisted columns will get default values) and you will
> need to inlcude the value that you want in each row.
>
>