(One table) Insert rows with Sequence

Question

This question is the continuation of this one.

I have the following table egr:

+---------+---------+------------+
|  egrid  |  offid  |  groupid   |
+---------+---------+------------+
|    1000 |       1 | 101        |
|    1001 |       1 | 202        |
|    1002 |       2 | 202        |
|    1003 |       2 | 404        |
+---------+---------+------------+

Note that egrid is a sequence.

I would like to insert missing groupids that the offid 2 does not have (compared to offid 1). Result would be:

+---------+---------+------------+
|  egrid  |  offid  |  groupid   |
+---------+---------+------------+
|    1000 |       1 | 101        |
|    1001 |       1 | 202        |
|    1002 |       2 | 202        |
|    1003 |       2 | 404        |
|    1004 |       2 | 101        |   --> new row to insert
+---------+---------+------------+

My try below, based on the answer of my other question (not working). The problem I have is that the sequence is incremented with the select statement. But if there are 555 rows with offid = 1, it will be incremented 555 times although in the end, only a few will be inserted. I am looking for a nicer way to do it.

INSERT INTO egr (egrid, offid, groupid)
  SELECT nextval('seq_egrid'), 2, groupid
    FROM egr
    WHERE offid = 1
  EXCEPT
  SELECT egrid, 2, groupid
    FROM egr
    WHERE offid = 2
ON CONFLICT DO NOTHING;

wheresmycookie · Accepted Answer · 2019-06-04T10:41:36.253

1

To only increment nextval in the cases where rows are added you could try something like this:

INSERT INTO egr (egrid, offid, groupid)
  SELECT nextval('seq_egrid'), 2, g FROM (
    SELECT groupid as g
      FROM egr
      WHERE offid = 1
    EXCEPT
    SELECT groupid
      FROM egr
      WHERE offid = 2
  ) AS t
ON CONFLICT DO NOTHING;

edited Jun 04 '19 at 10:41

answered Jun 04 '19 at 09:54

wheresmycookie

683
3
16
39

Modifying the schema is not an option unfortunately. – Stephane B. Jun 04 '19 at 10:17
@StephaneB. does something like this help? – wheresmycookie Jun 04 '19 at 10:41

score 0 · Answer 2 · answered Jun 04 '19 at 11:58

You can generate the rows using:

select o.offid, g.groupid
from (select distinct offid from egr) as o cross join
     (select groupid from egr where offid = 1) g left join
     egr
     on egr.offid = o.offid and
        egr.groupid = g.groupid
where egr.offid is null;

I would not use a sequence for the first column, just a serial value, so I would write:

insert into egr (offid, groupid)
    select o.offid, g.groupid
    from (select distinct offid from egr) as o cross join
         (select groupid from egr where offid = 1) g left join
         egr
         on egr.offid = o.offid and
            egr.groupid = g.groupid
    where egr.offid is null;

And let the database assign the first value. Of course, if you use using a sequence, you can be explicit:

insert into egr (egrid, offid, groupid)
    select nextval('seq_egrid'), o.offid, g.groupid
    from (select distinct offid from egr) as o cross join
         (select groupid from egr where offid = 1) g left join
         egr
         on egr.offid = o.offid and
            egr.groupid = g.groupid
    where egr.offid is null;

(One table) Insert rows with Sequence

2 Answers2