From amitjain@synechron.com Thu May  27 12:31:22 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 9B3A7632777
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 09:31:22 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 87647-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 12:31:13 +0000 (UTC)
X-Greylist: delayed 00:05:08.696853 by SQLgrey-1.7.6
Received: from smtp172.dfw.emailsrvr.com (smtp172.dfw.emailsrvr.com
	[67.192.241.172])
	by mail.postgresql.org (Postfix) with ESMTP id EE30E632755
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 09:31:14 -0300 (ADT)
Received: from relay17.relay.dfw.mlsrvr.com (localhost [127.0.0.1])
	by relay17.relay.dfw.mlsrvr.com (SMTP Server) with ESMTP id
	7526C2C704B8
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 08:26:04 -0400 (EDT)
Received: by relay17.relay.dfw.mlsrvr.com (Authenticated sender:
	amitjain-AT-synechron.com) with ESMTPSA id 9396F2C7246E
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 08:26:03 -0400 (EDT)
Message-ID: <4BFE64D4.4020107@synechron.com>
Date: Thu, 27 May 2010 17:55:56 +0530
From: Amit jain <amitjain@synechron.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US;
	rv:1.9.1.9) Gecko/20100317 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: How to add constraints without validating data.
Content-Type: multipart/alternative;
	boundary="------------060509050208000206040209"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201005/261
X-Sequence-Number: 34734

This is a multi-part message in MIME format.
--------------060509050208000206040209
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Gurus

Is there any way in postgreSQL to apply constraints forcefully without 
checking/validating Data ? It should validate only those records which 
comes after constraints are applied.

I need this help as data is already loaded in database but "ALTER TABLE" 
sql for adding CONSTRAINTS is taking enormous time.

Thanks for ideas/suggestion in Advance !!


-- 

Regards,

*Amit Jain*
*Synechron Technologies Pvt. Ltd.*  4th  Floor, B Wing, Block 1.2, Embassy Techzone,
Rajiv Gandhi Infotech Park, Hinjewadi Phase II, Pune 411 057.
Phone : +91.20.40262000 Ext 2163 | Mobile:+91.9561298565  |VoIP : +1.732.415.0894
http://www.synechron.com  <http://www.synechron.com/>   MailTo: amitjain@synechron.com  <mailto:nilnandanj@synechron.com>




--------------060509050208000206040209
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Gurus<br>
<br>
Is there any way in postgreSQL to apply constraints forcefully without
checking/validating Data ? It should validate only those records which
comes after constraints are applied. <br>
<br>
I need this help as data is already loaded in database but "ALTER
TABLE" sql for adding CONSTRAINTS is taking enormous time.<br>
<br>
Thanks for ideas/suggestion in Advance !!<br>
<br>
<br>
<div class="moz-signature">-- <br>
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=ISO-8859-1">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.2  (Win32)">
<meta name="AUTHOR" content="Amit jain">
<meta name="CREATED" content="20100427;20254400">
<meta name="CHANGEDBY" content="Amit jain">
<meta name="CHANGED" content="20100427;20482500">
<style type="text/css">
	<!--
		@page { margin: 0.79in }
		P { margin-bottom: 0.08in }
		PRE.cjk { font-family: "NSimSun", monospace }
		A:link { so-language: zxx }
	-->
	</style>
<pre class="western"><font face="Verdana, sans-serif"><font
 style="font-size: 9pt;" size="2"><font face="Verdana"><font size="2">Regards,</font></font></font></font>

<font color="#800080"><font face="Verdana, sans-serif"><font
 style="font-size: 11pt;" size="2"><b>Amit Jain </b></font></font></font>
<font color="#666666"><font face="Verdana, sans-serif"><font
 style="font-size: 9pt;" size="2"><b>Synechron Technologies Pvt. Ltd.</b> 4th  Floor, B Wing, Block 1.2, Embassy Techzone,</font></font></font>
<font color="#666666"><font face="Verdana, sans-serif"><font
 style="font-size: 9pt;" size="2">Rajiv Gandhi Infotech Park, Hinjewadi Phase II, Pune 411 057. </font></font></font>
