PostgreSQL implements unique constraints by way of unique indexes (and it's likely that all RDBMSs do likewise). Also, the syntax to declare unique indexes allows for more features than the unique constraints syntax. For example, you can have a unique index that covers only portion of the table, based on a WHERE condition (a partial unique index). You can't do this with a constraint.
Note, partial uniqueness can be achieved by using EXCLUDE contraints also.
