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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
