PostgreSQL – Primary Key

In this article we will look into the PostgreSQL Primary key constraints using SQL statements. A primary key is a column or a group of columns used to identify a row uniquely in a table. Technically speaking a primary key constraint is the blend of a not-null constraint and a UNIQUE constraint. Only one primary key must exist in a table. PostgreSQL produces a unique B-tree index on the column or a group of columns used that defines the primary key whenever a primary key is added to the table.

Syntax: column_name Data-type PRIMARY KEY

Now let’s look into some examples.
Example 1:
In this example we will create a table (say, books) and add a column(say, book_id) as the primary key of the table.

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    price INTEGER
);

Now let’s insert some data to our table using the below command:

INSERT INTO books(book_id, title, price)
VALUES
    ('101', 'Jobs', '2000'),
    ('102', 'Geeta', '250'),
    ('103', 'Ramayana', '354'),
    ('104', 'Vedas', '268');

Now we will check if the inserted works as intended using the below command:

 SELECT * FROM books;

Output:

Example 2:
In this example we will be adding PRIMARY key to an existing table. So, lets first create one using the command below:

CREATE TABLE vendors (name VARCHAR(255));

Now add few rows to the vendors table using INSERT statement as below:

INSERT INTO vendors (NAME)
VALUES
    ('Microsoft'),
    ('IBM'),
    ('Apple'),
    ('Samsung');

Now verify the insert operation, we query data from the vendors table using the following SELECT statement:

SELECT
    *
FROM
    vendors;

This will give us the below result:

Now, if we want to add a primary key named id into the vendors table and the id field is auto-incremented by a single unit, we use the below statement:

ALTER TABLE vendors ADD COLUMN ID SERIAL PRIMARY KEY;

Let’s check the vendors table again using the below command:

SELECT
    id, name
FROM
    vendors;

Output: