postgresql insert with multiple selects

Question

If this is how I insert one record:

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')

And this is how I insert another record:

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')

How do I insert them both in the same insert statement? Note, that in reality, there will be hundreds of thousands of inserts, not just two, so efficiency is very important.

In other words, from record to record in the insert company_id, fund_val, fiscal_prd_end_date change. bbg_pulls_idstays the same. But fund_entries_id is found via fiscal_prd_end_date in the fund_entries table for each insert.

My tables look like this:

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
)

I am currently considering using WITH statements for the selects and access the fund_entries_id from the WITH queries. Not sure that is the cleanest way to do that.

Or maybe something like this: One INSERT with multiple SELECT

Not sure how to implement that though.

Thanks!

Why these hard coded values for individual `company_id` and dates? It seems kind of strange. — JNevill, May 11 '18 at 18:41
Yes, it is strange, but that is what I am working with. Can you help? — mountainclimber11, May 11 '18 at 18:47
I mean,... you can UNION all of your Hundreds or thousands of SELECT statements together? That's going to be garbage performance, but better than running `INSERT... SELECT...` hundreds/thousands of times. Can't really offer more help than that. Is there any logic to these hardcoded values? Could they be stored in a seperate table that could be looked up for a given `company_id`/date combination so this could be done in a single select statement.... ? — JNevill, May 11 '18 at 18:48
This is part of an ETL process. Part of what I am doing is putting them into a table that can be looked up. That is what I am doing. — mountainclimber11, May 11 '18 at 18:57

klin · Accepted Answer · 2018-05-11T19:52:57.387

3

You can prepare the data to be inserted in a CTE, 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)

edited May 11 '18 at 19:52

answered May 11 '18 at 18:59

klin

112,967
15
204
232

It took a long time to grok this example and adapt it to my needs. But the core mechanism works and is useful. Thanks. – Mike Finch Mar 08 '22 at 01:41

postgresql insert with multiple selects

1 Answers1

Linked