CODEWITHSUNDEEP
postgresqluniquedatabase-schemapostgresql-9.0
Djurdjura H.
Djurdjura H.

Reputation: 119

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

Upvotes: 6

Views: 4327

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

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.

Upvotes: 11

Related Questions