Wrong array literal when inserting array into PostgreSQL column

Question

I'm using objection.js in my app.

I encountered a problem while trying to insert a string array 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 while updating and inserting, Getting error while inserting column path

return NotificationUserOption.query().upsertGraphAndFetch(
        { userId: ctx. user, path, groupId, option },
        { insertMissing: true },

When I pass path similar to ['chat']

await updateGlobalChatNotificationOptIn({
      variables: {
        path: ['chat'],
        option: updatedGlobalChatNotificationOptIn ? '30MinSummary' : 'off',
      },
    })
  }

However, when I try to insert into array using this code The following error was encountered:

Array literal format error: "["chat"]" "[" must explicitly specify array dimensions.

I tried using single brackets ('{chat}') and double brackets ([['chat']]), but neither worked.

How do I properly format an array literal so that I don't encounter this error when inserting into a PostgreSQL column?

Answer 1

To insert a string array into a PostgreSQL column of type TEXT[], you need to convert the JavaScript array to a string representation, wrapping each element with curly braces and double quotes.

Perform the following operations in Objection.js:

// 将数组转换为字符串表示形式
const pathArray = ['chat'];
const pathString = `{${pathArray.join(',')}}`;

// 使用Objection.js将数据插入数据库
await NotificationUserOption.query().upsertGraphAndFetch(
  { userId: ctx.user, path: pathString, groupId, option },
  { insertMissing: true }
);

Now TEXT[] will not cause any "malformed array literal" errors. When querying data from the database, Objection.js will handle converting it back into a JavaScript array.

#Apache-Age #posgresql #graphql #objection.js