> > select *
> > into t_temp_final
> > from t_temp_output
> > union
> > SELECT * FROM t_product
> > ;
> the current t_product table entries have product_id
> values. the temp table doesn't. so i need to add
> product_ids wher where product_id IS NULL.
>
> if i knew how to create a serial field (and control
> its values to start at a level greater than the
> highest t_product product_id value) in an insert into
> temp table, i could create product_ids when i create
> the insert into temp table and then merge everything
> together - complete with product_ids in place.
When you define a column to use a serial, you are really just using a nifty short-cut.
What actually happens, (and you could manually do all of this by hand) is:
1st. a sequence is created (it is just a "kind" of storage are that keeps track of the last
number used. in your case, it is the last number use as a primary key.) your automated sequence
is name something like "a_id_sequence"
2nd. your create table statement would actuall be read as:
create table a ( id integer default nextval(a_id_sequence) primary key).
http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
notice section 8.1.4
so, long story short. you can directly call the nextval() of the sequence with you insert your
temp table.
select nextval(what_ever_your_sequence_name_is), colA, colB, colC, ...
into t_temp_final
from t_temp_output
union
SELECT * FROM t_product
;
just be sure that the column name maps to the correct destination columns.
Regards,
Richard Broersma Jr.
