Re: importing db as text files
От | Gregory S. Williamson |
---|---|
Тема | Re: importing db as text files |
Дата | |
Msg-id | 71E37EF6B7DCC1499CEA0316A256832801057078@loki.globexplorer.com обсуждение исходный текст |
Ответ на | importing db as text files (expect <expect@ihubbell.com>) |
Список | pgsql-general |
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@optushome.com.au] Sent: Fri 8/15/2003 3:11 AM To: pgsql-general@postgresql.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@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? > > > 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@postgresql.org)
В списке pgsql-general по дате отправления: