Re: referencing other INSERT VALUES columns inside the insert

On 11/17/2015 01:14 AM, Geoff Winkless wrote:
> On 16 November 2015 at 15:48, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>>wrote:
>
> ​You don't need WITH to accomplish this...
>
> INSERT INTO test (c1, c2, c3)
> SELECT c1, c2, c1 * c2
> FROM ( VALUES (3, 7) ) vals (c1, c2);
>
> David J.
>
>
> ​Oh I see, so it's the ability to use VALUES in place of a SELECT,
> really. I suppose I could equally have done
>
> INSERT INTO test (c1,c2,c3) SELECT *, c1*c2 from (SELECT 3 c1,7 c2) tmp
>
> Frustratingly, it still doesn't quite achieve what I needed (I
> appreciate that was me not describing the full problem, mainly because I
> hadn't realised that the code relied on it): on MySQL, I can do
>
> INSERT INTO test (c1, c2, c4, c5) VALUES (3, 7, c1*c2, c4*c3)
>
> and even though c3 isn't defined in the column list it will use the
> default column value for the c4 calculation, while for c5 it uses the
> value calculated for c4 in the previous field. I get that that isn't
> defined ANSI behaviour and don't think there's a way to do either of
> these things in PG, so I've fallen back to doing a single transaction
> with one INSERT with the static values followed by one update for each
> calculated value (obviously with a full PK for the WHERE clause...)
>
> So
>
> INSERT INTO test (c1, c2) VALUES (3, 7); UPDATE test SET c4=c1*c2 WHERE
> c1=3; UPDATE test SET c5=c4*c3 WHERE c1=3;

Could the above not be shortened to?:

INSERT INTO test (c1, c2) VALUES (3, 7); UPDATE test SET c4=c1*c2,
c5=c1*c2*c3 WHERE c1=3;

Also from your first post:
"To be clear, the SQL is generated dynamically based on data, ..."

Would it not be easier to just calculate the values in whatever program
is generating the SQL and just supply the calculated values in the INSERT?

Lastly, and this is more about my curiosity then anything else, why
calculate the values at all? You have the original values c1 and c2 the
others can be derived at any time. I am just interested in what the
benefit is to calculate them on initial data entry?

>
> Not as neat (nor probably as efficient), and a bit of a pain to have to
> include the PK each time, but does at least achieve what I need.
>
> Thanks again for the insights, always good to learn something :)
>
> Geoff

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com