PostgreSQL Add Primary Key To Existing Column

As a database developer, I recently encountered a situation in which I needed to add a primary key to an existing column in PostgreSQL.

In this comprehensive article, I will explain various methods for adding a primary key constraint to an existing column in PostgreSQL, providing multiple examples and best practices.

PostgreSQL Add Primary Key To Existing Column

Before adding the primary key to the existing column, let us discuss the prerequisites needed here.

Prerequisites for Adding a Primary Key

Before adding a primary key to an existing column, ensure:

1. The target column contains unique values
2. The column has no NULL values
3. You have sufficient privileges on the database
4. You’ve considered the impact on existing applications

Now, let us discuss all the approaches individually.

Method 1: Using ALTER TABLE to Add a Primary Key

The most straightforward approach to add a primary key to an existing column is to use the ALTER TABLE command.

Syntax

ALTER TABLE table_name 
ADD PRIMARY KEY (column_name);

Example

Let’s say we have a customers Table that was created without a primary key in the customer_id column. As a first step, we can use the following query to check if our column meets the requirements.

SELECT customer_id, COUNT(*) 
FROM customers 
GROUP BY customer_id 
HAVING COUNT(*) > 1;

After executing the above query, I got the expected output as shown in the screenshot below.

Now that the query returns no rows, we can proceed to adding the primary key using the query below.

ALTER TABLE customers
ADD PRIMARY KEY (id);

After executing the above query, I got the expected output as shown in the screenshot below.

Now, let us execute a select query, and you will be able to see that the Primary key has been created successfully, as shown in the screenshot below.

Method 2: Adding a Primary Key with Minimal Locking

Adding a primary key for production databases with larger tables can lock the table and impact performance. To avoid this scenario, you can use the steps below. First, create a unique index concurrently (which doesn’t lock the table for writes) using the below query.

CREATE UNIQUE INDEX CONCURRENTLY idx_customer_id 
ON customers (customer_id);

After executing the above query, I got the expected output as shown in the screenshot below.

Now, add the primary key constraint using the index with the query below.

ALTER TABLE customers 
ADD CONSTRAINT customers_pkey PRIMARY KEY 
USING INDEX idx_customer_id;

The primary key has been added successfully as shown in the screenshot below.

Method 3: Creating a New Primary Key Column

If modifying the existing column isn’t feasible, you can add a new primary key column using the query below.

ALTER TABLE customers ADD COLUMN customer_pk SERIAL;

I got the expected output as shown in the screenshot below.

Now, using the below query to make it the primary key.

ALTER TABLE customers ADD PRIMARY KEY (customer_pk);

Comparing Different Primary Key Addition Methods

Here’s a quick comparison of the methods we’ve discussed:

Method	Pros	Cons	Best For
Simple ALTER TABLE	Quick, straightforward	Locks the table	Development, small tables
CONCURRENTLY with INDEX	Minimal locking	Takes longer	Production, large tables
Fixing data first	Addresses data quality	More complex	Tables with data issues
New primary key column	Preserves existing data	Requires app changes	Legacy systems

Verify the Primary Key After Addition

After adding your primary key, it is essential to verify that it is working as expected using the query below.

SELECT * FROM information_schema.table_constraints 
WHERE table_name = 'customers' AND constraint_type = 'PRIMARY KEY';

After executing the above query, I got the expected output as shown in the screenshot below.

Conclusion

Adding a primary key to an existing column in PostgreSQL is a common task for a developer. By following the methods outlined in this article, you can easily implement this in your database.

You may also like the following articles.

• PostgreSQL Add Primary Key Multiple Columns
• PostgreSQL add primary key
• PostgreSQL Add Primary Key Autoincrement