[GENERAL] insert into ... select ... and column order
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.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] insert into ... select ... and column order
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
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] insert into ... select ... and column order
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 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 ... Thanks to both of you for this answer. - Tore. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] insert into ... select ... and column order
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
