PostgreSQL add primary key

In this PostgreSQL tutorial, we are going to learn about Postgresql add primary key. Here we will learn how to add the primary key in PostgreSQL, and we will also cover the following list of topics.

• Postgresql add primary key
• Postgresql add primary key autoincrement
• Postgresql add primary key if not exists
• Postgresql add primary key multiple columns
• Postgresql add primary key using index
• Postgresql add primary key to existing column
• Postgresql add primary key column to existing table
• Postgresql add primary key to materialized view

Postgresql add primary key

In Postgresql, the primary key is a field during a table that severally recognizes each row or the record in a database table, and it contains a unique value. A primary key doesn’t hold any null value.

Hence, we can additionally say that the primary key is collecting the unique and not-null constraint of a table. If any particular column has a primary key constraint, then it can’t be null or empty.

It is used to recognize each record in a database table distinctively. A table can have other unique columns, but we have only one primary key in a database table including single or multiple fields.

It is the most significant key while creating the database tables, and it is a unique ID. It can be signified for one column or a group of columns.

Now we will define the primary key while creating the table. Generally, the primary key is added to the table is when we explain the table’s structure using CREATE TABLE statement. Let’s check the query below.

CREATE TABLE TABLE (
	column_1 data_type PRIMARY KEY,
	column_2 data_type,
	);

The below statement creates a purchase order buyer_headers table with the name buyer_headers.

CREATE TABLE buyer_headers (
	buyer_no INTEGER PRIMARY KEY,
	distributor_no INTEGER,
	description TEXT,
	billing_address TEXT
);

The buyer_no is the primary key of the buyer_headers table, which uniquely identifies purchase orders in the buyer_headers table.

If the primary key has two or more columns, we will explain the primary key constraint as below.

CREATE TABLE TABLE (
	column_1 data_type,
	column_2 data_type,
	… 
        PRIMARY KEY (column_1, column_2)
);

Suppose the following statement creates the purchase order line items table whose primary key is a combination of purchase order number (buyer_no) and line product number (article_no).

CREATE TABLE po_article (
	buyer_no INTEGER,
	article_no INTEGER,
	product_no INTEGER,
	qty INTEGER,
	net_price NUMERIC,
	PRIMARY KEY (buyer_no, article_no)
);

In case we don’t want to state the name for the primary key constraint, PostgreSQL allocates a default name to the primary key constraint.

Therefore PostgreSQL uses table-name_pkey as the default name for the primary key constraint.

In the below example, PostgreSQL will create the primary key constraint with the name po_article_pkey for the po_article table.

Let’s check the output for the above two tables.

If we want to state the name of the primary key constraint, we will use the CONSTRAINT clause as below.

CONSTRAINT constraint_name PRIMARY KEY(column_1, column_2,...);

Let’s understand how we can explain the primary key when changing the existing table structure. It is uncommon to define a primary key for an existing table.

Now we can use the ALTER TABLE statement to add a primary key constraint. Let’s check the code for it.

ALTER TABLE table_name ADD PRIMARY KEY (column_1, column_2);

The below statement creates a table named items without defining any primary key.

CREATE TABLE items (
	item_no INTEGER,
	description TEXT,
	item_cost NUMERIC
);

Now if we want to add a primary key constraint to the items table, we can execute the below statement.

ALTER TABLE items
ADD PRIMARY KEY (item_no);

Let’s check the output for the above query.

Read How to connect to PostgreSQL database

Postgresql add primary key autoincrement

Auto-increment in Postgresql will allow a unique number to be generated automatically when a new record is inserted into a table.

Frequently this is the primary key field that is likely to be created automatically every time a new record is inserted. Let’s create another table seller that will not have any primary key.

CREATE TABLE seller (name VARCHAR(255));

And we will add some rows to the seller table using the INSERT statement.

INSERT INTO seller (NAME)
VALUES
	('ZARA'),
	('Old Navy'),
	('GAP'),
	('Hollister'),
        ('Coach');

Let’s check the output for the above table which we have created.

For checking the insert operation, we query data from the seller table using the following SELECT statement.

SELECT * FROM seller;

Now, if we want to add a primary key name id into the seller table and the id field is auto-incremented by one, we will use the below syntax.

ALTER TABLE table_name ADD COLUMN id SERIAL PRIMARY KEY;

Now Let’s check the query for our table.

ALTER TABLE seller ADD COLUMN ID SERIAL PRIMARY KEY;

Let’s check the seller table again.

SELECT id,name FROM seller;

Let’s check the output for the above query.

Read Postgresql Having Clause

Postgresql add primary key if not exists

Now we will learn how to add the primary key if not exist in Postgresql. Let’s understand it with the help of table items that we have already created in the previous topic.

We will alter the table items by adding a new column name which is customer_name. Let’s check the syntax.

ALTER TABLE table_name 
ADD COLUMN column_name VARCHAR NOT NULL;

Let’s check the query again by adding the column customer_name.

ALTER TABLE items ADD COLUMN customer_name VARCHAR NOT NULL;

Now we are going to alter the existing column name which is item_no. Let’s check the syntax for it.

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

Let’s check the query for the above syntax.

ALTER TABLE items ALTER COLUMN item_no SET NOT NULL;

Now, we can try to add the column name again which is item_no. Let’s understand the syntax first then will execute the query.

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name INTEGER;

