4

I couldn't find how to set a xid (transaction ID type) column to be unique in a table. It complains about class method missing for btree and I have no clue how to get around it.

This is using PostgreSQL 9.0.

Couldn't find any similar question in these forums or on the internet. :-(

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Djurdjura H.
  • 99
  • 2
  • 9
  • I'm very curious why one would do this, it basically would error if you tried to insert/update more than one row in the table per transaction. When would that be a useful property to have? – Eloff May 17 '20 at 17:43

1 Answers1

9

The reason is that there is no <> operator defined for data type xid (among others). Try:

SELECT '123'::xid <> '123'::xid

Fails.
You can circumvent this limitation by adding a unique index like this:

CREATE UNIQUE INDEX tbl_xid_col_uni_idx
ON tbl (cast(cast(xid_col AS text) AS int));
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228