How to insert multiple rows when one of the columns is type of array

[muratgozel]

There are integer, text and JSON columns in my table and I'm inserting rows seamlessly. Today, I've added an array column supported_locales as text[] but am unable to insert items to it.

This is the DDL of the table:

CREATE TABLE project (
    id SERIAL PRIMARY KEY,
    uuid text NOT NULL,
    title text NOT NULL,
    email_service jsonb,
    -- cut other columns
    supported_locales text[]
);

This is the test file in which I have simulated the error:

(async () => {
  const {createPool, sql} = require('slonik')
  const pgpool = createPool('postgresql://postgres@127.0.0.1:15432/auth')
  await pgpool.connect(async pgconn => {
    const selectQuery = sql.unnest([
      ['123jk1h3jh3', 'Title.', JSON.stringify({prop: 'value'}), ['en_US']]
    ], ['text', 'text', 'jsonb', 'text[]'])

    await pgconn.query(sql`insert into project (uuid, title, email_service, supported_locales) select * from ${selectQuery} returning *`)
  })
})()

This is the error I receive:

error: column "supported_locales" is of type text[] but expression is of type text

Well, the column is surely the type of text[] but I couldn't cast the expression ['en_US']. Is there anyone to show it to me?

Error stack trace:

error: column "supported_locales" is of type text[] but expression is of type text
    at Parser.parseErrorMessage (/path/to/projectapp/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/path/to/projectapp/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/path/to/projectapp/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/path/to/projectapp/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:394:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
  length: 201,
  severity: 'ERROR',
  code: '42804',
  detail: undefined,
  hint: 'You will need to rewrite or cast the expression.',
  position: '76',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_target.c',
  line: '582',
  routine: 'transformAssignedExpr',
  notices: []
}

Update:

I further investigate the issue and it seems like it is because of the parsers in the node-postgres library. When I debug the query generated by slonik is as the following:

finalSql = 'insert into project (uuid, title, email_service, supported_locales) select * from unnest($1::"text"[], $2::"text"[], $3::"jsonb"[], $4::"text"[][]) returning *'
finalValues = [
  [ '123jk1h3jh3' ],
  [ 'Title.' ],
  [ '{"prop":"value"}' ],
  [ [ 'en_US' ] ]
]

Slonik sends these parameters to node-postgres as stated here:

slonik/src/connectionMethods/query.ts

Line 22 in 91efde6

const result: QueryResult & {notices?: NoticeType[], } = await finalConnection.query(finalSql, finalValues);

[muratgozel]

Well, I also searched the issues of node-postgres and realized that there is no easy way to achieve this. The solution I have found requires two updates in the original code.

First, you need to serialize the array value into its PostgreSQL format. So ['en_US'] should become {"en_US"}. To achieve this, I have used pg-format package (you can manually dump it but pg-format is recommended way of doing it as far as I understand.):

const pgformat = require('pg-format')
const arrliteral = '{' + pgformat('%I', ['en_US']) + '}'

Second, you need to define columns along with their types inside the SELECT statement and append column names at the end of it:

await pgconn.query(sql`
  insert into project (uuid, title, email_service, supported_locales) 
  select uuid::text, title::text, email_service::jsonb, supported_locales::text[] 
  from ${selectQuery} 
  as t (uuid, title, email_service, supported_locales) 
  returning *
`)

So the test will look like this at final:

(async () => {
  const {createPool, sql} = require('slonik')
  const pgformat = require('pg-format')
  const pgpool = createPool('postgresql://postgres@127.0.0.1:15432/auth')
  await pgpool.connect(async pgconn => {
    const arrliteral = '{' + pgformat('%I', ['en_US']) + '}'
    const selectQuery = sql.unnest([
      ['123jk1h3jh3', 'Title.', JSON.stringify({prop: 'value'}), arrliteral]
    ], ['text', 'text', 'jsonb', 'text[]'])

    await pgconn.query(sql`insert into project (uuid, title, email_service, supported_locales) select uuid::text, title::text, email_service::jsonb, supported_locales::text[] from ${selectQuery} as t (uuid, title, email_service, supported_locales) returning *`)
  })
})()

and it will INSERT.

Of course I still wonder if there is any other solution to achieve this and if slonik internally can provide automatic handling of this or not.