Re: importing db as text files

On Thu, 14 Aug 2003 12:46:07 -0500
Bruno Wolff III <bruno(at)wolff(dot)to> wrote:

> On Thu, Aug 14, 2003 at 09:08:16 -0700,
> expect <expect(at)ihubbell(dot)com> wrote:
> >
> > 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,,,
>
> That should be handled by copy pretty easily. If the columns after what
> appears to be the zip code are some type of number rather than a string,
> then the empty string is going to be an invalid value.
>
> If you want the empty string to be interpreted as the default and you don't
> have any NULL values in your input, then the simplest thing to do is set
> the code for NULLs to be the empty string. You can then do queries after

I'm new to this so everything has to be explicit, I don't want to make any
leaps with what anyone is saying here.

copy db from '/usr/tmp/db' with delimiter ',' null '';

Is this the command that you're recommending?

> the data is in the database to change the NULLs to the appropiate default.
> If you are running the 7.4 beta you can use the DEFAULT key in the update,
> otherwise you will need to duplicate the default expression in the update
> commands. You will want to run update once for each column that can have
> NULL values using IS NULL in the WHERE clause.
> If these columns have a NOT NULL constraint, you may want to use a temporary
> table to load the data and then copy it over (with a single insert statement)
> after it has been cleaned up.
>
> Note that it isn't obvious what empty strings should map to for numbers.
> NULL and 0 make about as much sense as using the default value.

Well I'm new here but it seems to me they should map to the default value
for that column. Why wouldn't they?

>
>