Let’s check the code for the above syntax by adding the column item_no again by IF NOT EXISTS clause.

ALTER TABLE items ADD COLUMN IF NOT EXISTS item_no INTEGER;

Now we will execute the queries which we have understood above followed by the execution. Let’s check the output for it.

Check out, Postgresql while loop

Postgresql add primary key multiple columns

In Postgresql, if we want to add a new column to an existing table, we will use the ALTER TABLE and ADD COLUMN statement as below.

In the below syntax, firstly we will specify the name of the table that we want to add a new column to after the ALTER TABLE keyword.

Then, we will specify the name of the new column as well as its data type and constraint after the ADD COLUMN keywords. Let’s check the syntax.

ALTER TABLE table_name
ADD COLUMN new_column_name data_type constraint;

If we want to add a new column to the table, PostgreSQL will append it at the end of the table because it has no option to specify the position of the new column in the table.

To add multiple columns to an existing table, we will use multiple ADD COLUMN clauses in the ALTER TABLE statement as below. Let’s check the syntax.

ALTER TABLE table_name
ADD COLUMN column_name1 data_type constraint,
ADD COLUMN column_name2 data_type constraint,
...
ADD COLUMN column_namen data_type constraint;

The following CREATE TABLE statement creates a new table named student with two columns which is id and student_name.

DROP TABLE IF EXISTS student CASCADE;

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    student_name VARCHAR NOT NULL
);

The below statement will use the ALTER TABLE and ADD COLUMN statements to add the marks column to the student table.

ALTER TABLE student
ADD COLUMN phone VARCHAR;

Now the below statement will add the fax and email columns to the student table.

ALTER TABLE student
ADD COLUMN fax VARCHAR,
ADD COLUMN email VARCHAR;

Let’s check the output for the above queries.

For checking the structure of the student table using the psql tool we will use the \d command like this. Let’s check the output also.

Read Postgresql REGEXP_REPLACE Function

Postgresql add primary key using index

Now we will learn how to add primary key using index in Postgresql. Now let’s see how to make a primary key within a table, but how do we make an existing index a primary key.

Firstly we will try to copy an existing table from one database to another. When we show in the table, the index at the bottom is in this form. Let’s check the syntax by creating the index.

CREATE INDEX my_index ON tablename (column1, column2);

Let’s check the query followed by the output.

CREATE INDEX my_index ON student (student_name);

Read Postgresql Sum

Postgresql add primary key column to existing table

Now we will learn how we can add a primary key column to an existing table. If we want to include PRIMARY KEY in the ADD clause with the ALTER TABLE statement to add a primary key to a table definition.

Before adding the primary key, we must ensure that the columns in the primary key column list are defined as NOT NULL. A primary key is a unique index that is created only on not nullable columns.

If any unique index on not nullable columns has already existed, the ADD PRIMARY KEY will not create another unique index. In this instead of the existing unique index is promoted to a primary key.

In the below example the statements would promote the named index T1_C1C2 as a primary key. Let’s check the query followed by the output.

CREATE TABLE t1 (c1 INT NOT NULL, c2 CHAR(10) NOT NULL);
CREATE UNIQUE INDEX t1_c1c2 ON t1(c1,c2);
ALTER TABLE t1 ADD PRIMARY KEY(c1, c2);

If that type of primary key is dropped, the primary key will switch to a unique index. Let’s check the query for this.

ALTER TABLE t1 DROP PRIMARY KEY(c1, c2);

Read PostgreSQL Loop Examples

Postgresql add primary key to materialized view

In PostgreSQL, materialized views uses the rule system just like views do, but continue the results in a table-like form. The view is not materialized physically in the query. The query is run every time, views are referenced in a query. Let’s check the syntax.

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

We will use our existing table in the following query. Let’s check the code for it.

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM student;

The materialized view is not eventually directly updated and that the query is used to create the materialized view which is stored in exactly the same way that a view’s query is used to store so that new data is generated for the materialized view with the below syntax.

REFRESH MATERIALIZED VIEW mymatview;

Let’s check the output.

Read Postgresql if else

Postgresql add primary key to existing column

Now we will learn how to add a primary key to an existing column. Check the below table named student. None column in this table is decided as PRIMARY KEY. Now, we will make the column id as PRIMARY KEY.

We will take the example of the table student which we have already created in the previous topic. Let’s check the syntax first.

ALTER TABLE student
    ADD CONSTRAINT fax_pk PRIMARY KEY (fax);

When we explain the table by its table name with \d table name command as shown below, there is a constraint that is added with id as PRIMARY KEY. Let’s check the output. Let’s check the output.

You may also like the following postgresql tutorials:

• Postgres date range
• Drop Database PSQL
• PostgreSQL Update
• Postgresql date comparison
• Postgresql date to string

In this PostgreSQL tutorial, we have learned about Postgresql add primary key. Here we have learned how to add the primary key in PostgreSQL, and we have also covered the following list of topics.

• Postgresql add primary key
• Postgresql add primary key autoincrement
• Postgresql add primary key if not exists
• Postgresql add primary key multiple columns
• Postgresql add primary key using index
• Postgresql add primary key to existing column
• Postgresql add primary key column to existing table
• Postgresql add primary key to materialized view