Postgresql unique constraint

In this PostgreSQL tutorial, we will learn about “Postgresql unique” and cover the following topics.

• Postgresql unique constraint
• Postgresql unique constraint multiple columns
• Postgresql unique select
• Postgresql unique index
• Postgresql unique constraint name
• Postgresql unique constraint multiple columns null
• Postgresql unique constraint null
• postgresql unique two columns

Postgresql unique constraint

In Postgresql, we want to ensure that values stored in a column or a group of columns are unique across the whole table such as email addresses or usernames.

PostgreSQL provides us with the UNIQUE constraint that keeps the uniqueness of the data correctly.

When a UNIQUE constraint is in place, it tries to see if the value is already in the table before inserting a new entry. If the value already exists then It rejects the change and issues an error. The same process is done for updating existing data.

When we add a UNIQUE constraint to a column or a group of columns, PostgreSQL will automatically create a unique index on the column or the group of columns.

Let’s create a table with columns id, emp_name, email, and make the email column unique.

CREATE TABLE emp_data (
	id SERIAL,
	emp_name VARCHAR ,
	email VARCHAR (50) UNIQUE
);

In the above code, we have declared column email as unique using the keyword UNIQUE.

Insert the following records.

INSERT INTO emp_data (emp_name,email)
VALUES('john','jdoe@.com');

Again insert the same records with a different name.

INSERT INTO emp_data (emp_name,email)
VALUES('dan', 'jdoe@.com');

The output of the above code is given below.

As we can see in the above output, it shows an error when we inserted a duplicate email.

Read: Postgresql replace

Postgresql unique constraint multiple columns

In Postgresql, we can create the unique constraint on multiple or groups of columns using the below syntax.

CREATE TABLE table_name (
    col_1 data_type,
    col_2 data_type,
    col_3 data_type,
    UNIQUE (col_2, col_3)
);

In the above code, within the UNIQUE function, we have provided two columns col_2, col_3 to make a unique column.

Lets’ consider this with an example.

CREATE TABLE state_city(states varchar,city varchar,UNIQUE(states,city));

Insert the following records.

INSERT INTO state_city(states,city)VALUES('New York','New York City'),('California','Los Angeles'),
('Victoria','Melbourne');

The output of the code is given below.

In the above output, we have inserted the records as states and cities like New York, California is the state of the United States and Victoria is the state of Australia.

Read: PostgreSQL Update Join

Postgresql unique index

In Postgresql, when we want to add a unique constraint to an existing column or group of columns.

Let’s create a new table name unique_data.

CREATE TABLE unique_data (
	id SERIAL PRIMARY KEY,
	name VARCHAR  NOT NULL,
	unq_data_id VARCHAR  NOT NULL
);

In the above code, we are creating a table unique_data with three columns id, name, unq_data_id.
Create a unique index on unq_data_id.

CREATE UNIQUE INDEX CONCURRENTLY unique_data_id 
ON  unique_data(unq_data_id);

Now add a unique constraint to the unique_data table using the unique_data_id index using the below code.

ALTER TABLE unique_data
ADD CONSTRAINT unique_unq_id
UNIQUE USING INDEX unique_data_id;

Also, check: Postgresql Add Foreign Key

Postgresql unique select

In Postgresql, we will use the DISTINCT in the select statement to find the unique data or values in a column.

Here we are going to use the fruit_sell table and the records or information of the table is given below.

Now, use the below code to select the unique f_name from the table fruit_sell.

SELECT DISTINCT(f_name) FROM fruit_sell;

The output of the above code is given below.

Read: PostgreSQL Min With Examples

Postgresql unique constraint null

In Postgresql, the SQL standard allows us to insert or save many nulls in a single column, although certain database systems, such as MS SQL, only allow a single null. If users moving from other database systems sometimes want to mimic this behavior in Postgres. So this can be done.

First, we will see the default Postgres behavior

CREATE TABLE nullexample (a INTEGER UNIQUE);

INSERT INTO nullexample VALUES (NULL);
INSERT INTO nullexample VALUES (NULL);

The output of the above code is given below.

Read: PostgreSQL group by

Postgresql unique constraint multiple columns null

In Postgresql, we can also insert the null values in multiple columns that have constraints as unique as we have done in a single column in the above sub-section.

Let’s create two unique columns and insert the null values.

CREATE TABLE nulltwoexample (a INTEGER UNIQUE,b INTEGER UNIQUE);
INSERT INTO nulltwoexample(a,b) VALUES (NULL,NULL);
INSERT INTO nulltwoexample(a,b) VALUES (NULL,NULL);
SELECT * FROM nulltwoexample;

The output of the above code is given below.

Read: PostgreSQL add primary key

Postgresql unique two columns

In Postgresql, we can make two-column as unique using the UNIQUE keyword or function.

Let’s create a table named two_unq.

CREATE TABLE emp_data (
	id SERIAL,
	emp_name VARCHAR ,
email VARCHAR (50),
UNIQUE(emp_name,email ));

In the above code, we are creating emp_name, email as the unique column using the UNIQUE().

Postgresql unique constraint name

In Postgresql, we can also make the name column a unique column.

Let’s create a table that will contain the name column as a unique column.

CREATE TABLE unq_name(id SERIAL, name varchar UNIQUE);

Insert the following records.

INSERT INTO unq_name(name)VALUES('Lucifer');

Again insert the same record.

INSERT INTO unq_name(name)VALUES('Lucifer');

The output of the above code is given below.

In the above output, as we can see that inserting the same data again shows the error “duplicate key value violates unique constraint “unq_name_name_key”, which means the column can contain unique values.

Also, take a look at some more tutorials on PostgreSQL.

• Postgresql Having Clause
• Postgresql while loop
• Postgresql now() function
• Postgresql Sum
• Postgresql if else
• PostgreSQL Loop
• Drop Database PSQL

So, in this tutorial, we have learned about “Postgresql unique” and covered the following topics.

• Postgresql unique constraint
• Postgresql unique constraint multiple columns
• Postgresql unique select
• Postgresql unique index
• Postgresql unique constraint name
• Postgresql unique constraint multiple columns null
• Postgresql unique constraint null
• postgresql unique two columns