Re: [SQL] Newbie dbadmin out of his league

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