Postgresql enforce unique two-way combination of columns

by

A variation on Neil’s solution which doesn’t need an extension is:

create table friendz (
  from_id int,
  to_id int
);

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));

Neil’s solution lets you use an arbitrary number of columns though.

We’re both relying on using expressions to build the index which is documented
https://www.postgresql.org/docs/current/indexes-expressional.html

More Related Contents:

  1. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  2. Insert, on duplicate update in PostgreSQL?
  3. invalid byte sequence for encoding “UTF8”
  4. How to make “case-insensitive” query in Postgresql?
  5. Rownum in postgresql
  6. Postgresql: Scripting psql execution with password
  7. dynamic sql query in postgres
  8. Cannot create a database table named ‘user’ in PostgreSQL
  9. Is there a timeout for idle PostgreSQL connections?
  10. How to make connection to Postgres via Node.js
  11. Postgresql Truncation speed
  12. Buffers (Circle) in PostGIS
  13. docker postgres pgadmin local connection
  14. SELECT raises exception in PL/pgSQL function
  15. Heroku “psql: FATAL: remaining connection slots are reserved for non-replication superuser connections”
  16. Changing primary key int type to serial
  17. Return SETOF rows from PostgreSQL function
  18. PSQLException: current transaction is aborted, commands ignored until end of transaction block
  19. Postgresql insert trigger to set value
  20. When do Postgres column or table names need quotes and when don’t they?
  21. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  22. how to store PostgreSQL jsonb using SpringBoot + JPA?
  23. PostgreSQL how to concat interval value ‘2 days’
  24. Displaying image in Ireports using PostgreSql
  25. Orbeon Forms Postgres DB connection
  26. How to delete an enum type value in postgres?
  27. Why unsigned integer is not available in PostgreSQL?
  28. now() default values are all showing same timestamp
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment