0

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.

5
  • your problem is the single quote ' you need to scape it with `\` (i think) Commented Mar 1, 2018 at 12:05
  • @CelsoLívero which single quote sorry? There's a few of them knocking about... Commented Mar 1, 2018 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') Commented Mar 1, 2018 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? Commented Mar 1, 2018 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. Commented Mar 1, 2018 at 21:19

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

3 Comments

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[].
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.
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: stackoverflow.com/questions/28473476/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.