Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

phpmyadmin fehler rechte unerlaubte mischung, WWWXXXApc, lilo trying to map files from unnamed device 0x0000", Wwwxxxapc, CAM::PDF::Renderer::TextF Fragen, adodb.recordset error '800a0cc1', php save mails as eml, WWWXXXAPC, sqlexpress database file auto-creation error, sqlexpress database file auto-creation error

Links

XODOX
Impressum
Datenschutz

#1: How to add constraints without validating data.

Posted on 2010-05-27 14:25:56 by AmitKumar Jain

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--

Report this message

#2: Re: How to add constraints without validating data.

Posted on 2010-05-27 14:34:35 by Nilesh Govindrajan

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message

#3: Re: How to add constraints without validating data.

Posted on 2010-05-27 18:00:54 by imartinez

--=-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--

Report this message

#4: Re: How to add constraints without validating data.

Posted on 2010-05-27 18:15:25 by Nilesh Govindrajan

On Thu, May 27, 2010 at 9:30 PM, Iñigo 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_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 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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message

#5: Re: How to add constraints without validating data.

Posted on 2010-05-27 18:19:49 by imartinez

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

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+--

Report this message

#6: Re: How to add constraints without validating data.

Posted on 2010-05-27 18:32:33 by Nilesh Govindrajan

On Thu, May 27, 2010 at 9:49 PM, Iñigo 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ñ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.
>
>
>


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

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message

#7: Re: How to add constraints without validating data.

Posted on 2010-05-28 07:44:52 by Robert Voinea

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ñigo 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.

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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Report this message