Temp table column type error when values are null

Question

I am using a temp table to hold some data while I tidy up a database table. Narrowed down, it goes something like this:

CREATE TEMP TABLE fw_temp AS SELECT time, version, device_id FROM fw_status;
DELETE FROM fw_status;
INSERT INTO fw_status SELECT * FROM fw_temp

On my INSERT statement I get some.. interesting behaviour when the values in the device_id column are null:

SQL Error [42804]: ERROR: column "device_id" is of type bigint but expression is of type text Hint: You will need to rewrite or cast the expression.

As the error message may lead on, device_id is indeed bigint type in the table, but appears to have lost the type when we select from the temp table.

Making a general select,

CREATE TEMP TABLE fw_temp AS SELECT * FROM fw_status;
DELETE FROM fw_status;
INSERT INTO fw_status SELECT * FROM fw_temp

interestingly does not cause this error.

Now, the real temp table is a bit more complex, so sadly going with SELECT * is not an option. How can I go about this error?

Maybe INSERT INTO fw_status (time, version, device_id) SELECT * FROM fw_temp;. Misplaced and missing column values? — Stefanov.sm
– Stefanov.sm, Commented Oct 21, 2022 at 11:56
Funny thing, I figured the same and saw your comment while posting the fix. Any idea what happens behind the scene? I notice column order does not appear to be maintained on the general select — Bjarke Moholt
– Bjarke Moholt, Commented Oct 21, 2022 at 12:01
You were trying to insert into the wrong columns of fw_status - the first three - regardless of what column names the select statement is producing. — Stefanov.sm
– Stefanov.sm, Commented Oct 21, 2022 at 12:04

Bjarke Moholt · Accepted Answer · 2022-10-21 11:59:15Z

0

Changing to

CREATE TEMP TABLE fw_temp AS SELECT time, version, device_id FROM fw_status;
DELETE FROM fw_status;
INSERT INTO fw_status (time, version, device_id) SELECT time, version, device_id FROM fw_temp

resolved the issue. Apparently, you can mix general select and general insert or specific select and specific insert, but not general select and specific insert. I am still puzzled as to what is going on. Any enlightenment would be appreciated

answered Oct 21, 2022 at 11:59

Bjarke Moholt

3331 gold badge5 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Temp table column type error when values are null

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