Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Nice summary. Where are we on the last item.
It was still broken as of a day or two ago.
I poked at it some more, and concluded that INSERT ... SELECT is pretty
broken when the SELECT includes GROUP BY. I didn't try to delve into
the code though, just experimented with different commands. Here are my
notes:
TEST CONDITION:
CREATE TABLE "si_tmpverifyaccountbalances" ( "type" int4 NOT NULL, "memberid" int4 NOT NULL,
"categoriesid"int4 NOT NULL, "amount" numeric);
CREATE TABLE "invoicelinedetails" ( "invoiceid" int4, "memberid" int4, "totshippinghandling" numeric,
"invoicelinesid" int4);
ACCEPTED:
insert into si_tmpVerifyAccountBalances select invoiceid+3,
memberid, 1, TotShippingHandling from InvoiceLineDetails
group by invoiceid+3, memberid;
NOT ACCEPTED:
insert into si_tmpVerifyAccountBalances select invoiceid+3,
memberid, 1, TotShippingHandling from InvoiceLineDetails
group by invoiceid+3, memberid, TotShippingHandling;
Probably error check is including GROUP BY targets in its count of
things-to-be-inserted :-(. The behavior is quite inconsistent though.
Also, why doesn't the first example get rejected, since
TotShippingHandling is neither GROUP BY nor an aggregate??
regards, tom lane
