Postgres numeric type should be a string in Json Output

[johanneskares]

Environment

• PostgreSQL version: public.ecr.aws/supabase/postgres:14.1.0.89
• PostgREST version: public.ecr.aws/supabase/postgrest:v9.0.1.20220717
• Operating system: MacOS

Description of issue

When querying a Postgrest numeric type, PostgREST returns a number like:

[{"current_price":1234567890123456789012345678901234567890}]

After using JSON.parse in Javascript I get, so precision is lost. Other javascript libraries that are able to parse large numbers are quite inefficient.

[{"current_price": 1.2345678901234568e+39 }]

I think it would make sense to return a string instead, like.

[{"current_price":"1234567890123456789012345678901234567890"}]

Related Issue: supabase/cli#582

[wolfgangwalther]

I think you should cast your column to text either in a view or in the query: https://postgrest.org/en/stable/api.html#casting-columns.

[Taylor123]

This becomes very painful when there are many columns that must be cast. It would be better to auto cast based on the size/precision of the numeric.

[laurenceisla]

@Taylor123 Another alternative is to use Custom Domains and Domain Response Format.

[andrew222651]

Unfortunately setting a domain response format didn't seem to change the OpenAPI schema.

[andrew222651]

And if we cast everything to TEXT, we can't filter using numerical comparisons

[wolfgangwalther]

Unfortunately setting a domain response format didn't seem to change the OpenAPI schema.

Yeah, that's correct.

[andrew222651]

This should be considered a bug. Pydantic uses strings: https://docs.pydantic.dev/latest/api/standard_library_types/#decimaldecimal

[wolfgangwalther]

That would be a bug in PostgreSQL then, not PostgREST. We're not doing the conversion to json ourselves.

[andrew222651]

I agree it would be easier for postgres to handle it but theoretically PostgREST could do its own conversion

[wolfgangwalther]

No, we can't really. If we were to do this in haskell, we'd need to parse the whole json response in haskell first - and that would be a significant performance problem.