Setting a JSON array of strings as a column in a PostgreSQL table

Question

I'm a beginner to SQL (as you'll soon be able to tell...) and I cannot for the life of me figure out how to best insert a simple JSON array of strings into a PostgreSQL table. I suspect the solution is quite easy but I've spent just a bit too long trying to puzzle it out on my own.

First I create the table:

CREATE TABLE test (
  id                    serial PRIMARY KEY
  my_array              jsonb
);

Where the array is of type JSON. Insert some initial data:

INSERT INTO test (id, my_array) VALUES(1, '[]');

And now I want to update the myarray column with a JSON array using Node.js node-postgres. The array might look something like

const myArray = ['foo', 'bar', 'foobar\'s escaped character emporium'];

await db.none(
  'UPDATE test ' +
  `SET my_array = ${myArray} ` +
  'WHERE id = 1'
);

This results in

error: syntax error at or near ","

Ok, so what if I do

await db.none(
  'UPDATE test ' +
  `SET my_array = "${myArray}" ` +
  'WHERE id = 1'
);

I get

error: column "foo,bar,foobar's escaped character emporium" does not exist

and if I do

await db.none(
  'UPDATE test ' +
  `SET my_array = ${JSON.stringify(myArray)} ` +
  'WHERE id = 1'
);

I get

ERROR error: syntax error at or near "["

Finally, if I do

await db.none(
    'UPDATE test ' +
    `SET my_array = '${JSON.stringify(myArray)}' ` +
    'WHERE id = 1'
);

I end up with

stack=error: syntax error at or near "s"

I've also tried storing the data data as a native PostgreSQL array but I encounter similar problems:

CREATE TABLE test (
  id                    serial PRIMARY KEY
  my_array              text ARRAY
);

INSERT INTO test (id, my_array) VALUES(1, '{}');

Then

const myArray = ['foo', 'bar', 'foobar\'s escaped character emporium'];

await db.none(
  'UPDATE test ' +
  `SET my_array = ${myArray} ` +
  'WHERE id = 1'
);

gives

stack=error: syntax error at or near ","

Similar variations using JSON.stringify() and combinations of different quotes have proved fruitless as well. I kind of expected this approach to be less likely to work as PostgreSQL arrays are just a different format, but I was hoping there might be some kind of attempt at coercion. Reading through the documentation I can't spot any obvious way to convert a JSON array into the expected format for a PostgreSQL array.

your problem is the single quote `'` you need to scape it with `\\` (i think) — Celso Lívero, Mar 01 '18 at 12:05
@CelsoLívero which single quote sorry? There's a few of them knocking about... — Tom, Mar 01 '18 at 12:28
all of them, you have to use the escape character to be inserted, for example: assuming I want to insert "baba o'riley" in a column, I should do this: INSERT INTO table (column) VALUES ('baba o\' riley') — Celso Lívero, Mar 01 '18 at 12:32
As far as I can tell, singlequotes in the array elements (strings) are already escaped. They get unescaped by `JSON.stringify()`, but that's because it encapsulates each element in doublequotes. Are you saying I should use `JSON.stringify()` and _then_ escape singlequotes in the resulting string and _then_ surround the entire thing with singlequotes? — Tom, Mar 01 '18 at 12:40
You are mixing ES6 template strings with regular strings + pg-promise string formatting, so you don't know what is going on there, generating invalid SQL. You should stick to one syntax, and then update the SQL formatting accordingly. — vitaly-t, Mar 01 '18 at 21:19

score 0 · Accepted Answer · answered Mar 01 '18 at 13:01

0

Consider using a Parameterized query or Prepared statements. That will help with you with qoutes and get protection against SQL injection as a bonus.

answered Mar 01 '18 at 13:01

Chris

958
8
19

Parametrised query works a treat! Thanks! For posterity, the query that works is `await db.none( 'UPDATE test ' + 'SET my_array = ${myArray} ' + 'WHERE id = 1', { myArray });`. When I tried this `my_array` had type `text[]`. – Tom Mar 01 '18 at 14:19
The problem got nothing to do with parameterized queries or prepared statements. He just needs to fix his SQL query formatting. Taking him away into another module isn't a solution. – vitaly-t Mar 01 '18 at 21:21
Actually, i'm not pointing him to another module. He states that he uses that particular module in his question. Futhermore, it is a bad practice to build queries by string concatenation. See this question: https://stackoverflow.com/questions/28473476/avoid-string-concatenation-to-create-queries/28473802 – Chris Mar 02 '18 at 15:59

Setting a JSON array of strings as a column in a PostgreSQL table

1 Answers1