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.

  • 31,495
  • 27
  • 59
  • 64

4 Answers4


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

Jack Douglas
  • 38,607
  • 15
  • 97
  • 174
  • 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. – Alexis Wilke Feb 08 '23 at 03:11
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)
  • 30,069
  • 13
  • 96
  • 141
Nirmal Sharma
  • 561
  • 4
  • 2

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
  • 129
  • 1
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;
  • 76,712
  • 14
  • 153
  • 191