How to insert an array using sails waterline into a postgresql table?

Question

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!

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
– Glen, Commented Apr 8, 2019 at 10:30
How about Users.create({interest : JSON.stringify(['programming']).replace(/\[/g, '{').replace(/]/g, '}')}). Very ugly though :) — Glen
– Glen, Commented Apr 8, 2019 at 10:42
@Glen Thanks, It works fine.But I think there could be a more proper way of doing that. — Rajan Sharma
– Rajan Sharma, Commented Apr 8, 2019 at 11:19
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
– Glen, Commented Apr 8, 2019 at 13:54

nahanil · Accepted Answer · 2019-04-12 01:35:28Z

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

edited Apr 12, 2019 at 1:35

answered Apr 11, 2019 at 12:24

nahanil

5223 silver badges10 bronze badges

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

Collectives™ on Stack Overflow

How to insert an array using sails waterline into a postgresql table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related