cli: ERROR: multiple primary keys for table

Describe the bug ERROR: multiple primary keys for table.

To Reproduce Steps to reproduce the behavior:

  1. Create a new project
  2. Add tables via Dashboard
  3. Connect project with via CLI and Github via link and supabase db pull which populates migrations
  4. supabase db push
  5. Error

Expected behavior Should not error

Screenshots

supabase db pull
Connecting to remote database...
Schema written to supabase/migrations/20230827014200_remote_schema.sql
Update remote migration history table? [Y/n] n
Finished supabase db pull.
supabase db push
Applying migration 20230827014200_remote_schema.sql...
ERROR: multiple primary keys for table "app_users" are not allowed (SQLSTATE 42P16)
At statement 105: ALTER TABLE ONLY "public"."app_users"                            
    ADD CONSTRAINT "app_users_pkey" PRIMARY KEY ("id")                             
Try rerunning the command with --debug to troubleshoot the error.

Desktop (please complete the following information):

  • OS: MacOS [e.g. iOS]
  • Version of CLI v1.88.0

Additional context Add any other context about the problem here.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 3
  • Comments: 22 (7 by maintainers)

Most upvoted comments

@AmitMirgal you need to use the beta version for now npx supabase@beta db pull

The fix will be released to stable next Tuesday.

I am facing a similar issue while trying to setup a local instance. (also tried the beta pull.

I believe the issues still exists … having the same issue. supabase CLI version - 1.131.5

supabase db pull

error output

Connecting to remote database...
Setting up initial schema....
Applying migration 20240117205057_remote_schema.sql...
ERROR: multiple primary keys for table "schema_migrations" are not allowed (SQLSTATE 42P16)
At statement 90: ALTER TABLE ONLY "supabase_migrations"."schema_migrations"
    ADD CONSTRAINT "schema_migrations_pkey" PRIMARY KEY ("version")
Try rerunning the command with --debug to troubleshoot the error.

I believe this is a regression introduced by this PR https://github.com/supabase/cli/pull/1783. I will work on a fix.

I was able to work around this and fix the error. Delele all the migration files and to start fresh.

Insted of using the command

supabase db pull

to pull my schema from my remote DB. I used this command

supabase db diff -f migration_file_name --linked

make sure you have liked to your supabase instace to your CLI before running the above command ☝🏼

Note --linked means you have you DB on supabase.com, if you have a self hosted instance you replace --linked with --db-url DB_URL

After you have succesfully have your new migration files populated, run the command

supabsae db reset

Hope it helps you all

Same…