postgresql插入多个选择

如果这是我插入一条记录的方式：

insert into fund_data 
    (fund_entries_id
     ,fund_val
     ,bbg_pulls_id)
(select fe.id, 1,20
    from
        fund_entries fe
    where
         fe.company_id = 399
        and fe.fiscal_prd_end_date = '2016-09-30')

这就是我插入另一条记录的方式：

insert into fund_data 
    (fund_entries_id
     ,fund_val
     ,bbg_pulls_id)
(select fe.id, 567,20
    from
        fund_entries fe
    where
         fe.company_id = 5
        and fe.fiscal_prd_end_date = '2016-09-28')

如何将它们插入到同一个插入语句中？请注意，实际上，会有数十万个插入，而不仅仅是两个，因此效率非常重要。

换句话说，插入中从记录到记录company_id, fund_val, fiscal_prd_end_date改变。bbg_pulls_id保持不变。但fund_entries_id是通过找到的fiscal_prd_end_date in the fund_entries每个插入的表。

我的桌子看起来像这样：

CREATE TABLE public.fund_data
(
    id bigint NOT NULL DEFAULT nextval('fund_data_id_seq'::regclass),
    fund_entries_id integer NOT NULL,
    fund_val numeric,
    bbg_pulls_id integer,
    wh_calc_id integer,
    CONSTRAINT fund_data_pkey PRIMARY KEY (id),
    CONSTRAINT fund_data_fund_entries_id_bbg_pulls_id_key UNIQUE (fund_entries_id, bbg_pulls_id),
    CONSTRAINT fund_data_fund_entries_id_wh_calc_id_key UNIQUE (fund_entries_id, wh_calc_id),
    CONSTRAINT fund_data_bbg_pulls_id_fkey FOREIGN KEY (bbg_pulls_id)
        REFERENCES public.bbg_pulls (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fund_data_fund_entries_id_fkey FOREIGN KEY (fund_entries_id)
        REFERENCES public.fund_entries (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fund_data_wh_calc_id_fkey FOREIGN KEY (wh_calc_id)
        REFERENCES public.wh_calc (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE,
    CONSTRAINT fund_data_check CHECK ((wh_calc_id IS NULL) <> (bbg_pulls_id IS NULL)),
    CONSTRAINT fund_data_check1 CHECK (NOT (wh_calc_id IS NULL AND fund_val IS NULL))
)

CREATE TABLE public.fund_entries
(
    id integer NOT NULL DEFAULT nextval('fund_entries_id_seq'::regclass),
    fiscal_prd_end_date date NOT NULL,
    company_id integer NOT NULL,
    ern_dt_id integer NOT NULL,
    CONSTRAINT fund_entries_pkey PRIMARY KEY (id),
    CONSTRAINT fund_entries_company_id_fiscal_prd_end_date_key UNIQUE (company_id, fiscal_prd_end_date),
    CONSTRAINT fund_entries_ern_dt_id_key UNIQUE (ern_dt_id),
    CONSTRAINT fund_entries_company_id_fkey FOREIGN KEY (company_id)
        REFERENCES public.company (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fund_entries_ern_dt_id_fkey11 FOREIGN KEY (ern_dt_id)
        REFERENCES public.ern_dt (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

我目前正在考虑使用WITH用于选择和访问的语句fund_entries_id来自WITH查询。不确定这是最干净的方法。

或者也许是这样的：

但不确定如何实施。

Thanks!

您可以准备要插入的数据CTE, https://www.postgresql.org/docs/current/static/queries-with.html e.g.:

with rows_to_insert(fund_val, bbg_pulls_id, company_id, fiscal_prd_end_date) as (
values
    (  1, 20, 399, '2016-09-30'::date),
    (567, 20,   5, '2016-09-28')
)

insert into fund_data (fund_entries_id, fund_val, bbg_pulls_id)
select fe.id, ri.fund_val, ri.bbg_pulls_id
from fund_entries fe
join rows_to_insert ri using (company_id, fiscal_prd_end_date)