<font color="#666666"><font face="Verdana, sans-serif"><font
 style="font-size: 9pt;" size="2">Phone : +91.20.40262000 Ext 2163 | Mobile:+91.9561298565</font> | <font
 style="font-size: 9pt;" size="2">VoIP : +1.732.415.0894</font></font></font>
<font color="#666666"><font face="Verdana, sans-serif"><font
 style="font-size: 9pt;" size="2"><a href="http://www.synechron.com/">http://www.synechron.com</a>  MailTo: amitjain<a
 href="mailto:nilnandanj@synechron.com">@synechron.com</a></font></font></font></pre>
<p><br>
<br>
</p>
</div>
</body>
</html>

--------------060509050208000206040209--



From lists@itech7.com Thu May  27 12:43:20 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id D9CCD632A53
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 09:43:20 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 69798-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 12:43:12 +0000 (UTC)
X-Greylist: delayed 00:08:04.022078 by SQLgrey-1.7.6
Received: from mail-qy0-f196.google.com (mail-qy0-f196.google.com
	[209.85.221.196])
	by mail.postgresql.org (Postfix) with ESMTP id C96716326D7
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 09:43:12 -0300 (ADT)
Received: by qyk34 with SMTP id 34so11806213qyk.10
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 05:43:10 -0700 (PDT)
Received: by 10.224.65.223 with SMTP id k31mr5634758qai.359.1274963705192;
	Thu, 27 May 2010 05:35:05 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.9.207 with HTTP; Thu, 27 May 2010 05:34:35 -0700 (PDT)
In-Reply-To: <4BFE64D4.4020107@synechron.com>
References: <4BFE64D4.4020107@synechron.com>
From: Nilesh Govindarajan <lists@itech7.com>
Date: Thu, 27 May 2010 18:04:35 +0530
Message-ID: <AANLkTik0wf-id2BZslZxbOuDRZNkD6zMVlMVuS7Qdb0I@mail.gmail.com>
Subject: Re: How to add constraints without validating data.
To: Amit jain <amitjain@synechron.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=UTF-8
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201005/262
X-Sequence-Number: 34735

On Thu, May 27, 2010 at 5:55 PM, Amit jain <amitjain@synechron.com> wrote:
> Hi Gurus
>
> Is there any way in postgreSQL to apply constraints forcefully without
> checking/validating Data ? It should validate only those records which comes
> after constraints are applied.
>
> I need this help as data is already loaded in database but "ALTER TABLE" sql
> for adding CONSTRAINTS is taking enormous time.
>
> Thanks for ideas/suggestion in Advance !!
>
>
> --
>
> Regards,
>
> Amit Jain
> Synechron Technologies Pvt. Ltd. 4th  Floor, B Wing, Block 1.2, Embassy
> Techzone,
> Rajiv Gandhi Infotech Park, Hinjewadi Phase II, Pune 411 057.
> Phone : +91.20.40262000 Ext 2163 | Mobile:+91.9561298565 | VoIP :
> +1.732.415.0894
> http://www.synechron.com  MailTo: amitjain@synechron.com
>
>


I don't think there's any method to do that. If you add a constraint
using ALTER TABLE it will check existing data also. One option is you
create a new table with the constraints and same column names then:

insert into newtable values ( select * from oldtable)

but again this will check for old data, but may be faster.

-- 
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com



From imartinez@vectorsf.com Thu May  27 16:01:13 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 06015632979
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 13:01:13 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 41730-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 16:01:02 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hermes.vectorsf.com (firewall.vectorsf.com [62.82.80.62])
	by mail.postgresql.org (Postfix) with ESMTP id 6F97F632904
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 13:01:02 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by hermes.vectorsf.com (Postfix) with ESMTP id 96B99248000B;
	Thu, 27 May 2010 18:00:57 +0200 (CEST)
X-Virus-Scanned: amavisd-new at hermes.vectorsf.com
Received: from hermes.vectorsf.com ([127.0.0.1])
	by localhost (hermes.vectorsf.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id IpHuidFsTL4c; Thu, 27 May 2010 18:00:52 +0200 (CEST)
Received: from [10.70.4.200] (unknown [10.70.4.200])
	(Authenticated sender: imartinez)
	by hermes.vectorsf.com (Postfix) with ESMTP id 1F72F2480006;
	Thu, 27 May 2010 18:00:52 +0200 (CEST)
Subject: Re: How to add constraints without validating data.
From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala <imartinez@vectorsf.com>
To: Amit jain <amitjain@synechron.com>
Cc: pgsql-admin@postgresql.org
In-Reply-To: <4BFE64D4.4020107@synechron.com>
References: <4BFE64D4.4020107@synechron.com>
Content-Type: multipart/alternative; boundary="=-a+zbPXXLsbUI+qb0aPco"
Date: Thu, 27 May 2010 18:00:54 +0200
Message-ID: <1274976054.19383.37.camel@deimos>
Mime-Version: 1.0
X-Mailer: Evolution 2.28.3 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.791 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/265
X-Sequence-Number: 34738


--=-a+zbPXXLsbUI+qb0aPco
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

You have to disable triggers.

Two options:

- Export table data, truncate table (if your table is fk source you will
have a problem), add constraint and import exported data with
--disable-triggers flag in pg_restore.

- Perhaps you could add manually triger via pg_trigger table in
pg_catalog whith an insert into this table.

-----Original Message-----
From: Amit jain <amitjain@synechron.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: [ADMIN] How to add constraints without validating data.
Date: Thu, 27 May 2010 17:55:56 +0530

Hi Gurus

Is there any way in postgreSQL to apply constraints forcefully without
checking/validating Data ? It should validate only those records which
comes after constraints are applied. 

I need this help as data is already loaded in database but "ALTER TABLE"
sql for adding CONSTRAINTS is taking enormous time.

Thanks for ideas/suggestion in Advance !!


-- 

Regards,

Amit Jain 
Synechron Technologies Pvt. Ltd. 4th  Floor, B Wing, Block 1.2, Embassy Techzone,
Rajiv Gandhi Infotech Park, Hinjewadi Phase II, Pune 411 057. 
Phone : +91.20.40262000 Ext 2163 | Mobile:+91.9561298565 | VoIP : +1.732.415.0894
http://www.synechron.com  MailTo: amitjain@synechron.com






--=-a+zbPXXLsbUI+qb0aPco
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.28.3">
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#ffffff">
You have to disable triggers.<BR>
<BR>
Two options:<BR>
<BR>
- Export table data, truncate table (if your table is fk source you will have a problem), add constraint and import exported data with --disable-triggers flag in pg_restore.<BR>
<BR>
- Perhaps you could add manually triger via pg_trigger table in pg_catalog whith an insert into this table.<BR>
<BR>
-----Original Message-----<BR>
<B>From</B>: Amit jain &lt;<A HREF="mailto:Amit%20jain%20%3camitjain@synechron.com%3e">amitjain@synechron.com</A>&gt;<BR>
<B>To</B>: pgsql-admin@postgresql.org &lt;<A HREF="mailto:%22pgsql-admin@postgresql.org%22%20%3cpgsql-admin@postgresql.org%3e">pgsql-admin@postgresql.org</A>&gt;<BR>
<B>Subject</B>: [ADMIN] How to add constraints without validating data.<BR>
<B>Date</B>: Thu, 27 May 2010 17:55:56 +0530<BR>
<BR>
Hi Gurus<BR>
<BR>
Is there any way in postgreSQL to apply constraints forcefully without checking/validating Data ? It should validate only those records which comes after constraints are applied. <BR>
<BR>
I need this help as data is already loaded in database but &quot;ALTER TABLE&quot; sql for adding CONSTRAINTS is taking enormous time.<BR>
<BR>
Thanks for ideas/suggestion in Advance !!<BR>
<BR>
<BR>
-- 
<PRE>
<FONT SIZE="2">Regards,</FONT>

<B><FONT SIZE="2"><FONT COLOR="#800080">Amit Jain </FONT></FONT></B>
<B><FONT SIZE="2"><FONT COLOR="#666666">Synechron Technologies Pvt. Ltd.</FONT></FONT></B><FONT COLOR="#666666"><FONT SIZE="2"> 4th  Floor, B Wing, Block 1.2, Embassy Techzone,</FONT></FONT>
<FONT SIZE="2"><FONT COLOR="#666666">Rajiv Gandhi Infotech Park, Hinjewadi Phase II, Pune 411 057. </FONT></FONT>
<FONT SIZE="2"><FONT COLOR="#666666">Phone : +91.20.40262000 Ext 2163 | Mobile:+91.9561298565</FONT></FONT><FONT COLOR="#666666"> | </FONT><FONT COLOR="#666666"><FONT SIZE="2">VoIP : +1.732.415.0894</FONT></FONT>
<FONT SIZE="2"><FONT COLOR="#666666"><A HREF="http://www.synechron.com/">http://www.synechron.com</A>  MailTo: amitjain<A HREF="mailto:nilnandanj@synechron.com">@synechron.com</A></FONT></FONT>
</PRE>
<BR>
<BR>
<BR>
<BR>
</BODY>
</HTML>

--=-a+zbPXXLsbUI+qb0aPco--




From lists@itech7.com Thu May  27 16:16:04 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 423A8632260
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 13:16:04 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 52245-09 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 16:15:57 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qy0-f196.google.com (mail-qy0-f196.google.com
	[209.85.221.196])
	by mail.postgresql.org (Postfix) with ESMTP id 4A0416325E4
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 13:15:57 -0300 (ADT)
Received: by qyk34 with SMTP id 34so240388qyk.10
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 09:15:56 -0700 (PDT)
Received: by 10.224.81.84 with SMTP id w20mr5990423qak.259.1274976956242;
 Thu,
	27 May 2010 09:15:56 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.9.207 with HTTP; Thu, 27 May 2010 09:15:25 -0700 (PDT)
In-Reply-To: <1274976054.19383.37.camel@deimos>
References: <4BFE64D4.4020107@synechron.com>
 <1274976054.19383.37.camel@deimos>
From: Nilesh Govindarajan <lists@itech7.com>
Date: Thu, 27 May 2010 21:45:25 +0530
Message-ID: <AANLkTilVBVMQF7wEmRgQRqEUKf1YQYQ4Jc3tdIV_dqbV@mail.gmail.com>
Subject: Re: How to add constraints without validating data.
To: =?UTF-8?Q?I=C3=B1igo_Martinez_Lasala?= <imartinez@vectorsf.com>
Cc: Amit jain <amitjain@synechron.com>, pgsql-admin@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201005/266
X-Sequence-Number: 34739

On Thu, May 27, 2010 at 9:30 PM, I=C3=B1igo Martinez Lasala
<imartinez@vectorsf.com> wrote:
> You have to disable triggers.
>
> Two options:
>
> - Export table data, truncate table (if your table is fk source you will
> have a problem), add constraint and import exported data with
> --disable-triggers flag in pg_restore.
>
> - Perhaps you could add manually triger via pg_trigger table in pg_catalo=
g
> whith an insert into this table.
>
> -----Original Message-----
> From: Amit jain <amitjain@synechron.com>
> To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Subject: [ADMIN] How to add constraints without validating data.
> Date: Thu, 27 May 2010 17:55:56 +0530
>
> Hi Gurus
>
> Is there any way in postgreSQL to apply constraints forcefully without
> checking/validating Data ? It should validate only those records which co=
mes
> after constraints are applied.
>
> I need this help as data is already loaded in database but "ALTER TABLE" =
sql
> for adding CONSTRAINTS is taking enormous time.
>
> Thanks for ideas/suggestion in Advance !!
>
>
> --
>
> Regards,
>
> Amit Jain
> Synechron Technologies Pvt. Ltd. 4th  Floor, B Wing, Block 1.2, Embassy
> Techzone,
> Rajiv Gandhi Infotech Park, Hinjewadi Phase II, Pune 411 057.
> Phone : +91.20.40262000 Ext 2163 | Mobile:+91.9561298565 | VoIP :
> +1.732.415.0894
> http://www.synechron.com  MailTo: amitjain@synechron.com
>
>
>
>
>


How would disabling triggers help that ? He just wants to add
constraints without loss of data. I don't think he has any triggers.

--=20
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com



From imartinez@vectorsf.com Thu May  27 16:20:02 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 7599F632266
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 13:20:02 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 53687-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 16:19:55 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from hermes.vectorsf.com (firewall.vectorsf.com [62.82.80.62])
	by mail.postgresql.org (Postfix) with ESMTP id 1A05F6325EF
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 13:19:55 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by hermes.vectorsf.com (Postfix) with ESMTP id 27F032480011;
	Thu, 27 May 2010 18:19:51 +0200 (CEST)
X-Virus-Scanned: amavisd-new at hermes.vectorsf.com
Received: from hermes.vectorsf.com ([127.0.0.1])
	by localhost (hermes.vectorsf.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 5jVS+8L6ULWS; Thu, 27 May 2010 18:19:46 +0200 (CEST)
Received: from [10.70.4.200] (unknown [10.70.4.200])
	(Authenticated sender: imartinez)
	by hermes.vectorsf.com (Postfix) with ESMTP id C831711C027B;
	Thu, 27 May 2010 18:19:46 +0200 (CEST)
Subject: Re: How to add constraints without validating data.
From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala <imartinez@vectorsf.com>
To: Nilesh Govindarajan <lists@itech7.com>
Cc: Amit jain <amitjain@synechron.com>, pgsql-admin@postgresql.org
In-Reply-To: <AANLkTilVBVMQF7wEmRgQRqEUKf1YQYQ4Jc3tdIV_dqbV@mail.gmail.com>
References: <4BFE64D4.4020107@synechron.com>
 <1274976054.19383.37.camel@deimos>
	<AANLkTilVBVMQF7wEmRgQRqEUKf1YQYQ4Jc3tdIV_dqbV@mail.gmail.com>
Content-Type: multipart/alternative; boundary="=-F0X791O7OdEgbw/aWyW+"
Date: Thu, 27 May 2010 18:19:49 +0200
Message-ID: <1274977189.19383.51.camel@deimos>
Mime-Version: 1.0
X-Mailer: Evolution 2.28.3 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.791 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201005/267
X-Sequence-Number: 34740


--=-F0X791O7OdEgbw/aWyW+
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit

Constraints are special triggers, but triggers anyway.

So, If you truncate the table, create the contraint and then restore
with triggers disabled, it could be faster that create the contraint and
wait until it checks all table data... or not... :)



-----Original Message-----
From: Nilesh Govindarajan <lists@itech7.com>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: Amit jain <amitjain@synechron.com>, pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How to add constraints without validating data.
Date: Thu, 27 May 2010 21:45:25 +0530


How would disabling triggers help that ? He just wants to add
constraints without loss of data. I don't think he has any triggers.




--=-F0X791O7OdEgbw/aWyW+
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.28.3">
</HEAD>
<BODY>
Constraints are special triggers, but triggers anyway.<BR>
<BR>
So, If you truncate the table, create the contraint and then restore with triggers disabled, it could be faster that create the contraint and wait until it checks all table data... or not... :)<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
<B>From</B>: Nilesh Govindarajan &lt;<A HREF="mailto:Nilesh%20Govindarajan%20%3clists@itech7.com%3e">lists@itech7.com</A>&gt;<BR>
<B>To</B>: I&#241;igo Martinez Lasala &lt;<A HREF="mailto:%3d%3fISO-8859-1%3fQ%3fI%3dF1igo%3f%3d%20Martinez%20Lasala%20%3cimartinez@vectorsf.com%3e">imartinez@vectorsf.com</A>&gt;<BR>
<B>Cc</B>: Amit jain &lt;<A HREF="mailto:Amit%20jain%20%3camitjain@synechron.com%3e">amitjain@synechron.com</A>&gt;, <A HREF="mailto:pgsql-admin@postgresql.org">pgsql-admin@postgresql.org</A><BR>
<B>Subject</B>: Re: [ADMIN] How to add constraints without validating data.<BR>
<B>Date</B>: Thu, 27 May 2010 21:45:25 +0530
<PRE>

How would disabling triggers help that ? He just wants to add
constraints without loss of data. I don't think he has any triggers.

</PRE>
<BR>
</BODY>
</HTML>

--=-F0X791O7OdEgbw/aWyW+--




From lists@itech7.com Thu May  27 17:02:16 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 2ABB86324C3
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 14:02:16 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 13557-02 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 27 May 2010 17:02:08 +0000 (UTC)
X-Greylist: delayed 00:23:52.543657 by SQLgrey-1.7.6
Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com
	[209.85.212.46])
	by mail.postgresql.org (Postfix) with ESMTP id 29E1963221C
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 14:02:09 -0300 (ADT)
Received: by vws9 with SMTP id 9so165994vws.19
	for <pgsql-admin@postgresql.org>; Thu, 27 May 2010 10:02:08 -0700 (PDT)
Received: by 10.229.214.13 with SMTP id gy13mr2370032qcb.105.1274977984781;
	Thu, 27 May 2010 09:33:04 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.9.207 with HTTP; Thu, 27 May 2010 09:32:33 -0700 (PDT)
In-Reply-To: <1274977189.19383.51.camel@deimos>
References: <4BFE64D4.4020107@synechron.com>
	<1274976054.19383.37.camel@deimos>
	<AANLkTilVBVMQF7wEmRgQRqEUKf1YQYQ4Jc3tdIV_dqbV@mail.gmail.com>
	<1274977189.19383.51.camel@deimos>
From: Nilesh Govindarajan <lists@itech7.com>
Date: Thu, 27 May 2010 22:02:33 +0530
Message-ID: <AANLkTinPVDOCGDuqDKFZHRYArK4s6s5Iy6XQfPLKe2aS@mail.gmail.com>
Subject: Re: How to add constraints without validating data.
To: =?UTF-8?Q?I=C3=B1igo_Martinez_Lasala?= <imartinez@vectorsf.com>
Cc: Amit jain <amitjain@synechron.com>, pgsql-admin@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201005/270
X-Sequence-Number: 34743

On Thu, May 27, 2010 at 9:49 PM, I=C3=B1igo Martinez Lasala
<imartinez@vectorsf.com> wrote:
> Constraints are special triggers, but triggers anyway.
>
> So, If you truncate the table, create the contraint and then restore with
> triggers disabled, it could be faster that create the contraint and wait
> until it checks all table data... or not... :)
>
>
>
> -----Original Message-----
> From: Nilesh Govindarajan <lists@itech7.com>
> To: I=C3=B1igo Martinez Lasala <imartinez@vectorsf.com>
> Cc: Amit jain <amitjain@synechron.com>, pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] How to add constraints without validating data.
> Date: Thu, 27 May 2010 21:45:25 +0530
>
> How would disabling triggers help that ? He just wants to add
> constraints without loss of data. I don't think he has any triggers.
>
>
>


I agree constraints are triggers. But as far as I know, truncating
table doesn't disable the constraints.
Its same as deleteing all rows and then altering the table.
In that manner, even my solution would be appropriate because it
creates a duplicate table with the constraints.
Whatever be the method, I don't think its possible to avoid the
constraints. Correct me if I'm wrong.

--=20
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com



From robert.voinea@topex.ro Fri May  28 06:00:05 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 82A1A632B8E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 28 May 2010 03:00:05 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 63991-08 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 28 May 2010 05:59:57 +0000 (UTC)
X-Greylist: delayed 00:14:58.625008 by SQLgrey-1.7.6
Received: from mail2.topex.ro (mail2.topex.ro [82.137.49.134])
	by mail.postgresql.org (Postfix) with ESMTP id 16924632B59
	for <pgsql-admin@postgresql.org>; Fri, 28 May 2010 02:59:56 -0300 (ADT)
Received: from mail2.topex.ro ([172.27.168.99]) by topex.ro with MailEnable
	ESMTP; Fri, 28 May 2010 08:45:02 +0300
X-ASG-Debug-ID: 1275025494-039a64d50001-ysJW5H
Received: from vr.localnet (89-38-174-194.volio.ro [89.38.174.194]) by
	mail2.topex.ro with ESMTP id zggey9KdyFyKZNyx;
	Fri, 28 May 2010 08:44:54 +0300 (EEST)
X-Barracuda-Envelope-From: robert.voinea@topex.ro
X-Barracuda-AUTH-User: robert.voinea
X-Barracuda-AUTH-User: robert.voinea
X-Barracuda-AUTH-User: robert.voinea
X-Barracuda-AUTH-User: robert.voinea
X-ASG-Whitelist: Client
From: Robert Voinea <robert.voinea@topex.ro>
Organization: Topex
To: pgsql-admin@postgresql.org
X-ASG-Orig-Subj: Re: [ADMIN] How to add constraints without validating data.
Subject: Re: How to add constraints without validating data.
Date: Fri, 28 May 2010 08:44:52 +0300
User-Agent: KMail/1.13.2 (Linux/2.6.32-22-generic; KDE/4.4.2; i686; ; )
Cc: Nilesh Govindarajan <lists@itech7.com>,
	=?utf-8?q?I=C3=B1igo_Martinez_Lasala?= <imartinez@vectorsf.com>,
	Amit jain <amitjain@synechron.com>
References: <4BFE64D4.4020107@synechron.com>
 <1274977189.19383.51.camel@deimos>
	<AANLkTinPVDOCGDuqDKFZHRYArK4s6s5Iy6XQfPLKe2aS@mail.gmail.com>
In-Reply-To: <AANLkTinPVDOCGDuqDKFZHRYArK4s6s5Iy6XQfPLKe2aS@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201005280844.53208.robert.voinea@topex.ro>
X-Barracuda-Connect: 89-38-174-194.volio.ro[89.38.174.194]
X-Barracuda-Start-Time: 1275025494
X-Barracuda-URL: http://172.27.168.99:8000/cgi-mod/mark.cgi
X-Virus-Scanned: by bsmtpd at topex.ro
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.907 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	RCVD_IN_BL_SPAMCOP_NET=1.347, RCVD_IN_SORBS_WEB=0.77,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: **
X-Archive-Number: 201005/275
X-Sequence-Number: 34748

Why don't you use a partition?

Create another table that inherits from the table and add your new constrai=
nts=20
to the new table.


On Thursday 27 May 2010 19:32:33 Nilesh Govindarajan wrote:
> On Thu, May 27, 2010 at 9:49 PM, I=C3=B1igo Martinez Lasala
>=20
> <imartinez@vectorsf.com> wrote:
> > Constraints are special triggers, but triggers anyway.
> >=20
> > So, If you truncate the table, create the contraint and then restore wi=
th
> > triggers disabled, it could be faster that create the contraint and wait
> > until it checks all table data... or not... :)
> >=20
> > How would disabling triggers help that ? He just wants to add
> > constraints without loss of data. I don't think he has any triggers.
>=20
> I agree constraints are triggers. But as far as I know, truncating
> table doesn't disable the constraints.
> Its same as deleteing all rows and then altering the table.
> In that manner, even my solution would be appropriate because it
> creates a duplicate table with the constraints.
> Whatever be the method, I don't think its possible to avoid the
> constraints. Correct me if I'm wrong.

=2D-=20
Robert Voinea <robert (dot) voinea (at) topex (dot) ro>
Software Developer
Phone: +40 21 408 38 00 / ext. 343
=46ax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro