How can I delete unique constraints of thousands of indices in psql?

userC4id 0
Viewed 38

I accidentally set alter:true in the ORM and now it's generated thousands of rows that look like this:

    "users_email_key12076" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12077" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12078" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12079" UNIQUE CONSTRAINT, btree (email)
    "users_email_key1208" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12080" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12081" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12082" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12083" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12084" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12085" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12086" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12087" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12088" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12089" UNIQUE CONSTRAINT, btree (email)
    "users_email_key1209" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12090" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12091" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12092" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12093" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12094" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12095" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12096" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12097" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12098" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12099" UNIQUE CONSTRAINT, btree (email)
    "users_email_key121" UNIQUE CONSTRAINT, btree (email)
    "users_email_key1210" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12100" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12101" UNIQUE CONSTRAINT, btree (email)
    "users_email_key12102" UNIQUE CONSTRAINT, btree (email)

I can only delete each index like this:

alter table users drop constraint users_email_key12001;

so I copied and pasted it onto the psql command line, and I'm only able to delete 18 lines at a time, on each command line. I may have thousands of these indices. any way to get rid of them all at once?

database postgresql
1 Answers

You can do it in a single query.

The idea is to identify the constraints to be removed, to build a list of command to remove them and to execute them. For this, you can use psql /gexec functionality.

 SELECT format('alter table %I drop constraint %I;', rel.relname, conname)
FROM pg_catalog.pg_constraint con
    INNER JOIN pg_catalog.pg_class rel
      ON rel.oid = con.conrelid
    INNER JOIN pg_catalog.pg_namespace nsp
      ON nsp.oid = connamespace
WHERE nsp.nspname = 'public'
  AND rel.relname = 'users'
  AND conname ilike  'users_email_key%';\gexec

PS: there is no undo... you may want to print the commands (i.e. run without the \gexec flag) first.

JGH0
Related