Postgres remove constraint by column names

Question

Is there a way I can remove a constraint based on column names?

I have postgres 8.4 and when I upgrade my project the upgrade fails because a constraint was named something different in a different version.

Basically, I need to remove a constraint if it exists or I can just remove the constraint using the column names.

The name of the constraint is the only thing that has changed. Any idea if that's possible?

In this case, I need to remove "patron_username_key"

discovery=# \d patron
                       Table "public.patron"
          Column          |            Type             | Modifiers
--------------------------+-----------------------------+-----------
 patron_id                | integer                     | not null
 create_date              | timestamp without time zone | not null
 row_version              | integer                     | not null
 display_name             | character varying(255)      | not null
 username                 | character varying(255)      | not null
 authentication_server_id | integer                     |
Indexes:
    "patron_pkey" PRIMARY KEY, btree (patron_id)
    "patron_username_key" UNIQUE, btree (username, authentication_server_id)

What kind of constraint? Foreign key? Check constraint? Not Null? — user330315
– user330315, Commented Oct 17, 2012 at 19:01

vyegorov · Accepted Answer · 2012-10-17 19:17:20Z

2

You can use System Catalogs to find information bout constraints. Still, some constraints, like keys, are mentioned in the separate pg_constraint table, while others, like NOT NULL, are essentially a columns in the pg_attribute table.

For the keys, you can use this query to get a list of constraint definitions:

SELECT pg_get_constraintdef(c.oid) AS def
  FROM pg_class t
  JOIN pg_constraint c ON c.conrelid=t.oid
 WHERE t.relkind='r' AND t.relname = 'table';

You can then filter out the ones that references your column and dynamically construct ALTER TABLE ... DROP CONSTRAINT ... statements.

answered Oct 17, 2012 at 19:17

vyegorov

23k7 gold badges61 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2012-10-17 19:27:04Z

Assuming that unique index is the result of adding a unique constraint, you can use the following SQL statement to remove that constraint:

do $$
declare 
  cons_name text;
begin
  select constraint_name 
     into cons_name
  from information_schema.constraint_column_usage  
  where constraint_schema = current_schema()
  and column_name in ('authentication_server_id', 'username')
  and table_name = 'patron'
  group by constraint_name
  having count(*) = 2;

  execute 'alter table patron drop constraint '||cons_name;
end;
$$

I'm not sure if this will work if you have "only" added a unique index (instead of a unique constraint).

If you need to do that for more than 2 columns you also need to adjust the having count(*) = 2 part to match the number of columns in the column_name in .. condition.

(As you did not specify your PostgreSQL version I'm assuming the current version)

Collectives™ on Stack Overflow

Postgres remove constraint by column names

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related