porsager / porsager/postgres

PostgresError: cannot cast type boolean to jsonb

Open
#931 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
JavaScript
Stars
8.7k
Forks
374
Avg merge
11d 16h
Merged PRs (30d)
1

Description

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

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the reported INSERT with information set to [true, false] and compare it with the Boolean-object workaround. Trace how the library serializes arrays containing primitive booleans before the jsonb cast. Done means the original query succeeds without the workaround and the behavior is covered by a regression test.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, nodejs, postgresql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.