Postgres array datatype in waterline being converted to text

Question

I need to create an App. We are using PostgreSQL as the underlying DB. The reason we decided on Postgres was because we wanted a relational database due to various entities and their relationships (which did not seem ideal for NoSQL databases like Mongo since there will be multiple joins) and at the same time we wanted to leverage the advantage of array and JSON datatypes of Postgres which is not available in MySQL.

We are using SailsJS framework due to its simplicity, built in sockets.io API and my prior (though very limited) experience with this framework (with MongoDB as underlying DB). It uses waterline as the underlying ORM.

Model.query() is really an awesome feature which allows me to run native SQL queries.

The problem that I am facing is related to defining a model with array datatype of Postgres. By default, if I check in database, the column is changed to text datatype in DB. I need to manipulate array fields using postgre's build in array functions. But for that, I need to be able to create a table with the required type. I need to know how is that possible ?

sgress454 · Accepted Answer · 2014-06-16 16:23:35Z

4

Use the json field type when defining your Waterline model, e.g.:

module.exports = {

    attributes: {

        name: 'string',
        my_array: 'json',
        ...etc...
    }

}

Then you can save array data directly into the model, e.g.:

User.create({name: 'bob', my_array: [1,2,3]}).exec(...);

and it will be translated to/from JSON by the underlying adapter. In the case of sails-postgresql, it will use the json column type.

answered Jun 16, 2014 at 16:23

sgress454

25k4 gold badges77 silver badges93 bronze badges

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

3 Comments

Mandeep Singh Over a year ago

Thanks Scott.. I am getting an error when I send the packet via post request { "intColumn" : 1, "jsonColumn": [1,2,3] } Error message : { "status": 500, "errors": [ "invalid input syntax for type json" ] }

sgress454 Over a year ago

Not sure, works like a charm for me. What version of Sails / sails-postgresql are you using?

Mandeep Singh Over a year ago

Hi Scott, sorry for the late response. It worked on sails beta. But how do I access the keys of the json array ? for example select my_array[0] does not work

stefan.schwetschke · Accepted Answer · 2014-06-12 13:16:04Z

0

I don't have node.js running here, so it cannot test it right now. But try the following:

Use the array constructor syntax
Use proper enquoting

To 1)

 SELECT ARRAY[1,2,3+4];

To 2)

The PostgreSQL documentation states:

Remember that what you write in an SQL command will first be interpreted as a string literal, and then as an array. This doubles the number of backslashes you need. For example, to insert a text array value containing a backslash and a double quote, you'd need to write:

INSERT ... VALUES (E'{"\\\\","\\""}');

Example fiddle with proper enquoting.

edited Jun 12, 2014 at 13:16

answered Jun 12, 2014 at 12:59

stefan.schwetschke

8,9621 gold badge29 silver badges30 bronze badges

2 Comments

Mandeep Singh Over a year ago

Hi Stefan, I guess you misunderstood the question. The problem I am facing is not related to array datatype of Postgres but how waterline as an ORM handles them. I can work with array datatype comfortably but waterline does not have this functionality. I am looking for a workaround

stefan.schwetschke Over a year ago

Ah, OK. Then I have to look into this later. I thought you had just the problem that queries you give to your ORM are not properly quoted.

Collectives™ on Stack Overflow

Postgres array datatype in waterline being converted to text

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related