up vote 5 down vote favorite
share [fb]

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int).

Now I want to insert a row into items_ver from items. Is there any short SQL-syntax for doing this?

I have tried with:

INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item_id = 2);

but I get a syntax error:

ERROR:  syntax error at or near "select"
LINE 1: INSERT INTO items_ver VALUES (SELECT * FROM items WHERE item...

I now tried:

INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

It worked better but I got an error:

ERROR:  column "item_group" is of type integer but expression is of type 
character varying
LINE 1: INSERT INTO items_ver SELECT * FROM items WHERE item_id = 2;

This may be because the columns are defined in a different order in the tables. Does the column order matter? I hoped that PostgreSQL match the column names.

link|improve this question
95% accept rate
This is a casting error (you can't insert int into varchar), is it working when specifing the columns and casting them where needed? – DrColossos May 27 '11 at 8:59
@DrColossos: The columns with the same name has the same type. But the columns are defined in a different order in the two tables. – Jonas May 27 '11 at 9:12
Ah, misread that. – DrColossos May 27 '11 at 9:20
feedback

1 Answer

up vote 9 down vote accepted

Column order does matter so if (and only if) the column orders match you can for example:

insert into items_ver
select * from items where item_id=2;

Or if they don't match you could for example:

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read

There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:

insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;
link|improve this answer
+1 for recommending to always specify the columns – a_horse_with_no_name May 27 '11 at 9:35
Thanks - I've added a few more reasons why, no doubt there are more :) – Jack Douglas♦ May 27 '11 at 9:53
feedback

Your Answer

 
or
required, but never shown

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