Malformed Array Literal

[sujeshthekkepatt]

Hai,
I am using sequelize for the migration task instead of the default automigrate function. I have created a column "tags" with datatype Array in sequelize. And created the model using "lb model" and defined the type of tags as the array of string. But when i POST to /api/users it throws malformed array literal error. Turns out the default automigrate function create the type of "tags" to "text" not to actual postgres array. Any way to use the default Postgres array type in here?
My model definition is as,

{
"name": "user",
"plural": "users",
"base": "mfy-base",
"idInjection": false,
"options": {
"validateUpsert": true
},
"forceId": false,
"properties": {
"id": {
"type": "string",
"id": true,
"required": false,
"defaultFn": "uuid",
"postgresql": {
"dataType": "uuid"
}
},
"psid": {
"type": "string"
},
"profile_pic": {
"type": "string"
},
"gender": {
"type": "string"
},
"locale": {
"type": "string"
},
"timezone": {
"type": "string"
},
"status": {
"type": "string",
"default": "unread"
},
"tags": {
"type": ["string"]
},
"last_message": {
"type": "object"
},
"last_fb_message_at": {
"type": "date",
"defaultFn": "now"
},
"mmail": {
"type": "string"
},
"is_mmail_allowed": {
"type": "boolean",
"default": true
},
"is_messaging_allowed": {
"type": "boolean",
"default": true
},
"first_name": {
"type": "string"
},
"last_name": {
"type": "string"
},
"type": {
"type": "string"
}
},
"validations": [],
"relations": {
"page": {
"type": "belongsTo",
"model": "page",
"foreignKey": "",
"options": {
"nestRemoting": true
}
},
"events": {
"type": "hasMany",
"model": "event",
"foreignKey": "",
"options": {
"nestRemoting": true
}
}
},
"acls": [],
"methods": {}
}

[padelagani]

@sujeshthekkepatt

Have you got any solution for this? I am also facing the same issue.

@bajtos Any update on this?

[sujeshthekkepatt]

No. You may have to convert the type to 'text' at the database level. Since they are not doing any DB level operations it is very difficult to modify.

[bajtos]

I suspect this is a bug in the code emitting SQL commands to define database tables and columns. I am not familiar with PostgreSQL and this connector codebase, thus I cannot really help here.

[JoergFechner]

Hello!

I faced this problem with TypeScript.

My solution is the following

@property.array(String, {
    postgresql: {
      dataType: 'varchar[]'
    }
  })
  labels?: string[];

Insert and Update works fine!

The only thing is to use find the loopback-style. I need something like this:

labels @> ARRAY['pommes','ketchup']::varchar[]

[JoergFechner]

Hello!

Little update.

@sujeshthekkepatt

You are right!

Same problem.

It only works for PATCH not for POST!!

Kind regards

Joerg

[lgzajac]

Hello!

Just a note:
Looking for a solution I found that someone (probably) solved this with a manual patch..
https://gist.github.com/shaharz/2095dcc025ab142c20af17e6a9fd6b9a

.. unfortunately I don't have time now to check it out, resolve naming issue (I guess it probably shouldn't create new operator in dao.js, but override 'inq' somehow), and create pull request...

But it maybe a way to go ..

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.

[Riplar]

I don't think this should be closed as the problem still persists. I am still unable to save a model with a property that contains an array of numbers into the database even if the field itself is a numeric[].
It always throws an error that the array is malformed.

[vikramkh]

I am also facing this issue right now

[lumiQV]

Also facing the same issue when attempting to create an entity with an array despite of having properly declared the property as an array in the model. Updating existing entities with the array seems to work.

While we have implemented some workarounds (such as manual create methods or creating entities without the array property set and then proceeding to update the entity with the array), this still seems like an active bug in postgres connector that should be fixed as it partially prevents us from properly using the array type provided by the database.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.

[entcor]

Small hack for correct it
default loopback database converter convert array to JSON serialized string like ["1","2","3"]
but postgres use another format like: {"1","2","3"}

it converter located in loopback-datasource-juggler/lib/dao.js
and we should change it

next code should call after loopback init:

              loopbackInstance.models.YOUMODELNAME._forDB = function(data) {
                if (!(this.getDataSource().isRelational && this.getDataSource().isRelational())) {
                  return data;
                }
                const res = {};
                for (const propName in data) {
                  const type = this.getPropertyType(propName);
                  if (data[propName] instanceof Array) {  
                    res[propName] = `{${data[propName].map(el => `"${el}"`).join(',')}}`; // <<<<<< correct array converter 
                  } else
                  if (type === 'JSON' || type === 'Any' || type === 'Object') {
                    res[propName] = JSON.stringify(data[propName]);
                  } else {
                    res[propName] = data[propName];
                  }
                }
                return res;
              };

you should change YOUMODELNAME