Re: importing db as text files

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