Thread: referencing other INSERT VALUES columns inside the insert
I know that this is something that can't be done...
CREATE TABLE test (c1 int default 0, c2 int default 0, c3 int default 0);
INSERT INTO test (c1, c2, c3) VALUES (3, 7, c1 * c2);
Is there a known trick to work around it (so that the values inserted into c1 and c2 is referenced back to c3), other than (obviously!) copying the literal values into the VALUES string?
To be clear, the SQL is generated dynamically based on data, so I can't just create a view for the calculated column (it won't always be calculated!).
Thanks!
Geoff
Geoff Winkless wrote:
> I know that this is something that can't be done...
>
> CREATE TABLE test (c1 int default 0, c2 int default 0, c3 int default 0);
> INSERT INTO test (c1, c2, c3) VALUES (3, 7, c1 * c2);
>
> Is there a known trick to work around it (so that the values inserted into c1 and c2 is referenced
> back to c3), other than (obviously!) copying the literal values into the VALUES string?
>
> To be clear, the SQL is generated dynamically based on data, so I can't just create a view for the
> calculated column (it won't always be calculated!).
What about something along these lines:
INSERT INTO test (c1, c2, c3)
(WITH fixed(x1, x2) AS (VALUES (3, 7))
SELECT x1, x2, x1 * x2 FROM fixed);
What about something along these lines:
INSERT INTO test (c1, c2, c3)
(WITH fixed(x1, x2) AS (VALUES (3, 7))
SELECT x1, x2, x1 * x2 FROM fixed);
Genius!
It never occured to me that the with_query parameter could be used that way. Thanks!
Geoff
What about something along these lines:
INSERT INTO test (c1, c2, c3)
(WITH fixed(x1, x2) AS (VALUES (3, 7))
SELECT x1, x2, x1 * x2 FROM fixed);Genius!It never occured to me that the with_query parameter could be used that way. Thanks!
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.
You don't need WITH to accomplish this...INSERT INTO test (c1, c2, c3)SELECT c1, c2, c1 * c2FROM ( 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
On 11/17/2015 01:14 AM, Geoff Winkless wrote: > On 16 November 2015 at 15:48, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.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.klaver@aklaver.com
On 11/17/2015 01:14 AM, Geoff Winkless wrote: > On 16 November 2015 at 15:48, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.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.klaver@aklaver.com
On 11/17/2015 01:14 AM, Geoff Winkless wrote: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;
Well yes, but having to do a (potentially very) complicated parse just to get to that point is a bit of a wasted effort.
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?
Easier how? At the moment I can just pass the derivations straight through to postgres and it does all the evaluation for me. If I do that in the code, I have to implement a complete parser and evaluation engine... so I'd say probably no, it's not :)
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?
I've simplified to show an example. In reality the derivations are significantly more complex and represent business rules, configurable
by a second-party admin.
Aargh, just realized I am not seeing where c3 comes from.
It takes the column's default value, since it's not explicit in the first INSERT.
Geoff
On 11/17/15 8:53 AM, Geoff Winkless wrote: > It takes the column's default value, since it's not explicit in the > first INSERT. Not sure if it would help or not, but you can use pg_get_expr(adbin, adrelid) against pg_attrdef to get the default for a column. That would let you dynamically insert the expression that generates the default value into the VALUES clause. start psql with the -E option and do \d on a table with e default to see this in action. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com