Why PostgREST assume oid types are string?

[gkobluk]

Hi Everyone,

I'm currently dealing with a small issue, probably it's just me. Let's consider the following example:

db=# create table test2(id serial primary key, oid_col oid, oid_array_col oid[]);
CREATE TABLE
db=# insert into test (oid_col, oid_array_col) values ('12345', '{1,2,3,4,5}'::oid[]);
INSERT 0 1
db=# select * from test;
 id | oid_col | oid_array_col 
----+---------+---------------
  1 |   12345 | {1,2,3,4,5}
(1 row)

The postgREST response is looking like this:

[{"id":1,"oid_col":"12345","oid_array_col":["1","2","3","4","5"]}]

Is there any reason why it can't respond as a number or array of numbers instead of doing text cast by default?

Thank you.

[steve-chavez]

Hello,

postgREST is just using the default PostgreSQL json_agg:

postgres=# select json_agg('{1,2,3,4,5}'::oid[]);
        json_agg
-------------------------
 [["1","2","3","4","5"]]
(1 row)

postgres=# select to_json('{1,2,3,4,5}'::oid[]);
        to_json
-----------------------
 ["1","2","3","4","5"]
(1 row)

postgres=# select to_json(3::oid);
 to_json
---------
 "3"
(1 row)

Which casts oids to strings, as it can be seen above.

[gkobluk]

I see. Thanks for clarifying it.

[steve-chavez]

As a workaround, you can cast the columns. Lilke:

GET /test?select=oid_col::int

[steve-chavez]

Also, with #2279 you can convert the oid array to an int array with:

GET /test?select=oid_array::_int4

That would render the json as you'd expect:

[{"id":1,"oid_array":[1,2,3,4,5]}]

Another option to avoid the casting on the url would be a computed column or a view.

[gkobluk]

Yeah, You're right. Thank you.