How to set a UNIQUE column constraint in PostgreSQL

There is the contraint/3 function.

create table("table_name") do
  add :my_unique_field, :integer
end

create unique_index("table_name", [:my_unique_field])

for reference

If I understand it correctly, the constraint/3 function is just for check or exclusion constraints. You cannot e. g. say: create constraint(:my_table, unique: :my_unique_field)

So the check constraint will yield a ALTER TABLE ... ADD CONSTRAINT ... CHECK (...) which is not what I’m after. But in theory this would be the correct function to implement such a thing.

Thanks! This is a unique_index though, which is distinct from a unique constraint on a table column

afaik they’re the same thing at least on regular sql databases(i’m not sure about timescale and other “sql-like” databases with mods). but on regular sql db unique constraint on a column is achieved by a unique index.

just for reference from postgres

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.
PostgreSQL: Documentation: 18: 5.5. Constraints

From the PostgreSQL perspective this is a distinction without a real difference. The unique constraint is implemented via the unique index.

Per the docs:

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

It is valid to create the index without the constraint.

To all the people stating that unique indexes and constraints are the same: This is not true.

If all you need are the features and functionality of a unique index the difference does not matter, but if you want to use the unique constraint with all the features of constraints – like being able to defer their validation – the difference does indeed matter.

@mmmrrr you can use execute/1,2 to add the constraint with raw sql after having created the table. Ecto doesn’t need to have explicit api for any and all things your db supports.

Edit: Example of that can be found in Unnest for runtime sorted results | Benjamin Milde (migration is in the code comments)

if i understand correctly all uniqueness checks are validated when the transaction is commited, that is the same as deferring a constraint.

If a conflicting valid row has been deleted by the current transaction, it’s okay. (In particular, since an UPDATE always deletes the old row version before inserting the new version, this will allow an UPDATE on a row without changing the key.)
PostgreSQL: Documentation: 18: 63.5. Index Uniqueness Checks

Fair enough, though uniqueness deferral is in my experience not common (yes, it absolutely does happen). Absent any specific need for the deferral and I’m just as likely to go for the index… because while you cannot defer an index, you can’t build a constraint backed by an index concurrently… so something gained, something lost… naturally there are caveats to all of this.

However, if you just need uniqueness… a unique index alone is perfectly valid.

That’s not correct. You get the error for uniqueness validation immediately on the command creating the violation. Delete and insert has no conflict. Allowing two rows to temporarily share a unique column value within a transaction needs a deferred constraint.