172

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.

254

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

3
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)
-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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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