0

I have two tables with same structure, first column gid as serial, and many other columns after that. I want to insert selected rows from one table to the other. Without serial it is really easy:

insert into all_roads select * from new_roads where add_road = 1;

But with serial as first column in both I do get error:

ERROR:  duplicate key value violates unique constraint

I really do not want to copy serial number from one table to the other. I rather use default serial value for all new inserted records. Is there a way without writing insert which manually lists all the columns except the first - serial?

0

You could list out the non-gid columns in your SELECT:

edb=# create table foo (id serial primary key, col1 text, col2 text);
CREATE TABLE
edb=# create table foo_new (id serial primary key, col1 text, col2 text);
CREATE TABLE
edb=# insert into foo values (default, generate_series(1,100)::text,'mytext');
INSERT 0 100
edb=# insert into foo_new values (default, generate_series(1,100)::text,'yourtext');
INSERT 0 100
edb=# insert into foo_new select * from foo where id = 1;
ERROR:  duplicate key value violates unique constraint "foo_new_pkey"
DETAIL:  Key (id)=(1) already exists.
edb=# insert into foo_new (col1, col2) (select col1, col2 from foo where id = 1);
INSERT 0 1
edb=# select * from foo_new where col2 = 'mytext';
 id  | col1 |  col2  
-----+------+--------
 101 | 1    | mytext
(1 row)

You could also try creating a temporary table, then drop the gid column, and then copy that table into the new table:

edb=# create temporary table foo_temp AS SELECT * FROM foo;
SELECT 100
edb=# alter table foo_temp drop column id;
ALTER TABLE
edb=# insert into foo_new (col1, col2) (select col1, col2 from foo);
INSERT 0 100
edb=# select count(*), col2 FROM foo_new group by col2;
 count |   col2   
-------+----------
   100 | yourtext
   100 | mytext
(2 rows)

If you don't want to list out all the columns by hand, because you have too many, I suppose you can use a script to create a listing of all the non-gid columns, and then copy/paste into your INSERT statement:

edb=# select array_to_string(ARRAY(SELECT column_name::text
                                     FROM information_schema.columns 
                                    WHERE table_name = 'foo' 
                                      AND column_name::text NOT IN ('id')),',');
 array_to_string 
-----------------
 col1,col2
(1 row)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.