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_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.

  • 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

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

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM