The following worked with version 6.5 before 4/5/99 but now fails (I pull
new 6.5 source last night):

insert into si_tmpVerifyAccountBalances select invoiceid+3, memberid, 1,
TotShippingHandling from InvoiceLineDetails where TotShippingHandling <> 0
and InvoiceLinesID <= 100 group by invoiceid+3, memberid,
TotShippingHandling;
ERROR:  INSERT has more expressions than target columns

The following works even though the select list does not match the table
being inserted into (I eliminated a column, the literal 1):

insert into si_tmpVerifyAccountBalances select invoiceid+3, memberid,
TotShippingHandling from InvoiceLineDetails where TotShippingHandling <> 0
and InvoiceLinesID <= 100 group by invoiceid+3, memberid,
TotShippingHandling;
INSERT 0 0

The about statement should have inserted a few thousand records.

The following works (this has an aggregation function while the other insert
statements don't) :

insert into si_tmpVerifyAccountBalances select 2, memberid, categoriesid,
1::numeric * sum(InvAmount) from InvoiceLineDetails group by memberid,
categoriesid;

Here is a description of the table:

\d si_tmpVerifyAccountBalances
Table    = si_tmpverifyaccountbalances
+----------------------------------+----------------------------------+-----
--+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-----
--+
| type                             | int4 not null                    |
4 |
| memberid                         | int4 not null                    |
4 |
| categoriesid                     | int4 not null                    |
4 |
| amount                           | numeric                          |
var |
+----------------------------------+----------------------------------+-----
--+
Index:    si_tmpverifyaccountbalances_pke

InvoiceLineDetails is a view but I have also this with similar problems when
using a physical table.  Is a hidden column finding its way into the select
list?  If is use a group by, do I need to have an aggregation function?  Any
one work on portions of the code recently (last 2-3 weeks) that could be
causing this condition?  Any help would be greatly appreciated.

Thanks, Michael



Thu, 29 Apr 1999 15:46:11 -0500
Michael J Davis <michael.j.davis@tvguide.com>
Problem with insert into select from using aggregation