Problem

When upgrading Posit Workbench which is using a Postgres database, you may run into an issue that looks like this:

ERROR Could not update database to latest schema; 

ERROR database error 1 (Cannot execute query. Fatal error. ERROR: there is no unique constraint matching given keys for referenced table "tablename"

Where tablename is a name of the target table.

Solution

The error that you're seeing typically occurs when you upgrade, downgrade, and then attempt to re-upgrade Workbench. To resolve this, you will need to point your Workbench installation to a clean database that has no existing tables with an empty schema. You can either create a new database and point your /etc/rstudio/database.conf to the new database, or alternatively, clear the current database that Workbench is connected to.

Once complete, you can run the Posit Workbench migration tool which can be found here:

https://docs.posit.co/ide/server-pro/database/migration.html