January 14, 2023 By Matthew Rathbone *

When designing a database table in PostgreSQL, it’s important to consider the use of a primary key. A primary key is a column or group of columns that uniquely identifies each row in a table. This ensures that no two rows in the table will have the same values for the primary key columns, which can be useful for organizing and querying data.

Use ALTER TABLE if you forgot to add a primary key

If you’ve already created a table in PostgreSQL and forgot to add a primary key, you can use the ALTER TABLE command to add one. Here’s how:

Make PostgreSQL Fun Again With Beekeeper Studio

Write SQL, create tables, edit data, and have fun doing it! Beekeeper Studio is available for MacOS, Linux, and Windows.

First, open the PostgreSQL shell by typing psql at the command prompt, or opening your database in Beekeeper Studio.

Once you’re connected to the right database, you can use the ALTER TABLE command to add a primary key to an existing table. The general syntax for this command is as follows:

ALTER TABLE tablename ADD PRIMARY KEY (column1, column2, ...);

In this case, tablename is the name of the table that you want to modify, and column1, column2, etc. are the names of the columns that you want to include in the primary key. You can specify one or more columns, depending on your table design.

Adding a primary key to an existing table

For example, if you want to add a primary key to a table named customers that has columns named id, first_name, and last_name, you could use the following ALTER TABLE command:

ALTER TABLE customers ADD PRIMARY KEY (id);

This would create a primary key that consists of the id column.

Adding a composite primary key to an existing table

Alternatively, you could create a composite primary key that consists of multiple columns, like this:

ALTER TABLE customers ADD PRIMARY KEY (first_name, last_name);

This would create a primary key that consists of both the first_name and last_name columns.

Checking the results

Once you’ve executed the ALTER TABLE command, the primary key will be added to the table. You can verify this by using the \d command to view the table’s details if you are using the psql command line tool, or by right clicking the table and clicking View Structure if you are using Beekeeper Studio.

Adding primary key summary

In summary, adding a primary key to an existing table in PostgreSQL is a simple process that can be accomplished using the ALTER TABLE command. This can be useful for ensuring the uniqueness of rows in a table, and for organizing and querying data more effectively.