Postgresql: Conditionally unique constraint

by

PostgreSQL doesn’t define a partial (i.e. conditional) UNIQUE constraint – however, you can create a partial unique index.

PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, with an important caveat: you can’t perform upserts (ON CONFLICT DO UPDATE) against a unique index like you would against a unique constraint.

Also, you won’t see the constraint listed in information_schema.

CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);

See partial indexes.

More Related Contents:

  1. Does a Postgres UNIQUE constraint imply an index?
  2. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  3. CONSTRAINT to check values from a remotely related table (via join etc.)
  4. How to add “on delete cascade” constraints?
  5. Why can I create a table with PRIMARY KEY on a nullable column?
  6. PostgreSQL – disabling constraints
  7. Why the “Group BY” by month didn’t work? [closed]
  8. Truncating all tables in a Postgres database
  9. How can I drop all the tables in a PostgreSQL database?
  10. How to change PostgreSQL user password?
  11. How to get the count of current month Sunday’s in psql?
  12. Making sense of Postgres row sizes
  13. How to export table as CSV with headings on Postgresql?
  14. What’s the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?
  15. Can I automatically create a table in PostgreSQL from a csv file with headers?
  16. PostgreSQL query very slow with limit 1
  17. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  18. Postgres query optimization (forcing an index scan)
  19. psql: FATAL: database “” does not exist
  20. Difference between timestamps with/without time zone in PostgreSQL
  21. Omitting the double quote to do query on PostgreSQL
  22. Storing Images in PostgreSQL
  23. Psql could not connect to server: No such file or directory, 5432 error?
  24. How to add column if not exists on PostgreSQL?
  25. Maximum characters in labels (table names, columns etc)
  26. Docker – Postgres and pgAdmin 4 : Connection refused
  27. Heroku database restore issue
  28. How do I get min, median and max from my query in postgresql?
  29. PostgreSQL jsonb, `?` and JDBC
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment