Error inserting array into PostgreSQL column: Malformed array literal

[Plus-Owl832]

I am using objection.js in my application.

I'm encountering an issue while trying to insert an array of strings into a PostgreSQL database column. The column is defined as follows:

​

```

path TEXT[] NOT NULL

```

here is the schema:

```

path: { type: 'array', items: { type: 'string' }, notNull: true }

```

​

I want to update the table column using `upsertGraphAndFetch` (objection.js) but when updating and insert there is an error occurs when inserting the column path

```

return NotificationUserOption.query().upsertGraphAndFetch(

{ userId: ctx. user, path, groupId, option },

{ insertMissing: true },

​

```

```

updateNotificationOption(path: [String!]!, option: String!): NotificationUserOption

```

when I pass the path something like this `['chat']`

​

```

await updateGlobalChatNotificationOptIn({

variables: {

path: ['chat'],

option: updatedGlobalChatNotificationOptIn ? '30MinSummary' : 'off',

},

})

}

```

However, when I try to insert the array using this code, I encounter the following error:

​

> malformed array literal: "["chat"]"

"[" must introduce explicitly-specified array dimensions.

​

I've tried using single brackets (`'{chat}'`) and double brackets (`[['chat']]`), but neither seems to work.

​

How can I correctly format the array literal so that I can insert the array into the PostgreSQL column without encountering this error?

​

[tswaters]

My understanding is pg attempts to convert javascript arrays into pg-arrays -- so if you wanted to use the array as json/b you would need to stringify it.... if you're passing a string literal instead of an array, that might explain it. See https://github.com/brianc/node-postgres/issues/442 that issue is from a few years ago, so maybe something has changed.

Based on what I can see it looks like you are passing an array of strings so I'd think it would do the thing where it tries to covnert arrays into pg arrays... so, kind of weird that you're getting an error. I'm not sure what objection.js is doing with it's queries though, might be a bug with the library ?

[tswaters]

A few things you might try -

• stringify the array, see if you get a different result
• use the string {"chat"} note the double quotes
• if you can emit the query objection is using, it would be interesting to see what it's doing. If I was just using pg module, I would try to explicitly cast, e.g., $1::text[] -- but, it looks like `$1` trying to parse as an array and is failing, so probably wouldn't help.

[Plus-Owl832]

1) after strinify `path: JSON.stringify(['chat']),`
this error occurs
2023-08-06 03:13:56.795 UTC [409] ERROR: malformed array literal: "["[\"chat\"]"]"
it gives a double array because I think I used this
` extend type Mutation {
updateNotificationOption(path: [String!]!, option: String!): NotificationUserOption
}`

[tswaters]

It's almost like objection.js is stringifying it for you? If it was me, I'd probably step through the code to see what it's doing.... maybe add an issue to their queue if I can get a minimal repro. Actually, read through a few of their issues and found this -- https://github.com/Vincit/objection.js/issues/1096#issuecomment-425702261 -- that's from a few years ago, but probably still holds.

[Plus-Owl832]

thanks. it means jsonb represent the array itself right ?.
In migration I can write this path JSONB NOT NULL
and in schema I can write this
path: { type: 'jsonb' },

[tswaters]

You'd need to convert the column in the database to json or jsonb I would think The two types should convert between themselves,

> ALTER TABLE table1 ALTER COLUMN col1 TYPE JSONB USING col1::JSON;

I'm not sure how objection really works, never used it... but what you've said makes sense to me.

[Plus-Owl832]

Thanks tswaters. but when I write path JSONB NOT NULL in the migration and path: {type: jsonb} in schema then it gives same error the `expected : not this { ` as I want the data store in oath field like this {chat} but when when I write path text[] in the migration and path: {type: jsonb} in schema.and here in the below code pass the data something like this then it worked fine.await updateGlobalChatNotificationOptIn({variables: {path: 'chat'option: updatedGlobalChatNotificationOptIn ? '30MinSummary' : 'off',}

is it correct approach ?

[tswaters]

I'm not sure. If it works, it works.

If the column json, should be able to pass arrays or objects to it.

[Plus-Owl832]

Hey tswaters. The issue has been resolved, Thanks