From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-admin@postgresql.org
Subject: Re: Dropping all constraints in database
Date: Mon, 14 Mar 2011 10:43:09 +0100
Message-ID: <ilknvd$n3t$1@dough.gmane.org> (raw)
In-Reply-To: <AANLkTimD8w0v3OPySWRLFojzfNgyG1iv1+h4OB7rzm-J@mail.gmail.com>
References: <AANLkTimD8w0v3OPySWRLFojzfNgyG1iv1+h4OB7rzm-J@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Dropping all constraints in database
In-Reply-To: <ilknvd$n3t$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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