Why postgres won't map columns correctly

Question

I have this query to update another table but getting error.

With CTE AS
(Select employer_id, employee_id, start_date, end_date, hourly_rate, end_date - start_date AS time_worked
 FROM payroll_timesheet)
INSERT INTO payroll_timelog 
SELECT * FROM CTE
WHERE employee_id = 1;

I get this error:

ERROR:  column "employee_id" is of type integer but expression is of type timestamp with time zone
LINE 5: SELECT employer_id, employee_id, start_date, end_date, hourl...
                                         ^
HINT:  You will need to rewrite or cast the expression.

It seems to be mapping employee_id with start_date.

Or is there a syntax error in the query?

Tim Biegeleisen · Accepted Answer · 2020-10-14 02:48:57Z

3

Your current error is an excellent reason for why you should always explicitly list out the column names of the target table in an insert. You should have used something like this:

WITH cte AS (
    SELECT employer_id, employee_id, start_date, end_date, hourly_rate,
           end_date - start_date AS time_worked
    FROM payroll_timesheet
)

INSERT INTO payroll_timelog (employer_id, employee_id, start_date, end_date, hourly_rate,
                             time_worked)
SELECT employer_id, employee_id, start_date, end_date, hourly_rate, time_worked
FROM cte
WHERE employee_id = 1;

You might have to change the column names of the target payroll_timelog table to match. Your current error is happening because Postgres is using the default column order, in the absence of your specifying the column order you want.

answered Oct 14, 2020 at 2:48

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why postgres won't map columns correctly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related