Upgradation to 4.4.5 error : alter table could not create unique index

Questions and Answers
1
System Information
  • Strapi Version: - 4.4.5
  • Operating System: Windows
  • Database: Postgres
  • Node Version: 20
  • NPM Version: 10
  • Yarn Version:

Hi Everyone,

I’m trying to upgrade the strapi version to 4.4.5, and did all the migrations but i’m getting this error.

error: alter table "challenges_invited_users_links" add constraint "challenges_invited_users_links_unique" unique ("challenge_id", "user_id") - could not create unique index "challenges_invited_users_links_unique"

In my project i have challenge collection which has relation with user collection several times under different field names,the first being challenges_api_created_by_links which uses same fields like challenge_id and user_id, so i’m assuming this error is coming as it couldn’t create unique index as it consider it as duplicate, i’ve have similar relations(with user collection) duplicates in other collections as well which are essential for the project, so it will be a huge help if anyone can guide me in right direction.

2

Found out i had a duplication of data in the database,

SELECT challenge_id, user_id, COUNT(*)
FROM challenges_invited_users_links
GROUP BY challenge_id, user_id
HAVING COUNT(*) > 1;

This above query helped me to check for the duplicates. So i made a custom code(given below) from migration file to check all _links collections for checking duplicates and removing them.

const contentTypes = strapi.db.metadata;
      contentTypes.forEach((contentType) => {
        // Get attributes
        const attributes = contentType.attributes;

        // For each relation type, add the joinTable name to tablesToUpdate
        Object.values(attributes).forEach(async (attribute) => {
          if (
            attribute.type === 'relation' &&
            attribute.joinTable &&
            !attribute.relation.startsWith('morph') && // Ignore polymorphic relations
            attribute.joinTable.name.includes('_links')
          ) {
            let query = `SELECT ${attribute.joinTable.joinColumn.name}, ${attribute.joinTable.inverseJoinColumn.name}, COUNT(*)
                          FROM ${attribute.joinTable.name}
                            GROUP BY ${attribute.joinTable.joinColumn.name}, ${attribute.joinTable.inverseJoinColumn.name}
                            HAVING COUNT(*) > 1`;
            let { rows } = await strapi.db.connection.raw(query);
            if (rows.length) {
              let deleteDuplicateQuery = `DELETE FROM ${attribute.joinTable.name}
              WHERE ctid NOT IN (
                  SELECT MIN(ctid)
                  FROM ${attribute.joinTable.name}
                  GROUP BY ${attribute.joinTable.joinColumn.name}, ${attribute.joinTable.inverseJoinColumn.name}
              )`;
              let deleteDuplicates = await strapi.db.connection.raw(deleteDuplicateQuery);
            }
          }
        });
      });

Sharing it here so that it may help some one.