PostgreSQL: Unexpected array element

Question

Writing a Node application that's pushing some data to a Postgres DB. One column's data type is an Array of a custom Composite type.

Here's what I'm trying to inject:

'{(123, (12, "Some Description")),(null, (34, "Some Desc"))}'

The composite type has two fields, the 2nd of which is another composite type. The error I'm getting back from Postgres is gives me the message:

malformed array literal: "{(123, (12, "Some Description")),(null, (34, "Some Desc"))}"

With the detail:

Unexpected array element

The column is declared as data type:

my_custom_data_type[]

Can't figure this exception out.

Answer 1

The string version of arrays ('{...}') can be cumbersome to work with, especially with more complex array entries like you have. I tend to prefer the array constructor syntax so that everything is a plain old value:

array[(123, (12, 'Some Description')), (null, (34, 'Some Desc'))]::my_custom_data_type[]

This array syntax also tends to work better with ORMs and the like.

The ::my_custom_data_type[] type cast helps make sure the parser knows what you're trying to say, you should be able to get away with just casting the first entry:

array[(123, (12, 'Some Description'))::my_custom_data_type, (null, (34, 'Some Desc'))]

but it is probably cleaner and easier to cast the whole array.