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

От:

Richard Huxton <dev@archonet.com>

Дата:

15 января 2008 г. в 06:39:23

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 , , ...

-- 
   Richard Huxton
   Archonet Ltd

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

От:

Tino Wildenhain <tino@wildenhain.de>

Дата:

15 января 2008 г. в 08:28:18

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.
...
> 
> Why does the column order matter when the subselect has all the correct 
> column names?

When I noticed this long ago I just blamed it to SQL standards ;)
I do admit I never checked with the documentation.

And yes the names of the columns do not matter, just use the correct
order - either default or better:

INSERT INTO ... ( a,b,c ) SELECT a,b,c FROM ....

since new columns or reorder would not disturb your insert.

Regards
Tino

insert into ... select ... and column order

От:

Tore Halset <halset@pvv.ntnu.no>

Дата:

15 января 2008 г. в 06:29:28

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.

-- a source table and some content
create table source (USER_ID varchar(10), PRODUCT_ID varchar(8),  
PERMIT_START_DATE timestamp, PERMIT_END_DATE timestamp);
insert into source (USER_ID, PRODUCT_ID, PERMIT_START_DATE,  
PERMIT_END_DATE) values ('a', 'b', now(), now());
insert into source (USER_ID, PRODUCT_ID, PERMIT_START_DATE,  
PERMIT_END_DATE) values ('c', 'd', now(), now());
insert into source (USER_ID, PRODUCT_ID, PERMIT_START_DATE,  
PERMIT_END_DATE) values ('e', 'f', now(), now());

-- 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. message:  
"column "permit_end_date" is of type timestamp without time zone but  
expression is of type character varying".
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?

Regards,
  - Tore.

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

От:

"Albe Laurenz" <laurenz.albe@wien.gv.at>

Дата:

15 января 2008 г. в 07:16:59

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  is omitted, then an 
  that identifies all columns of T in the ascending sequence of
  their ordinal positions within T is implicit."

You want an explicit :

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

Yours,
Laurenz Albe

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

От:

Tore Halset <halset@pvv.ntnu.no>

Дата:

15 января 2008 г. в 07:44:50

On Jan 15, 2008, at 12:16 , Albe Laurenz wrote:

> Because the SQL standard says so.
>
> ISO/IEC 9075-2, Chapter 14.8, Syntax Rule 9:
>
> "If the  is omitted, then an 
>  that identifies all columns of T in the ascending sequence of
>  their ordinal positions within T is implicit."
>
> You want an explicit :
>
> INSERT INTO dest_2
> (user_id, product_id, permit_start_date, permit_end_date)
> SELECT ...

Thanks to both of you for this answer.

  - Tore.