How to copy records which do not exist in the target table-CodePudding

I create a temporary table which I fill with external data.

CREATE TEMP TABLE tmp AS SELECT * FROM saireco.employee LIMIT 0;

Next, I want to copy all records which exist in the temporary table to the target table.

INSERT INTO saireco.employee
SELECT *
FROM tmp t
LEFT JOIN saireco.employee e USING ("employeeID")
WHERE e."employeeID" IS NULL;

The results in an error

ERROR: INSERT has more expressions than target columns LINE 2: SELECT *

The reason is that SELECT * not only results the columns from the temporary table, but also the columns from the target table resulting in more columns than the target table contains.

How can I only return the columns from the temporary table?

CodePudding user response：

Your join combined with select * returns all columns of both tables, not only those from the tmp table.

One way to solve it, is to explicitly specify the target columns and those in the source:

insert into saireco.employee (col1, col2, col2)
select t.col1, t.col2, t.col3
from tmp t
  LEFT JOIN saireco.employee e USING ("employeeID")
WHERE e."employeeID" IS NULL;

Or as both tables have the same structure (including the order of the columns), you can use select t.* to only get the columns of the tmp table:

insert into saireco.employee 
select t.*
from tmp t
  LEFT JOIN saireco.employee e USING ("employeeID")
WHERE e."employeeID" IS NULL;

Alternatively you can use a NOT EXISTS that doesn't increase the number of columns in the output:

insert into saireco.employee 
select *
from tmp t
where not exists (select * 
                  from saireco.employee e 
                  where employeeID" t."employeeID");

But specifying the target columns and and specifying the output columns is good coding practice and should also be done in the second solution.

CodePudding user response：

In general, you should use t.* to return the columns of the table t

INSERT INTO saireco.employee
SELECT t.*
FROM tmp t
LEFT JOIN saireco.employee e USING ("employeeID")
WHERE e."employeeID" IS NULL;

But if the primary key column in the employee table is IDENTITY , you must specify the column names explicitly because the IDENTITY column will not accept the insert , and the code will be better clear Exp:

 INSERT INTO saireco.employee
 ( employeeName ,  phone , .....)
 SELECT t.employeeName, employee.phone ....
 FROM tmp t
 LEFT JOIN saireco.employee e USING ("employeeID")
 WHERE e."employeeID" IS NULL;