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;

Aargh, just realized I am not seeing where c3 comes from.

>
> 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