1

I want to insert an array of strings in one column of a postgresql table using waterline ORM of sailsjs.

I have tried making the model Users.js like this :

 interest:{
      type: 'string',
      required: false,
      columnType: 'array'
    }

The insert query goes like this:

Users.create({ interest : ['programming'] });

The datatype in postgre table for interest column is character varying[].

When i try to perform an insert using this setup, it throws an error:

 Specified value (a array: [ 'programming' ]) doesn't match the expected type: 'string'

how can I insert an array in the postgresql table, how should the model look like?

Any help would be appreciated, thanks!

Rajan Sharma
  • 2,211
  • 3
  • 21
  • 33
  • You can use curly braces when inserting an array into a postgres db. So have you tried stringifying the array before creating it. For example, `Users.create({interest : JSON.stringify(['programming'])})` – Glen Apr 08 '19 at 10:30
  • @Glen It gives an error `malformed array literal` – Rajan Sharma Apr 08 '19 at 10:32
  • 1
    How about `Users.create({interest : JSON.stringify(['programming']).replace(/\[/g, '{').replace(/]/g, '}')})`. Very ugly though :) – Glen Apr 08 '19 at 10:42
  • @Glen Thanks, It works fine.But I think there could be a more proper way of doing that. – Rajan Sharma Apr 08 '19 at 11:19
  • 1
    Had a quick look into the code, can you try changing the attribute type to json, `interest: { type: 'json', required: false, columnType: 'array' }` and just pass like `Users.create({ interest : ['programming'] });` – Glen Apr 08 '19 at 13:54
  • @Glen It produces the same error `malformed array literal: "["programming"]"` – Rajan Sharma Apr 09 '19 at 06:34
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191503/discussion-between-rajan-sharma-and-glen). – Rajan Sharma Apr 09 '19 at 06:47

1 Answers1

3

I use PG arrays quite a bit throughout my projects and have never had any issues using type: 'ref' then specifying a postgres array type in columnType. Something like the following:

 things: {
  type: 'ref',
  columnType: 'text[]',
  defaultsTo: null, // or defaultsTo: []
  description: 'An array of stringy-things',
},

PG array type docs: https://www.postgresql.org/docs/9.1/arrays.html, but basically you want probably want to use <TYPE>[] for your columnTypes, ie integer[], etc

nahanil
  • 522
  • 3
  • 9