Tore Halset wrote:
Hello.
One of our users tried a "insert into ... select ..." that gave a
strange error message. After digging into the issue, the problem seem to
be that the order of the columns in the select statement must match the
table definition. Here is a way to reproduce this case.
insert into dest_2 select USER_ID, PRODUCT_ID, min(PERMIT_START_DATE) as
PERMIT_START_DATE, max(PERMIT_END_DATE) as PERMIT_END_DATE from source
group by USER_ID, PRODUCT_ID;
Why does the column order matter when the subselect has all the correct
column names?
The names do not matter - the database won't try to match up the names.
Think about it in comparison with INSERT ... VALUES - it's the same layout.
What you need to do is supply the column-names for the insert (this is a
good idea anyway - it makes it explicit what is going on and will cope
better if you change the definition of dest_2).
INSERT INTO dest_2 (permit_end_date, permit_start_date, ...)
SELECT <column for permit_end_date>, <column for permit_start_date>, ...
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
