How to insert values into a table from a select query in PostgreSQL?

Question

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.

Answer 1

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;

dbfiddle here

• Alexis Wilke: Yeah, especially with Postgres which by default has a way to handle columns that is likely to make it not match. I've run in that issue.
• Randall: So the subtle thing here is that you don't put the select inside parentheses. I was trying the syntax here, but kept using parens until I noticed that. EDIT: whoops, misspoke. It works with or without enclosing parentheses; I still had the values keyword in there that was throwing my error.

Answer 2

INSERT INTO test_import_two (name, name1, name2) 
(SELECT name, name1, name2 FROM test_import_one WHERE id = 2)

For same table

INSERT INTO test_import_three (id1, name1, name2) 
(SELECT 216 ,name1, name2 FROM test_import_three WHERE id = 4)

Answer 3

I needed to insert new rows with data from 2 different tables, I was pleased that this worked:

insert into new_table (const_col, a_col, b_col)
select some_constant, a.col, b.col from a_table a, b_table b

• Colin 't Hart: I hope you meant to do a Cartesian product.

Answer 4

INSERT INTO gate_pass(
 site_id, gate_pass_element, sequence_no, createdby, createddate, lastmodifiedby, lastmodifieddate)
SELECT 1, gatepasselement, 3, 1,now(),1,now() 
FROM unnest(string_to_array('Bhushan,Amol,pallavi', E',')) as gatepasselement;

• Randall: Not sure how this is supposed to address the Original Post. As the SQL is, some context and explanation of why this is useful to the OP is really needed. Without ay additional info, this post will almost certainly not get voted up.
• Enric Naval: Useful for me because I am working with arrays.