How to insert values into a table from a select query in PostgreSQL?
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. 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.
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;