Re: importing db as text files

On Fri, 15 Aug 2003, expect wrote:

> On Fri, 15 Aug 2003 00:32:01 -0700 (PDT)
> Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> >
> > On Thu, 14 Aug 2003, expect wrote:
> >
> > > On Thu, 14 Aug 2003 12:46:07 -0500
> > > Bruno Wolff III <bruno@wolff.to> wrote:
> > > > 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?
> >
> > One problem with doing that is that it's inconsistent.
>
> Please elaborate.  How is it inconsistent, exactly?

See my comments below.

> >
> > Given
> > create table test(
> >  a text default 'abc',
> >  b int default 5
> > );
> >
> > copy test from stdin with delimiter ',';
> > ,
> > \.
> >
> > What would you expect the values of the row in test
> > to be?
>
>
> Oh a test....
>
> Does the \. end the STDIN input?
>
> Where's the null option?  Don't you mean:
>
> copy test from stdin with delimiter ',' null '';

No, I'm saying without a null specifier.

> In this case I would expect the row to have:
>
>   a  |  b
> ----------
>      |
>  abc |  5
>
>
> Is this too much to expect?

Without a null specifier of '', empty string is a valid value for a.
Why would it get the default, and how would you insert an empty string?
Should it treat b differently because '' isn't valid for that type, that'd
be inconsistent.

With a null specifier of '', empty string is valid for both and means
NULL.  Otherwise, how would you specify a null when you have that
null specifier?

What you probably really want is another specifier that inserts the
default for a column, so you could say something like:
copy test from stdin with delimiter ',' default '';