Problem with insert into select from using aggregation
От | Michael J Davis |
---|---|
Тема | Problem with insert into select from using aggregation |
Дата | |
Msg-id | 93C04F1F5173D211A27900105AA8FCFC14544D@lambic.prevuenet.com обсуждение исходный текст |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: