PostgresError: cannot cast type boolean to jsonb
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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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