PostgreSQL 10 Administration Cookbook - Fourth Edition

How to do it…

To prevent duplicate rows, we need to create a unique index that the database server can use to enforce uniqueness of a particular set of columns. We can do this in the following three similar ways for basic data types:

1. Create a primary key constraint on the set of columns. We are allowed only one of these per table. The values of the data rows must not be NULL, as we force the columns to be NOT NULL if they aren't already:

ALTER TABLE newcust ADD PRIMARY KEY(customerid);

2. This creates a new index named newcust_pkey.
3. Create a unique constraint on the set of columns. We can use these instead of/or with a primary key. There is no limit on the number of these per table. NULL values are allowed in the columns:

ALTER TABLE newcust ...