👀 Views: 2 💬 Answers: 1 📅 Created: 2025-06-01

PostgreSQL JSONB composite-types SQL

I'm trying to debug I'm a bit lost with I'm updating my dependencies and I'm working with an behavior when trying to insert data into a PostgreSQL table that uses JSONB and composite types. My table structure includes a column of type JSONB that stores an array of composite types. Here's the relevant part of my table definition: ```sql CREATE TYPE my_composite AS ( id INT, name TEXT ); CREATE TABLE my_table ( id SERIAL PRIMARY KEY, data JSONB ); ``` I'm trying to insert the following data into the `data` column: ```sql INSERT INTO my_table (data) VALUES ( '[{"id": 1, "name": "John"}, {"id": 2, "name": "Doe"}]' ); ``` However, I'm receiving the behavior: `behavior: additional data is invalid`. I double-checked the JSON format, and it seems correct. I even validated it using an online JSON validator. After some testing, I tried inserting a simpler JSON object without the array: ```sql INSERT INTO my_table (data) VALUES ('{"id": 1, "name": "John"}'); ``` This works without any issues. It seems like the question only occurs when I try to insert an array. I've searched for similar issues but haven't found any that specifically address the combination of JSONB and composite types. Is there a specific requirement or format I need to follow when dealing with arrays in JSONB for composite types? Any help would be greatly appreciated! I'm coming from a different tech stack and learning Sql. Has anyone dealt with something similar? I'm on Windows 11 using the latest version of Sql. Thanks for taking the time to read this! This is my first time working with Sql latest. Hoping someone can shed some light on this.