PostgresError: cannot cast type boolean to jsonb

[mwannenmacher]

Problem Description

Trying to insert [true, false] into a jsonb column like the following sql:

INSERT INTO public.device_events (id, device_id, event, information, timestamp) VALUES (gen_random_uuid(), '9ba9b367-97b1-4a6b-9403-9615d845304c', 'EXAMPLE_EVENT', '[true, false]'::jsonb, 0);

This query does work as expected directly in the db, but is throwing an exception when executed with this library.
We are receiving events from external devices and want to persist those events including event data directly into the db.

Expected Behaviour

Inserting column directly into the table with result

const event = {
    id: '1e25ebb1-9818-49e1-9d00-6477cd521036',
    deviceId: '9ba9b367-97b1-4a6b-9403-9615d845304c ',
    event: 'EXAMPLE_EVENT',
    information: [true, false],
    timestamp: 0
};
const result = await sql` INTO device_events (id, device_id, event, information, timestamp)
       VALUES (${event.id}::uuid,
            ${event.deviceId}::uuid,
            ${event.event}::event_types,
            ${(event.information)}::jsonb,
            ${event.timestamp})
       RETURNING *`;

result:

id	device_id	event	information	timestamp	created_at
5640d21c-9c81-4a3f-b411-380837003b8d	9ba9b367-97b1-4a6b-9403-9615d845304c	EXAMPLE_EVENT	[true, false]	0	2024-08-19 09:59:10.108266+00

Current Behaviour

const event = {
    id: '1e25ebb1-9818-49e1-9d00-6477cd521036',
    deviceId: '9ba9b367-97b1-4a6b-9403-9615d845304c ',
    event: 'EXAMPLE_EVENT',
    information: [true, false],
    timestamp: 0
};
const result = await sql` INTO device_events (id, device_id, event, information, timestamp)
       VALUES (${event.id}::uuid,
            ${event.deviceId}::uuid,
            ${event.event}::event_types,
            ${(event.information)}::jsonb,
            ${event.timestamp})
       RETURNING *`;

Error output:

    PostgresError: cannot cast type boolean to jsonb

Local fix

const event = {
    id: '1e25ebb1-9818-49e1-9d00-6477cd521036',
    deviceId: '9ba9b367-97b1-4a6b-9403-9615d845304c ',
    event: 'EXAMPLE_EVENT',
    // fixing error on mapping promitive boolean to Boolean object
    information: [true, false].map(v => new Boolean(v)),
    timestamp: 0
};
const result = await sql` INTO device_events (id, device_id, event, information, timestamp)
       VALUES (${event.id}::uuid,
            ${event.deviceId}::uuid,
            ${event.event}::event_types,
            ${(event.information)}::jsonb,
            ${event.timestamp})
       RETURNING *`;

Table Description

Table "public.device_events"

Column	Type	Collation	Nullable	Default
id	uuid		not null
device_id	uuid		not null
event	event_types		not null	'UNKNOWN'::event_types
information	jsonb			'{}'::jsonb
timestamp	integer		not null
created_at	timestamp with time zone		not null	CURRENT_TIMESTAMP

[logi]

It seems that the postgres devs simply forgot to handle this case gracefully and only merged a fix for it in January 2025
https://www.postgresql.org/message-id/376008.1737733733%40sss.pgh.pa.us

[VividVisions]

Thanks for the local fix! <3