Insert records from one table to other with default serial

Question

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?

Answer 1

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)

Answer 2

Is there a way without writing insert which manually lists all the columns except the first - serial?

Basically, no.
At some point, you have to issue an insert statement, telling it which fields you're supplying and with a select statement to get the values to put into those fields.

How you generate that insert statement is up to you.
The information_schema is there to "describe" your database in standardised terms - you could query that and mash the output about a bit to create the column list.

If this is something you do regularly, you might consider creating a view on the second table (omitting the serial column), grant insert permission on that view - yes, you can do that - and insert your data through that.