Convert oid to json as int instead of string

Using Postgres 12, the following will return an int JSON representation:

> SELECT to_json(2::int)
.. 2

Whereas if the type is oid, it will return it as string:

> SELECT to_json(2::oid)
.. "2"

Since oid is inherently an int value, I would like it to be represented as such. I tried creating a cast between oid and both text and json types, but neither seems to be picked up by to_json.

Is there a way to make to_json represent an oid as an int, outside of casting each oid column to int explicitly?

Answer

You will have to use an explicit cast, because it is hard-coded that PostgreSQL treats oid as a string.

You could suggest the following patch to the pgsql-hackers mailing list:

diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 30ca2cf6c8..09e9a9ac08 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -170,6 +170,7 @@ json_categorize_type(Oid typoid,
        case FLOAT4OID:
        case FLOAT8OID:
        case NUMERICOID:
+       case OIDOID:
            getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
            *tcategory = JSONTYPE_NUMERIC;
            break;
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 8d1e7fbf91..0e8edb0fc3 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -650,6 +650,7 @@ jsonb_categorize_type(Oid typoid,
        case FLOAT4OID:
        case FLOAT8OID:
        case NUMERICOID:
+       case OIDOID:
            getTypeOutputInfo(typoid, outfuncoid, &typisvarlena);
            *tcategory = JSONBTYPE_NUMERIC;
            break;

That would change the behavior, and I don’t see why the patch shouldn’t be accepted.