Thread: Table Merge Successful, Primary Keys Missing
i'm trying to merge two tables together. i'm able to do it with... select * into t_temp_final from t_temp_output union SELECT * FROM t_product ; however, i lost all my primary key values in t_temp_final i can't create a pkey now b/c i have 1000 null values. how can i get around this situation? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
clarification below... --- operationsengineer1@yahoo.com wrote: > i'm trying to merge two tables together. > > i'm able to do it with... > > select * > into t_temp_final > from t_temp_output > union > SELECT * FROM t_product > ; > > however, i lost all my primary key values in > t_temp_final > > i can't create a pkey now b/c i have 1000 null > values. > > how can i get around this situation? > > tia... 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. is this the best way? if so, how can i work in a serial field when doing an insert into? tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > 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.
> > > 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. Richard, thanks for the input. when i follow that syntax, i get an error b/c the sql considers "nextval" to be a column and so the number of columns no longer match and i get an error. also, when i try this... select nextval(product_id), * from t_temp_output the column for nextval has no values. if i try... select nextval(t_temp_output_product_id_seq), * from t_temp_output ; i get the following error: ERROR: column "t_product_product_id_seq" does not exist __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
> > > > select *
> > > > into t_temp_final
> > > > from t_temp_output
> > > > union
> > > > SELECT * FROM t_product
> > > > ;
> > select nextval(what_ever_your_sequence_name_is),
> > colA, colB, colC, ...
> > into t_temp_final
> > from t_temp_output
> > union
> > SELECT * FROM t_product
> > ;
> select nextval(product_id), *
> from t_temp_output
> the column for nextval has no values. if i try...
> select nextval(t_temp_output_product_id_seq), *
> from t_temp_output
> ;
> i get the following error:
> ERROR: column "t_product_product_id_seq" does not exist
I believe that the sequence name must be in quotes nextval('t_temp_output_product_id_seq').
Just to test that the syntax works you should be able to try:
select nextval('t_temp_output_product_id_seq');
If this works, you might want to try to above method again. If not then try the below method.
You mentioned that you were able to get all of the values into t_temp_final.
The only problem was that your product key was null correct. Maybe this will work:
update t_temp_final
set product_id = nextval('t_temp_output_product_id_seq')
where product_id is null;
Hope one of these solutions works.
Regards,
Richard Broersma Jr.