pg-protocol not support js-object value

[transtone]

"aa": [
	{"bb": "cc"}
]

this will treat as a error json!

error: invalid input syntax for type json
    at Parser.parseErrorMessage (path\node_modules\pg-protocol\dist\parser.js:287:98)
    at Parser.handlePacket (path\node_modules\pg-protocol\dist\parser.js:126:29)
    at Parser.parse (path\node_modules\pg-protocol\dist\parser.js:39:38)
    at Socket.<anonymous> (path\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (node:events:526: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:190:23) {
  length: 169,
  severity: 'ERROR',
  code: '22P02',
  detail: 'Expected ":", but found ",".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'JSON data, line 1: {"{\\"bb\\":\\"cc\\"}",...',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'json.c',
  line: '1203',
  routine: 'report_parse_error'

if insert or update a jsonb field with js object, then will get the error.

must use JSON.stringify(someObject).

[brianc]

thanks for the report - can you include a bit more detail on how you made this bug trigger?

[transtone]

CREATE TABLE public.test (
	id varchar NOT NULL DEFAULT uuid_generate_v4(),
	"data" jsonb NULL,
);

const insertData= [{"bb": "cc"}]

const insertQuery = {
  text: 'INSERT INTO public.test (data) VALUES ($1) ',
  values: [ insertData ]
}
const client = await pool.connect()
try {
    await client.query('BEGIN')
    await client.query(insertQuery)
    await client.query('COMMIT')
 } catch (e) {
    await client.query('ROLLBACK')
    console.log(e)
 }
client.release()

then will get the error.

it must use values: [JSON.stringify(insertData)] , then everything goes ok.

[transtone]

in the doc:
https://node-postgres.com/features/types#uuid--json--jsonb

const createTableText = `
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
 
CREATE TEMP TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  data JSONB
);
`
// create our temp table
await client.query(createTableText)
 
const newUser = [{ email: '[email protected]' }]
// create a new user
await client.query('INSERT INTO users(data) VALUES($1)', [newUser])
 
const { rows } = await client.query('SELECT * FROM users')

if the newUser is Array, will triggle the error.

[charmander]

See #2012.