Postgresql – What column type can store txid_current value

postgresql

AFAIK txid_current returns uint 64 value (source).
However, the biggest int column type is bigint which is signed (docs).
From what I understand, NUMERIC type is slow and should be used "for storing monetary amounts and other quantities where exactness is required" (docs).

Should I store the txid_current value as text?

I need to perform mainly greater than queries on this field:

select * from t where fxid > 438298432

What would be the most efficient (from search speed POV) for full-transaction-id (unsigned integer 64)?

Best Answer

I'm suspicious about your motivations, but I'd say you should either use bigint (which is the data type xid_current returns) or use xid (which is the internal data type for xid's). By way of example, see the following:

pagila=# create table xidvals as select * from txid_current();
SELECT 1
pagila=# \d xidvals
                 Table "public.xidvals"
    Column    |  Type  | Collation | Nullable | Default
--------------+--------+-----------+----------+---------
 txid_current | bigint |           |          |

pagila=# select xmin,xmax,txid_current from xidvals;
  xmin  | xmax | txid_current
--------+------+--------------
 358165 |    0 |       358165
(1 row)

pagila=# select attname, atttypid::regtype from pg_attribute where attrelid = 'xidvals'::regclass and attname in ('xmax','xmin','txid_current');
   attname    | atttypid
--------------+----------
 xmax         | xid
 xmin         | xid
 txid_current | bigint
(3 rows)
Related Question