How to copy, change, and insert records in Postgres

Question

In a PostgreSQL DB table, I need to copy a block of records from a prior month, change values in some of the columns, and append the updated records to the table. Details include:

• The key id is configured with nextval to automatically create unique key values
• The target records have '200814' in group_tag
• The new records need '200911' in group_tag
• Several other fields need to be updated as shown in the SELECT

My script so far:

INSERT INTO hist.group_control( 
  id,
  group_tag,
  process_sequence,
  state,
  cbsa_code,
  window_starts_on,
  preceding_group,
  preceding_origin,
  preceding_window_starts_on
)
SELECT id,
       '200911',
       1,
       state,
       cbsa_code,
       '2020-09-11',
       '200814',
       preceding_origin,
       '2020-08-14'
FROM hist.group_control WHERE group_tag='200814';

This generates an error:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "group_control_pkey"
  Detail: Key (id)=(12250) already exists.

Records with key values up to 13008 exist. I would have expected nextval to determine this and start the id value at 13009. I attempted to simply not include id in the statement thinking the nextval function would operate automatically, but that errored as well. Variations on the following have not worked due to the respective errors:

alter sequence group_control_id_seq restart with 13009;
SQL Error [42501]: ERROR: must be owner of relation group_control_id_seq

SELECT setval('group_control_id_seq', 13009, true);
SQL Error [42501]: ERROR: permission denied for sequence group_control_id_seq

Anyone know how to code the main statement to not generate the duplicate key or alternatively, how to tell nextval to start at a value of 13009

Answer 1

It appears your serial, bigserial, or generated by default. Any of these only assign the id column when it is not specified in the insert statement. If you specify the id column Postgres will not assign a key PK. Since you selected the id, Postgres attempted to use what you specified. Solution drop id from the insert statement.

INSERT INTO hist.group_control( 
  group_tag,
  process_sequence,
  state,
  cbsa_code,
  window_starts_on,
  preceding_group,
  preceding_origin,
  preceding_window_starts_on
)
SELECT '200911',
       1,
       state,
       cbsa_code,
       '2020-09-11',
       '200814',
       preceding_origin,
       '2020-08-14'
FROM hist.group_control WHERE group_tag='200814';