node-postgres: Returning Invalid syntax for type json on arrays

When your column is type json and you try and insert a JSON array, you get the following error:

{ [error: invalid input syntax for type json]
  length: 154,
  name: 'error',
  severity: 'ERROR',
  code: '22P02',
  detail: 'Expected string or "}", but found "1".',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'JSON data, line 1: {1...',
  file: 'json.c',
  line: '665',
  routine: 'report_parse_error' }

For example, this works in PSQL:

insert into my_table ( data ) values ( '[1,2,3]' );

But this returns an error in node-pg:

client.query('insert into my_table ( data ) values ( $1 )', [ [1,2,3] ], function( error ){
  /* ERROR */
});

About this issue

Original URL
State: closed
Created 11 years ago
Reactions: 9
Comments: 29 (11 by maintainers)

Commits related to this issue

Released v1.0.6 Fix: "invalid input syntax for type json" error when setting jsonb column value to an array. See https://github.com/brianc/node-postgres/issues/442 Update npms to latest versions — committed to bigalorm/bigal by jgeurts 6 years ago
Re-architecture. Add Constants Classes, Setters and Getters, Optimize conversion between object and text, binary and hexBinary Yadamu: Enable TABLES parameter to be used to limit operations to a spec... — committed to markddrake/YADAMU---Yet-Another-DAta-Migration-Utility by markddrake 4 years ago
Re-architecture. Add Constants Classes, Setters and Getters, Optimize conversions between object and text, binary and hexBinary Yadamu: Enable TABLES parameter to be used to limit operations to a spe... — committed to markddrake/YADAMU---Yet-Another-DAta-Migration-Utility by markddrake 4 years ago

Most upvoted comments

We convert javascript arrays into postgres arrays. If you stringify your input array it should work:

client.query('INSERT INTO my_table (data) VALUES ($1)', [ JSON.stringify([1,2,3])], handler);

We don’t now the column type in advance so we can’t do a auto conversions of the array.

+28

booo on Sep 16, 2013

Would it make sense for node-postgres to intercept this error and augment it with something like Are you inserting an array into a JSON column? See <link to docs>? Helpful error messages make for a nice developer experience.

+22

tamlyn on Jun 29, 2017

@booo @brianc This behavior is a bug. Converting data from [{}, {}] to {{}, {}} should not be determined by pg.

The only solution is to use JSON.stringify before running the insert query? What if you’re running an ORM? What if you are unaware of this limitation?

Just because pg doesn’t know the column type in advance does not mean you should convert to the syntax of column type “array”. It might be helpful, but it’s also a pretty big assumption and makes it really hard to use an array in a json field, despite it being supported and normal behavior by the database.

The better behavior would be to leave converting data types to the client. Both possibilities would be easy. As is, it’s really hard to get the expected behavior using what’s available.

+15

badave on Oct 31, 2013

A way to configure the default behavior would be desirable.

sparebytes on Jun 30, 2017

Another option is to make your column have a type of jsonb[] rather than jsonb.

danvk on Aug 25, 2020

Proposed behavior passing {} or [] into a column without the library being opinionated about converting it into a postgres array type. The larger problem isn’t the JSON.stringify, it’s the behavior of pg. As a matter of fact, the point should be that I shouldn’t have to do a JSON.stringify because the library already handles it with JSON objects without stringifying.

This shouldn’t be a mandatory conversion. It’s that simple. The database error is “invalid input syntax for type json”, an insufficient error message as a user to conclude its caused by the lib transforming the data. It’s transforming valid JSON into invalid JSON.

However, if I were using an array type and inserting an array into postgres with the incorrect format, I would understand the invalid input syntax for type array because it isn’t JSON format, it requires an array format. At that point I would look for a solution that converted my data into the correct form to insert into the pg.

And I could understand it better if the array column type was any good in postgres, but it’s complete crap. I’d prefer to use a json array because it is more recognizable.

badave on Oct 31, 2013

Yeah, I see what you’re saying. It’s a bit funky that node-pg assumes to stringify Objects but not Arrays. I think making it more consistent, or rather, no incoming magic (I like the magic coming out though!) would be for the best. It seems a little arbitrary, especially now that JSON has been added to the data-type mix, that JS arrays are assumed to be PG Arrays, when they could be type json or even hstore.

My preference would be to make no assumptions and leave it up to the library consumer. I’m not sure if that means node-pg should JSON.stringify Arrays and Objects by default, as that would be trading one magic for another. But it certainly does seem like a sane default.

jrf0110 on Oct 31, 2013