60454

postgresql unique constraint for any integer from two columns (or from array)

<h3>Question</h3>

How to guarantee a uniqueness of any integer from the two columns / array?

Example: I create a table and insert one row in it:

CREATE TABLE mytest(a integer NOT NULL, b integer NOT NULL); INSERT INTO mytest values (1,2);

What UNIQUE INDEX should I create to not allow add any of the following values

INSERT INTO mytest values (1,3); # because 1 is already there INSERT INTO mytest values (3,1); # because 1 is already there INSERT INTO mytest values (2,3); # because 2 is already there INSERT INTO mytest values (3,2); # because 2 is already there

I can have array of two elements instead of two columns if it helps somehow.

Surely, I can invent some workaround, the following come into my mind:

<ul><li>create separate table for all numbers, have unique index there, and add values to two tables with transaction. If the number is not unique, it won't be added to the second table, and transaction fails</li> <li>add two rows instead of one, and have additional field for id-of-the-pair.</li> </ul>

But I want to have one table and I need one row with two elements in it. Is that possible?


<h3>Answer1:</h3>

You can use exclusion constraint on table along with intarray to quickly perform search of overlapping arrays:

CREATE EXTENSION intarray; CREATE TABLE test ( a int[], EXCLUDE USING gist (a gist__int_ops WITH &&) ); INSERT INTO test values('{1,2}'); INSERT INTO test values('{2,3}'); >> ERROR: conflicting key value violates exclusion constraint "test_a_excl" >> DETAIL: Key (a)=({2,3}) conflicts with existing key (a)=({1,2}).

来源:https://stackoverflow.com/questions/40934793/postgresql-unique-constraint-for-any-integer-from-two-columns-or-from-array

Recommend

  • postgresql unique constraint for any integer from two columns (or from array)
  • postgresql unique constraint for any integer from two columns (or from array)
  • Simple and elegant way to zero-pad a value in C# [duplicate]
  • Does Prolog have a condition and restart system like Common Lisp?
  • How do I printf() a uint16_t?
  • autofac wcf registration error
  • Delphi 'AND' evaluation with 2 conditions
  • How to know if SQL Server is in the cloud or on premises?
  • how to add 30 minutes to current time
  • Advantage of computed properties (gettable ones only) vs. stored properties
  • Why is ftp_nlist returning an empty array for a non-empty directory?
  • Vertical stretch WebBrowser to fit content inside
  • Automate stdin with Python using stdin.write()
  • Why net().get on success return empty string
  • Handle loading of .net module into PowerShell
  • Is Win2D yet available in C++/WinRt?
  • Display Windows Touch Keyboard
  • Flask-Admin batch action with form
  • Parallel exact matrix diagonalization with Python
  • MQL4, Code layout for big EA
  • Facebook Requests ID(s) retrieve and delete
  • Adding a delete button in PHP on each row of a MySQL table
  • How to install or uninstall SonarQube plug-ins with HTTP?
  • Error when build LineageOS: “make: *** [ninja_wrapper] Error 1”
  • iterating through image folder using javascript and adding the result in HTML
  • Python tk scrollbar becomes inactive once text is outside the screen
  • ROR + MVC Disable Browser Cache
  • iOS App crash issue `[UIWindow warpPoint:]`
  • Use AutoIt with java applications
  • Set SelectedIndex of ListView in FlipView_SelectionChanged event
  • Rotating Towards Path in OpenGL