Question 114496 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – AWS RDS Array Parameters are not Supported

I am trying to save an array of values in AWS RDS. When I go into the RDS query editor I am able to run this insert without any issues:

insert into picture_tags (picture_id, tag_id)
values (picId, unnest(ARRAY[tagId1, tagId2])::uuid);
(the IDs are UUIDs but to save space I just left them as variable names).

I am using the data-api-client for all of my queries except for this one because it cannot handle arrays per the documentation.

Using just the basic AWS-SDK I have the following code:

const pictureId = '3e1eb325-95fa-4229-9597-4e2f9f27a2df'
const tagIds = [
  'cd4bb6dc-9c74-4ed1-b66c-f0865a792aaa',
  '517f1d68-e964-4564-a9d0-d4b776c0af4d'
]

const db = new aws.RDSDataService()

const sql = `
  INSERT INTO picture_tags (picture_id, tag_id)
  VALUES (:pictureId, unnest(ARRAY[:tagIds]));
`

const params = {
  sql,
  parameters: [
    { name: 'picture_id', value: { stringValue: pictureId }},
    { name: 'tag_id', value: { arrayValue: { stringValues: tagIds }}}
  ],
  secretArn: 'secretArn',
  resourceArn: 'resourceArn',
  database: 'databaseName',
}

const res = await db.executeStatement(params).promise()

I then get the following errors:

BadRequestException: Array parameters are not supported.
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Turns out this is possible using the data-api-client with a little workaround:

    const pictureId = '3e1eb325-95fa-4229-9597-4e2f9f27a2df'
const tagIds = [
  'cd4bb6dc-9c74-4ed1-b66c-f0865a792aaa',
  '517f1d68-e964-4564-a9d0-d4b776c0af4d'
]

const sql = `
  INSERT INTO picture_tags (picture_id, tag_id)
  VALUES (:pictureId, unnest(array[:tagIds]));
`

const sqlValues = [
  { name: 'pictureId', value: pictureId, cast: 'uuid' },
  { name: 'tagIds', value: `{${tagIds.join(',')}}`, cast: 'uuid[]' },
]

try {
  await rdsService.query(sql, sqlValues)
  return { status: 200 }
} catch (err) {
  console.log('Error : ', err)
  throw err
}

    {${tagIds.join(',')}} is the part to pay attention to. Converting the array into a string will make it work. I'm not sure if this causes any issues with SQL injections but it's a working solution.

    Replace tagIds with your array, change the cast and you should be good. Feel free to read more here:

    https://github.com/aws/aws-sdk/issues/9


  2. 0

    It’s also possible to do using the below method. This is applied to a text array (text[]).

    Make sure you parse the inputs before to prevent any security issues.

    1. Parse the array into a string to have a ‘{}’ format with each element as a string with double quotes.
    const array = ['a', 'b', 'c']
let insertString = '{"';
for (let x = 0; x < array.length; x++) {
        if (x === 0){
          insertString += array[x] + '", "'
        } else if (d + 1 === array.length){
          insertString += array[x] + '"}'
        } else {
          insertString += array[x] + '", "'
        }
}
    1. Insert string in query, do not use in params. It should look something like this:
    `INSERT INTO schema.table (attribute1) VALUES ('${insertString}')`

    The resulting query will look like this:

    INSERT INTO schema.table (attribute1) VALUES ('{"a", "b", "c"}')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search