Re: referencing other INSERT VALUES columns inside the insert

Поиск
Список
Период
Сортировка
От Geoff Winkless
Тема Re: referencing other INSERT VALUES columns inside the insert
Дата
Msg-id CAEzk6feW9qH5BKHsNt2thN0bE4yuVKtfSM7ix1MQF9wiweFpuQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: referencing other INSERT VALUES columns inside the insert  ("David G. Johnston" <david.g.johnston@gmail.com>)
Ответы Re: referencing other INSERT VALUES columns inside the insert  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: referencing other INSERT VALUES columns inside the insert  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список pgsql-general
Дерево обсуждения
On 16 November 2015 at 15:48, David G. Johnston <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;

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

В списке pgsql-general по дате отправления:

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: pg_restore encounter deadlock since PostgreSQL bringing up
Следующее
От: Albe Laurenz
Дата:
Сообщение: Re: pg_restore encounter deadlock since PostgreSQL bringing up