title: Check for uniqueness of column in postgres table
body: <p>I need to ensure that the values in a column from a table are unique as part of a larger process.</p>
  <p>I'm aware of the <code>UNIQUE</code> constraint, but I'm wondering if there is a better way to do the check.</p>
  <p>I'm running the queries using <code>psycopg2</code> so adding that tag on the off chance there's something in there that can help with this.</p>
  <p>If the column is unique I can add a constraint. If the column is not unique adding the constraint will return an error.</p>
  <p>If there is already a constraint of the same name a useful error is returned. in this case would prefer to just check for the existing constraint.</p>
  <p>If the column is the primary key, the unique constraint can be added without error but in this case it would be preferable to just recognize that the column must be unique based on the primary key.</p>
  <p>Code examples of this below.</p>
  <pre class="lang-sql prettyprint-override"><code>DROP TABLE IF EXISTS unique_test;
  CREATE TABLE unique_test (
  pkey INT PRIMARY KEY,
  unique_yes CHAR(1),
  unique_no CHAR(1)
  );
  
  INSERT INTO unique_test (pkey, unique_yes, unique_no)
  VALUES(1, 'a', 'a'),
          (2, 'b', 'a');
      
  CREATE UNIQUE INDEX CONCURRENTLY u_test_1  ON unique_test (unique_yes);
  
  ALTER TABLE unique_test
  ADD CONSTRAINT unique_target_1
  UNIQUE USING INDEX u_test_1;
  
  -- the above runs no problem
  
  -- check what happens when column is not unique
  
  CREATE UNIQUE INDEX CONCURRENTLY u_test_2  ON unique_test (unique_no);
  
  ALTER TABLE unique_test
  ADD CONSTRAINT unique_target_2
  UNIQUE USING INDEX u_test_2;
  
  -- returns:
  -- SQL Error [23505]: ERROR: could not create unique index &quot;u_test_2&quot;
  --  Detail: Key (unique_no)=(a) is duplicated.
  
  
  CREATE UNIQUE INDEX CONCURRENTLY u_test_1  ON unique_test (unique_yes);
  
  ALTER TABLE unique_test
  ADD CONSTRAINT unique_target_1
  UNIQUE USING INDEX u_test_1;
  
  -- returns
  -- SQL Error [42P07]: ERROR: relation &quot;unique_target_1&quot; already exists
  
  
  -- test what happens if adding constrint to primary key column
  
  CREATE UNIQUE INDEX CONCURRENTLY u_test_pkey  ON unique_test (pkey);
  
  ALTER TABLE unique_test
  ADD CONSTRAINT unique_target_pkey
  UNIQUE USING INDEX u_test_pkey;
  
  -- this runs no problem but is inefficient.
  </code></pre>