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?
You could list out the non-
gid columns in your
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
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)
Is there a way without writing insert which manually lists all the columns except the first – serial?
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.