pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Dropping all constraints in database
3+ messages / 3 participants
[nested] [flat]

* Dropping all constraints in database
@ 2011-03-14 09:26  Lukasz Brodziak <lukasz.brodziak@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Lukasz Brodziak @ 2011-03-14 09:26 UTC (permalink / raw)
  To: pgsql-admin

Hello,

Is there a way of disabling/dropping all constrainsts in a given
database? I need to restore a db which has duplicate values in nearly
half of its tables then remove duplicates and then add the constraints
back. Is there a way to do that for each table in one
statement/function? It may be even a java/perl script if it can do
such a thing. Thank You all in advance for help.

-- 
Łukasz Brodziak
"What if everyting around You isn't quite as it seems,
What if all the world You think You know is an inelaborate dream
When You look at Your reflection is that all you want it to be
What if You could look right through the cracks
Would You find Yourself...... Find Yourself afraid to see"



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Dropping all constraints in database
@ 2011-03-14 09:43  Thomas Kellerer <spam_eater@gmx.net>
  parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Thomas Kellerer @ 2011-03-14 09:43 UTC (permalink / raw)
  To: pgsql-admin

Lukasz Brodziak, 14.03.2011 10:26:
> Hello,
>
> Is there a way of disabling/dropping all constrainsts in a given
> database? I need to restore a db which has duplicate values in nearly
> half of its tables then remove duplicates and then add the constraints
> back. Is there a way to do that for each table in one
> statement/function? It may be even a java/perl script if it can do
> such a thing. Thank You all in advance for help.
>

Something like this?

DO $body$
DECLARE r record;
BEGIN
    FOR r IN SELECT table_name,constraint_name
             FROM information_schema.constraint_table_usage
    LOOP
       EXECUTE 'ALTER TABLE ' || quote_ident(r.table_name)|| ' DROP CONSTRAINT '|| quote_ident(r.constraint_name) || ';';
    END LOOP;
END
$body$;


If you are not on 9.x yet, you can simply spool the output of a statement like this:

SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name||';'
FROM information_schema.constraint_table_usage

to a file, and then run that file to drop all constraints.

Regards
Thomas





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Dropping all constraints in database
@ 2011-03-14 10:51  Glyn Astill <glynastill@yahoo.co.uk>
  parent: Lukasz Brodziak <lukasz.brodziak@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Glyn Astill @ 2011-03-14 10:51 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org, Lukasz Brodziak <lukasz.brodziak@gmail.com>


> From: Lukasz Brodziak <lukasz.brodziak@gmail.com>
> Subject: [ADMIN] Dropping all constraints in database
> To: pgsql-admin@postgresql.org
> Date: Monday, 14 March, 2011, 9:26
> Hello,
> 
> Is there a way of disabling/dropping all constrainsts in a
> given
> database? I need to restore a db which has duplicate values
> in nearly
> half of its tables then remove duplicates and then add the
> constraints
> back. Is there a way to do that for each table in one
> statement/function? It may be even a java/perl script if it
> can do
> such a thing. Thank You all in advance for help.
> 
> 

Magnus Hagander wrote about a method to do that here:

http://blog.hagander.net/archives/131-Automatically-dropping-and-creating-constraints.html


      



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2011-03-14 10:51 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-14 09:26 Dropping all constraints in database Lukasz Brodziak <lukasz.brodziak@gmail.com>
2011-03-14 09:43 ` Thomas Kellerer <spam_eater@gmx.net>
2011-03-14 10:51 ` Glyn Astill <glynastill@yahoo.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox