sequelize/sequelize

Postgresql : Can't update row with an empty array

Open

#11.748 aperta il 15 dic 2019

Vedi su GitHub
 (5 commenti) (1 reazione) (0 assegnatari)TypeScript (4271 fork)batch import
dialect: postgresexisting workaroundgood first issuestatus: understoodtype: bug

Metriche repository

Star
 (29.527 star)
Metriche merge PR
 (Merge medio 5h 24m) (1 PR mergiata in 30 g)

Descrizione

Issue Description

What are you doing?

I have a model that contained precomputed fields including a column that is an array of int (tags_ids). Here is its definition :

'use strict';

// This sub table is useful to save computation times for search
module.exports = (sequelize, DataTypes) => {
    let Exercise_Metrics = sequelize.define('Exercise_Metrics', {
        // to count how many votes for this exercise
        vote_count: {
            type: DataTypes.INTEGER,
            defaultValue: 0,
            allowNull: false
        },
        // the average score
        avg_vote_score: {
            type: DataTypes.DECIMAL(3,2),
            defaultValue: 0.0,
            allowNull: false
        },
        // the tags ids, pre-retrieved so that search is faster
        tags_ids: {
            type: DataTypes.ARRAY(DataTypes.INTEGER),
            allowNull: false,
            defaultValue: []
        }
    }, {
        // https://sequelize.org/master/manual/models-definition.html#configuration
        timestamps: false,
        tableName: "Exercises_Metrics"
    });

    Exercise_Metrics.associate = function (models) {
        // each exercise has only one Exercise Metrics
        models.Exercise_Metrics.belongsTo(models.Exercise, {
            as: "exercise",
            foreignKey: {
                name: "exercise_id",
                allowNull: false
            }
        })
    };

    return Exercise_Metrics;
};

With some situations, it may be necessary to update this column to an empty array. So I tried this kind of query :

...
some_instance.update({tags_ids: []})
...

What do you expect to happen?

I expected that to work and update the row.

What is actually happening?

Postgresql refused the query, saying that an empty array [] cannot be casted to array[]::int[].

Environment

  • Sequelize version: 5.21.2
  • Node.js version: v10.16.0
  • Operating System: Windows 10 version 1909

Issue Template Checklist

How does this problem relate to dialects?

  • I think this problem happens regardless of the dialect.
  • I think this problem happens only for the following dialect(s): Postgres
  • I don't know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

Would you be willing to resolve this issue by submitting a Pull Request?

  • Yes, I have the time and I know how to start.
  • Yes, I have the time but I don't know how to start, I would need guidance.
  • No, I don't have the time, although I believe I could do it if I had the time...
  • No, I don't have the time and I wouldn't even know how to start.

Guida contributor