Problem with insert into select from using aggregation

From: Michael J Davis <michael(dot)j(dot)davis(at)tvguide(dot)com>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Problem with insert into select from using aggregation
Date: 1999-04-29 20:46:11
Message-ID: 93C04F1F5173D211A27900105AA8FCFC14544D@lambic.prevuenet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 1999-04-29 22:33:46 Re: [HACKERS] How do I get the backend server into gdb?
Previous Message Brian P Millett 1999-04-29 19:14:49 Re: SIGBUS in AllocSetAlloc & jdbc