RE: [GENERAL] Emptying a database. - Mailing list pgsql-general

From Jackson, DeJuan
Subject RE: [GENERAL] Emptying a database.
Date
Msg-id F10BB1FAF801D111829B0060971D839F45FE9B@cpsmail
Whole thread Raw
In response to [GENERAL] Emptying a database.  (Stuart Rison <stuart@ludwig.ucl.ac.uk>)
List pgsql-general
> >you just want to dump all the data?  just use delete then..
> >
> >DELETE FROM tablename;
> >
> >Deletes all the data in the table....
> >
>
> Yes (thanks ;) ), but I want to do so for ALL my tables within a db
> without
> having to type in the command for each table (I mean currently I have
> 20
> tables but I'm likely to have more).
>
> The way around it I've thought of is to write a delete for each table
> in a
> file and then just run them with \i.
>
> I just wondered if there was a pg command to do so (a 'flush' as it
> were).
>
There's no pg command to do it that I've ever seen, but you could easily
write a bash script to do it.  Just query pg_class for all non-system
tables then execute a loop of deletes for each table_name.  You could
even set up a way to tell your script not to delete the data in certain
tables.
(I would suggest a table to join to in the pg_class select that includes
the table which contains the tables not to delete as a table not to be
deleted. %^P)

Oh well, hope this helps.
        -DEJ

> >
> >
> >On Thu, 8 Oct 1998, Stuart Rison wrote:
> >
> >> Dear All,
> >>
> >> I'm currently doing some database design work and find myself
> entering
> >> loads of test records into my database tables.
> >>
> >> Occasionally I want to 'flush' out the database (i.e. keep all
> tables,
> >> views, etc. but delete all records).  Currently I have to pg_dump
> the
> >> database and then use a text editor to cut out all the 'copy'
> statements
> >> out and recreate a 'new' database.
> >>
> >> Is there a way of flushing the content of all tables?
> >>
> >> Thanks for any help you may provide.
> >>
> >> Cheers,
> >>
> >> Stuart.
> >>
>
> +-------------------------+--------------------------------------+
> | Stuart Rison            | Ludwig Institute for Cancer Research |
> +-------------------------+ 91 Riding House Street               |
> | Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
> | Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
> +-------------------------+--------------------------------------+
>
>

pgsql-general by date:

Previous
From: "Jackson, DeJuan"
Date:
Subject: RE: [HACKERS] Re: [COMMITTERS] 'pgsql/doc/src/sgml protocol.sgml'
Next
From: Constantin Teodorescu
Date:
Subject: Don't remember how to update a table :-(