postgresqluniquedatabase-schemapostgresql-9.0

PostgreSQL using XID as unique entry in a table

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. :-(

Solution

  • The reason is that there was no <> operator defined for data type xid before Postgres 9.6 at all. Trying:

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

    Failed.
    As of PostgreSQL 16 the type still has no btree operator class required to build an index enforcing the unique constraint but 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 bigint));

    It maps the xid to a type supported by btree and indexes that instead of the raw xid.