Thread: Rename a constraint

On 10/01/2009 19:15, Thom Brown wrote:
> I can't find anything in the documentation, but does anyone know if
> there is a way to rename a constraint?

I just tried it with a primary key...

test=# alter table t1 alter constraint t1_pk rename to t1_pp;
ERROR:  syntax error at or near "constraint"
LINE 1: alter constraint t1_pk rename to t1_pp;


... and as you can see it didn't work. I suppose you could always drop
and recreate it with a different name.

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

On 10 January 2009 19:22, Raymond O'Donnell <rod@iol.ie> wrote:
> On 10/01/2009 19:15, Thom Brown wrote:
>> I can't find anything in the documentation, but does anyone know if
>> there is a way to rename a constraint?
>
> I just tried it with a primary key...
>
> test=# alter table t1 alter constraint t1_pk rename to t1_pp;
> ERROR:  syntax error at or near "constraint"
> LINE 1: alter constraint t1_pk rename to t1_pp;
>
>
> ... and as you can see it didn't work. I suppose you could always drop
> and recreate it with a different name.

While this was a sufficient solution for the problem I was having back
then, it will be problematic for those with large tables as it means
re-validating the constraint against the entire table.

I notice Bruce submitted a change to allow the renaming of
constraints, but nothing ever came of it:
http://archives.postgresql.org/pgsql-patches/2006-02/msg00168.php

It's also in the TODO: http://wiki.postgresql.org/wiki/Todo#ALTER

Any chance of this being picked up for 9.2? :)

Thom

Thom Brown <thombrown@gmail.com> writes:
> On 10 January 2009 19:22, Raymond O'Donnell <rod@iol.ie> wrote:
>> On 10/01/2009 19:15, Thom Brown wrote:
>>> I can't find anything in the documentation, but does anyone know if
>>> there is a way to rename a constraint?

>> I just tried it with a primary key...
>>
>> test=# alter table t1 alter constraint t1_pk rename to t1_pp;
>> ERROR: �syntax error at or near "constraint"
>> LINE 1: alter constraint t1_pk rename to t1_pp;
>>
>> ... and as you can see it didn't work. I suppose you could always drop
>> and recreate it with a different name.

> While this was a sufficient solution for the problem I was having back
> then, it will be problematic for those with large tables as it means
> re-validating the constraint against the entire table.

Use ALTER INDEX RENAME to rename the index underlying the constraint.
The constraint will follow along.

            regards, tom lane

On 29 May 2011 16:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thombrown@gmail.com> writes:
>> On 10 January 2009 19:22, Raymond O'Donnell <rod@iol.ie> wrote:
>>> On 10/01/2009 19:15, Thom Brown wrote:
>>>> I can't find anything in the documentation, but does anyone know if
>>>> there is a way to rename a constraint?
>
>>> I just tried it with a primary key...
>>>
>>> test=# alter table t1 alter constraint t1_pk rename to t1_pp;
>>> ERROR:  syntax error at or near "constraint"
>>> LINE 1: alter constraint t1_pk rename to t1_pp;
>>>
>>> ... and as you can see it didn't work. I suppose you could always drop
>>> and recreate it with a different name.
>
>> While this was a sufficient solution for the problem I was having back
>> then, it will be problematic for those with large tables as it means
>> re-validating the constraint against the entire table.
>
> Use ALTER INDEX RENAME to rename the index underlying the constraint.
> The constraint will follow along.

Not all constraints are based on indexes though.

Thom