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: