How do JSON types relate the PG column types?

[jenstroeger]

Hi,

I’ve been reading through the docs, and in particular Tables and Views and Resource Representation and Working with PostgreSQL data types, but I struggle to find an explanation and fix for the following behavior except this hint

PostgREST makes use of PostgreSQL string representations to work with data types.

at the beginning of a section here.

I’ve got a view on top of the schema-protected user table:

test_db=# \d+ api.profile 
                                                                     View "api.profile"
   Column   |           Type           | Collation | Nullable | Default | Storage  |                              Description                               
------------+--------------------------+-----------+----------+---------+----------+------------------------------------------------------------------------
 email      | text                     |           |          |         | extended | The primary email address of the user, also the user’s main login key.
 first_name | text                     |           |          |         | extended | The user’s first name
 last_name  | text                     |           |          |         | extended | The user’s last name
 created_at | timestamp with time zone |           |          |         | plain    | Timestamp of the user’s creation in the system.
View definition:
 SELECT email,
    first_name,
    last_name,
    created_at
   FROM auth."user";
Options: security_invoker=true

and therefore a /profile endpoint. Note that both first_name and last_name are nullable text type columns. Surprisingly, though, I am able to insert non-text values which seem to be coaxed to text, e.g.

~ > echo '{"first_name":[1]}' | http --verbose patch localhost:3000/profile Authorization:'Bearer token'
PATCH /profile HTTP/1.1
Accept: application/json, */*;q=0.5
Accept-Encoding: gzip, deflate
Authorization: Bearer token
Connection: keep-alive
Content-Length: 19
Content-Type: application/json
Host: localhost:3000
User-Agent: HTTPie/3.2.4

{
    "first_name": [
        1
    ]
}


HTTP/1.1 204 No Content
Content-Range: 0-0/*
Date: Fri, 02 May 2025 23:13:34 GMT
Server: postgrest/12.2.8

accepts the list of numbers and turns it into text and updates the column:

test_db=# select * from api.profile ;
             email             | first_name | last_name |          created_at           
-------------------------------+------------+-----------+-------------------------------
 foo@bar.com                   | [1]        |           | 2025-05-02 22:43:55.04169+00

It works the same with any other non-string JSON value like true (boolean) or 3.14 (float) and so forth. They all seem to be coaxed to text, although I had expected a 400 response status for any non-string values here?!

Could somebody please shed some light on this behavior? Thanks!

[wolfgangwalther]

The parsing of JSON is done by PostgreSQL's json_populate_record(..) and friends. The docs mention the following rules for that:

To convert a JSON value to the SQL type of an output column, the following rules are applied in sequence:

A JSON null value is converted to an SQL null in all cases.

If the output column is of type json or jsonb, the JSON value is just reproduced exactly.

If the output column is a composite (row) type, and the JSON value is a JSON object, the fields of the object are converted to columns of the output row type by recursive application of these rules.

Likewise, if the output column is an array type and the JSON value is a JSON array, the elements of the JSON array are converted to elements of the output array by recursive application of these rules.

Otherwise, if the JSON value is a string, the contents of the string are fed to the input conversion function for the column's data type.

Otherwise, the ordinary text representation of the JSON value is fed to the input conversion function for the column's data type.

(emphasis mine)

The last two explain what you see here.

[jenstroeger]

The parsing of JSON is done by PostgreSQL's json_populate_record(..) and friends. The docs mention the following rules for that:

[…]

The last two explain what you see here.

Hmm, that’s somewhat unexpected behavior which, I think, should be mentioned in the PostgREST docs (I vote for the Working with types chapter).