For table `mailing_list` make new column to identify listener, use integer that increments automatically

[zipperer]

From comment

During this meeting, we noticed that the listenerid column in the mailing_list table is a custom identifier. The custom identifier uses first initial, last initial, abbreviation for state / geography, and possibly other information. We will add another column to use as an identifier. For now all the new column listenerid_new. For column listenerid we will use a unique integer that increments automatically -- i.e. for postgres a SERIAL.

[zipperer]

I record here one approach to do this:

• make a column mailing_list.listenerid_new with type integer that increments automatically
  • ALTER TABLE mailing_list ADD COLUMN listenerid_new SERIAL PRIMARY KEY;
• in other tables that use value for mailing_list.listenerid, either
  • make new column for listenerid_new, or
  • edit in place values for other_table.listenerid
• if make new column for listenerid_new, then populate with values from mailing_list, then e.g.
  • UPDATE other_table SET listenerid_new = mailing_list.listenerid_new FROM mailing_list WHERE other_table.listenerid = mailing_list.listenerid;
• if edit in place values for other_table.listenerid, then e.g.
  • UPDATE other_table SET listenerid = mailing_list.listenerid_new FROM mailing_list WHERE other_table.listenerid = mailing_list.listenerid;
• if edit in place values for other_table.listenerid, then drop/rename column mailing_list.listenerid and rename mailing_list.listenerid_new to mailing_list.listenerid, e.g.
  • ALTER TABLE mailing_list DROP COLUMN listenerid;
  • ALTER TABLE mailing_list RENAME COLUMN listenerid_new TO listenerid;
• once other_table uses new values, can add constraint (in particular, foreign key constraint) to other_table that those values refer to mailing_list, e.g.
  • ALTER TABLE other_table ADD FOREIGN KEY (listenerid) REFERENCES mailing_list(listenerid); -- or (listenerid_new) depending on whether renamed or not

After these steps:

• other tables have column with integer that identifies listener
• mailing_list has column with integer that identifies listener
• these integers relate the tables
• mailing_list has primary key
• other tables have foreign key to mailing_list

Alternative choices within this approach: either

• keep column listenerid, make a new column, both columns stay, and relate new columns in different tables, or
• keep only one column that identifies listener, and delete column with old scheme for identifying listener (i.e. delete column with values like ASTN1)

[zipperer]

Alternative choices within this approach: either

• keep column listenerid, make a new column, both columns stay, and relate new columns in different tables, or
• keep only one column that identifies listener, and delete column with old scheme for identifying listener (i.e. delete column with values like ASTN1)

We will probably keep both columns.

[zipperer]

During #3 , we created the new column. We have not yet made it

PRIMARY KEY

We have not yet imported data into other tables that use listenerid, so we have not yet:

• added a column to those tables
• populated that column in those tables
• made that new column a FOREIGN KEY / REFERENCES the listenerid_new in mailing_list

[zipperer]

We have not yet imported data into other tables that use listenerid, so we have not yet:

• added a column to those tables
• populated that column in those tables
• made that new column a FOREIGN KEY / REFERENCES the listenerid_new in mailing_list

We have now done this with table offerings.