Re: insert into ... select ... and column order

Tore Halset wrote:
> 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.
>
> -- two equal tables with different column order
> create table dest_1 (USER_ID varchar(10), PRODUCT_ID varchar(8),
> PERMIT_START_DATE timestamp, PERMIT_END_DATE timestamp);
> create table dest_2 (PERMIT_END_DATE timestamp, PERMIT_START_DATE
> timestamp, PRODUCT_ID varchar(8), USER_ID varchar(10));
>
> -- ok
> insert into dest_1 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;

> -- same sql, but to table with different column order failes.
> 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?

Because the SQL standard says so.

ISO/IEC 9075-2, Chapter 14.8, Syntax Rule 9:

 "If the <insert column list> is omitted, then an <insert column list>
  that identifies all columns of T in the ascending sequence of
  their ordinal positions within T is implicit."

You want an explicit <insert column list>:

INSERT INTO dest_2
(user_id, product_id, permit_start_date, permit_end_date)
SELECT ...

Yours,
Laurenz Albe