Re: Table Merge Successful, Primary Keys Missing

> > 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.