Let’s say you have a users_no_pk table with a unique email field:
Copy
Ask AI
CREATE TABLE users_no_pk ( email VARCHAR(255) UNIQUE NOT NULL, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL);
To make this table replication-ready, simply promote the unique email to a primary key:
Copy
Ask AI
ALTER TABLE users_no_pk ADD PRIMARY KEY (email);
For tables without any unique identifiers, we can add a new primary key column. Let’s use this example:
Copy
Ask AI
CREATE TABLE no_primary_keys ( key VARCHAR(5), value bool);INSERT INTO no_primary_keys (key, value) VALUES ('foo', true), ('bar', false);
Current table contents:
Copy
Ask AI
postgres=# select * from no_primary_keys; key | value-----+------ bar | f foo | f(2 rows)
Add a new primary key column:
Copy
Ask AI
ALTER TABLE no_primary_keys ADD COLUMN pk SERIAL PRIMARY KEY;-- This automatically:-- 1. Creates a new SERIAL column-- 2. Backfills existing rows with sequential values-- 3. Sets up auto-increment for new rows
After adding the primary key:
Copy
Ask AI
postgres=# select * from no_primary_keys; key | value | pk-----+-------+---- bar | f | 2 foo | f | 1(2 rows)
💡 Pro Tip: Your application code doesn’t need any changes! You can continue inserting data without specifying the primary key:
Copy
Ask AI
INSERT INTO no_primary_keys (key, value) VALUES ('qux', false);
Copy
Ask AI
postgres=# select * from no_primary_keys; key | value | pk-----+-------+---- bar | f | 2 foo | f | 1 qux | f | 3(3 rows)
