From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Tim Pizey <tim@paneris.co.uk>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Newbie dbadmin out of his league
Date: Wed, 1 Sep 1999 18:45:46 +0300
Message-ID: <l0313030ab3f2f95055be@[147.233.159.109]> (raw)
In-Reply-To: <7648.935788758@sss.pgh.pa.us>
References: <3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk>
At 00:19 +0300 on 28/08/1999, Tom Lane wrote:
> > Presumably I again have to take control of the key values,
> > drop the index
> > copy from tab delimited file containing hard ids
> > create id sequence
> > create index
> > modify id definition
>
> Yup, that's about what you need to do. You can leave the "DEFAULT"
> clause where it is, since it won't be invoked during a COPY that's
> supplying non-default values for the ID column. (A good thing too,
> since I don't think we support ALTER TABLE ADD DEFAULT...)
Hmmm. If it were I, I would have tackled it in a slightly different way:
COPY the data into a temporary table, that doesn't have the id numbers at
all. Thus you don't have to have a counter on the client side, that knows
the last id that's already on the table, etc, etc., and you also don't have
to transfer several extra bytes per row through the postgres port.
Then, when you have a temp table, you can add the values to the main table
with an
INSERT INTO main_table (field1, field2, field3)
SELECT field1, field2, field3
FROM temp_table;
If you don't mention the field that carries the default in this INSERT
statement, it will invoke the default. Dropping the index may still be a
good idea. The temp table shouldn't have an index anyways.
This would save you at least the three last steps in your "recipe".
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, tim@paneris.co.uk
Subject: Re: [SQL] Newbie dbadmin out of his league
In-Reply-To: <l0313030ab3f2f95055be@[147.233.159.109]>
References: <3.0.3.32.19990827211232.006f4c40@popmail.dircon.co.uk> <l0313030ab3f2f95055be@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox