From klodoma@ar-sd.net Mon Sep  6 14:02:55 2004
X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 371C45E48CA;
	Mon,  6 Sep 2004 15:02:55 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 19849-06; Mon,  6 Sep 2004 14:02:53 +0000 (GMT)
Received: from ar-sd.net (unknown [81.196.32.112])
	by svr1.postgresql.org (Postfix) with ESMTP id EF5A85E48DF;
	Mon,  6 Sep 2004 15:02:49 +0100 (BST)
Received: from localhost (localhost [127.0.0.1])
	by ar-sd.net (Postfix) with ESMTP
	id 3261D22A37; Mon,  6 Sep 2004 17:01:16 +0300 (EEST)
Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP
	id 5CB8B210A0; Mon,  6 Sep 2004 17:01:15 +0300 (EEST)
Message-ID: <023201c4941a$3ade6350$0b00a8c0@forge>
From: "Andrei Bintintan" <klodoma@ar-sd.net>
To: <pgsql-sql@postgresql.org>, <pgsql-admin@postgresql.org>
Subject: How to rename a constraint/trigger??
Date: Mon, 6 Sep 2004 17:03:02 +0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_022F_01C49433.5E74EAA0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2741.2600
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2742.200
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_40_50,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200409/54
X-Sequence-Number: 14757

This is a multi-part message in MIME format.

------=_NextPart_000_022F_01C49433.5E74EAA0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi to all,

I have a database, where in the tables I have around 100 constrains (link t=
o other tables) that don't have a name "<unnamed>" or they have a name like=
 "$1" "$2". Now, I have a module which bases on the same structure, but I g=
et some query errors from a "<unnamed>" constraint. I really don't know exa=
clty  which the constrain generates the error, in that table I have 12 such=
 constrains.=20

Does anyone have a script or it exist a command so that I can rename all th=
e "<unnamed>"constrains in this database?=20

I'm using postgre 7.3.2.

Thankx,
Andy.



------=_NextPart_000_022F_01C49433.5E74EAA0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2743.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Times New Roman, Times, serif"><FONT color=3D#000000>Hi =
to=20
all,</FONT></FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have a database, where in the tables I h=
ave=20
around 100 constrains (link to other tables) that don't have a name=20
"&lt;unnamed&gt;" or they have a name like "$1" "$2". Now, I have a module =
which=20
bases on the same structure, but I get some query errors from a=20
"&lt;unnamed&gt;" constraint. I really don't know exaclty&nbsp; which the=
=20
constrain&nbsp;generates the error, in that table I have 12 such constrains=
.=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Does anyone have a script or&nbsp;it exist=
 a=20
command so that I can rename all the "&lt;unnamed&gt;"constrains in this=20
database? </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I'm using postgre 7.3.2.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thankx,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Andy.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_022F_01C49433.5E74EAA0--




From josh@agliodbs.com Wed Sep  8 17:08:25 2004
X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 57A4A329CA4;
	Wed,  8 Sep 2004 18:08:25 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 57932-01; Wed,  8 Sep 2004 17:08:23 +0000 (GMT)
Received: from davinci.ethosmedia.com (server226.ethosmedia.com
	[209.128.84.226])
	by svr1.postgresql.org (Postfix) with ESMTP id 476E7329C67;
	Wed,  8 Sep 2004 18:08:23 +0100 (BST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8)
	with ESMTP id 6302550; Wed, 08 Sep 2004 10:09:40 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "Andrei Bintintan" <klodoma@ar-sd.net>,
	<pgsql-sql@postgresql.org>, <pgsql-admin@postgresql.org>
Subject: Re: [SQL] How to rename a constraint/trigger??
Date: Wed, 8 Sep 2004 10:05:00 -0700
User-Agent: KMail/1.4.3
References: <023201c4941a$3ade6350$0b00a8c0@forge>
In-Reply-To: <023201c4941a$3ade6350$0b00a8c0@forge>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200409081005.00682.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200409/77
X-Sequence-Number: 14780

Andrei,

> I have a database, where in the tables I have around 100 constrains (link
> to other tables) that don't have a name "<unnamed>" or they have a name
> like "$1" "$2". Now, I have a module which bases on the same structure, but
> I get some query errors from a "<unnamed>" constraint. I really don't know
> exaclty  which the constrain generates the error, in that table I have 12
> such constrains.

You'll be happy to know that in 7.4 we stopped using "<unnamed>".

> Does anyone have a script or it exist a command so that I can rename all
> the "<unnamed>"constrains in this database?

You can't rename constraints, you have to drop and re-create them under a new 
name (do this in a transaction for data safety).  However, this requires 
knowing what all those constraints do.   I'd suggest doing a text pg_dump 
file of your schema only (no data) and looking in the constraints section at 
the end.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco