On 11/17/2015 01:14 AM, Geoff Winkless wrote:
On 16 November 2015 at 15:48, David G. Johnston <david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx>>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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
