Re: referencing other INSERT VALUES columns inside the insert

On 16 November 2015 at 15:48, David G. Johnston <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;

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