4

What is the meaning and purpose of the following statement in Postgres:

xmax::text::int > 0

This looks like casting to text and then casting again to integer ???

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Jimski
  • 826
  • 8
  • 23
  • That would appear to be what it is doing. I would expect either `xmax > 0` or `xmax::int > 0` to be equivalent. – Gordon Linoff Apr 01 '18 at 12:07
  • Sometimes you can't cast directly from one type to another (especially with custom types), so you have to first cast to text and then to the type you want. – 404 Apr 01 '18 at 12:34
  • 1
    `xmax::text::int` it is because `xmax` have type `xid` which can not be directly converted to `integer` value (look at [casts](https://www.postgresql.org/docs/current/static/sql-createcast.html) or somewhere) The meaning of this condition: `row was updated`. There is really great answer, with explanation, about the topic: https://stackoverflow.com/a/39204667/593144 – Abelisto Apr 01 '18 at 13:33

1 Answers1

6

Every table in Postgres has several system columns. They are non-standard, Postgres specific feature, which use requires basic knowledge about Postgres internals.

The column xmax essentially contains 0 or some transaction number. This value can not be however directly compared to numbers, because the column is of type xid, for which no corresponding operators have been defined. For this reason, it is necessary to use casting.

The comparison can be also written as

xmax::text <> '0'

The meaning of the expression can be inferred from the column description:

xmax

The identity (transaction ID) of the deleting transaction, or zero for an undeleted row version. It is possible for this column to be nonzero in a visible row version. That usually indicates that the deleting transaction hasn't committed yet, or that an attempted deletion was rolled back.

klin
  • 112,967
  • 15
  • 204
  • 232
  • This explains the xmax::text but what is ::int for? Why double cast it instead just max::int <> '0' – Jimski Apr 01 '18 at 19:11
  • 2
    @Jimski From the answer above: `This value can not be however directly compared to numbers, because the column is of type xid, for which no corresponding operators have been defined`. Anything (?) can be cast to text, hence the cast to text, then the cast to int. – 404 Apr 01 '18 at 19:20