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.
Best Answer
Column order does matter so if (and only if) the column orders match you can for example:
Or if they don't match you could for example:
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:
dbfiddle here