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?
Best Answer
You could list out the non-
gid
columns in yourSELECT
:You could also try creating a temporary table, then drop the
gid
column, and then copy that table into the new table: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 yourINSERT
statement: