Re: importing db as text files

I inderstand why NULL is not the same as a default value. I do not understand why an integer column:
some_val INT,

will not accept a null value (not that there is no default).

NULL is NULL ... why do I have to massage load data to substitute a '0' when what I really want is NULL (no data).

Just one of those things I'll never get, I guess.

Thanks for the information ...

Greg W.

-----Original Message-----
From: Jason Godden [mailto:jasongodden(at)optushome(dot)com(dot)au]
Sent: Fri 8/15/2003 3:11 AM
To: pgsql-general(at)postgresql(dot)org
Cc:
Subject: Re: [GENERAL] importing db as text files

Whilst I agree with what Bruno said regarding adding a default option to the
copy syntax I think that the basic principles Stephan and I outlined in how
copy treats your import is correct and the developers did the right thing.

Now if the devs, you or I want to add a default option to copy in the future
thats all good but for the moment you will have to use some pre or post
process to get the absolute effect you require. It's not that hard..
literally one line of piped commands on the command line where you specify
the defaults or a post process that does the final import into your
production tables from your data import table.

Remember NULL != Default. These are VERY different concepts and I believe
that enough examples of how this behaviour can lead to confusion have been
shown. With copy it is also important to remember that is not part of the
SQL standard and PG doesn't use SQL statements to do the import but rather a
low-level C operation. If you need the behaviour you're referring to sed/awk
the data up and generate BEGIN... INSERT... COMMIT... statements and pipe
that to PG. It's not that difficult and I'll give you some suggestions with
it if you want.

I'm not trying to be difficult with my view of this but there are always other
ways (sometimes one liners) that can achieve the behaviour you're after.

Rgds,

Jason

On Fri, 15 Aug 2003 05:53 pm, you wrote:
> On Fri, 15 Aug 2003 00:32:01 -0700 (PDT)
>
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> > On Thu, 14 Aug 2003, expect wrote:
> > > On Thu, 14 Aug 2003 12:46:07 -0500
> > >
> > > Bruno Wolff III <bruno(at)wolff(dot)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?
>
> > 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 '';
>
>
> In this case I would expect the row to have:
>
> a | b
> ----------
>
> abc | 5
>
>
> Is this too much to expect?
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)