Returning Invalid syntax for type json on arrays

[jrf0110]

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 */
});

[booo]

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.

[jrf0110]

@booo Yeah, I was thinking that may be the case. Well, in that case, non-issue!

[badave]

@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.

[spollack]

see also: #374

[jrf0110]

@badave Doesn't your proposed behavior require the same amount of work to insert JSON? You're gonna be stringifying the JSON anyway.

[badave]

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.

[jrf0110]

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.

[eriknyk]

Hi, this issue should be reopened, since in postgres shell is possible to execute:

INSERT INTO public.my_table ("userId", "personId", books) VALUES (1, 1, '[1,2,3]');

Best Regards.