From: | Tore Halset <halset(at)pvv(dot)ntnu(dot)no> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | insert into ... select ... and column order |
Date: | 2008-01-15 10:29:16 |
Message-ID: | 4E53F171-06BC-4049-8F68-C39C2E07BA53@pvv.ntnu.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-01-15 10:39:12 | Re: insert into ... select ... and column order |
Previous Message | Michael Meskes | 2008-01-15 09:05:25 | Re: ECPG problem with 8.3 |