Add a primary key to an existing table

Recently we started to move some old legacy databases from our data center to AWS. We are moving them to PostgreSQL RDS. To reduce the cut-over time we are utilizing Amazon’s Database Migration Service (DMS). To properly replicate a table and keep it in sync the table needs a primary key or a unique key. We ran into several tables with no primary or unique keys that we needed to solve. Adding a primary key fell into a couple of different categories.

Add a primary key constraint

The first issue we ran into was a table where there was a primary key column. The default value was a sequence, but there was simply no primary key constraint. Without the constraint there is always the possibility there are duplicate values, but fortunately, that was not the case. The solution was easy, just add the primary key constraint.

ALTER TABLE public.my_table ADD CONSTRAINT my_table_pkey PRIMARY KEY (id);

Add a new primary key column

The next issue was a table that did not have a primary key column so one needed to be added. On relatively modern versions of PostgreSQL, this is a very simple fix. Keep in mind this process involves taking an exclusive lock on the table and can take a long time on a very large table. The statement below will:

  • Add a not null ID column
  • Create a sequence owned by the table and column
  • Make the sequence the ID column’s default value
  • Update every row in the table calling nexval on the sequence
ALTER TABLE public.my_table ADD COLUMN id SERIAL PRIMARY KEY;

Change a primary key sequence’s ownership

The last issue we ran into was a table that technically had a primary key, but the setup was not correct. Our application was calling nextval on the primary key’s sequence, but the table itself was not related to the sequence. This creates a couple of maintenance issues. To fix this we need to alter the column so the sequence is the default value and then change the ownership of the sequence to the column.

BEGIN;

LOCK TABLE public.my_table IN ACCESS EXCLUSIVE MODE;

ALTER TABLE public.my_table ALTER COLUMN id
SET DEFAULT nextval('public.my_table_id_seq'::regclass);

ALTER SEQUENCE public.my_table_id_seq
OWNED BY public.my_table.id;

COMMIT;

Matt McGuire

Proven technology leader with 25+ years of experience in the installation, administration, development, design, and maintenance of various enterprise data related systems.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.