Hello, I'm searching for a simple way to delete all data from a database and keep the structure (table, relationship, etc...). I using postgreSQL but I think, if there a command to do that, it's not specific to postgres.
Thanks,
Damien
Keep in mind:" There is no TRUNCATE command in the SQL standard. "
Tim Drisdelle
2010-01-22 14:12:30
Note that TRUNCATE works on a single table, not on the entire database. If you have a lot of tables, this might be tedious.
Adam Matan
2010-01-22 14:17:23
-1 - answer has nothing to do with question.
depesz
2010-01-22 14:22:15
@AdamYou are right about that.I'm in JCT/Machon Lev, by the way.
Jacob Relkin
2010-01-22 14:22:45
@Jacob Relkin Fairly amazing to find out that the person you're discussing with on the internet is less than 2 miles away. SHABAT SHALOM!
Adam Matan
2010-01-22 14:26:09
@Adam Yes, it is! To you too!
Jacob Relkin
2010-01-22 14:28:19
In SQL Server, you can't use TRUNCATE TABLE on tables with foreign key constraints - not sure if that's the same in postgresql?
AdaTheDev
2010-01-22 14:30:26
I think you have to use TRUNCATE CASCADE (http://developer.postgresql.org/pgdocs/postgres/sql-truncate.html)
Adam Matan
2010-01-22 14:31:45
TRUNCATE works in my version of PostgreSQL (8.6.3 if I'm right). With the manual of the command linked by "R van Rijn" I've seen that it could be used on multiple tables. So I used :SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'public'To list my tablesAndTRUNCATE on this list of table=> TRUNCATE table1, table2, etc... And it worksThanks all :=)
Damien
2010-01-22 14:35:22
Adam's solution is way better, and works on the whole database. TRUCATE won't work to invalidate foreign key constraints, such as `REFERENCES foo ON NULL DROP`, (truncate can't trigger the drop portion), so the TRUNCATE will fail.
Evan Carroll
2010-01-23 16:29:32
A:
I'm not a Postgres guy, but one option would be to iterate through the tables and issue a Truncate command against each one. You'll have to take otable relationships into account, though - you will not be able to delete reference data before data that refers to it, for example.
ZombieSheep
2010-01-22 14:10:23
+7
A:
Dump the schema using pg_dump. drop the database, recreate it and load the schema.
Dump you database schema (the -s tag) to a file:
pg_dump -s -f db.dump DB-NAME
Delete the database:
dropdb DB-NAME
Recreate it:
createdb DB-NAME
Restore the schema only:
pg_restore db.dump > psql DB-NAME
This should work on PostgreSQL; Other DBMS might have their own tools for that. I do no know of any generic tool to do it.
EDIT:
Following comments, you might want to skip the dropdb command, and simply create another database with the dumped schema. If all went through well, you can drop the old database:
pg_dump -s -f db.dump DB-NAME
createdb DB-NEW-NAME
pg_restore db.dump > psql DB-NEW-NAME
At this point, you have the full database at DB-NAME, and an empty schema at DB-NAME-NEW. after you're sure everything is OK, use dropdb DB-NAME.
Adam Matan
2010-01-22 14:11:03
An interesting option. I'd be wary of it, however. I guess I'm just inherently nervous about dropping the whole database, but that's just me. :)
ZombieSheep
2010-01-22 14:12:11
No need to be wary. If the OP wants a way to truncate the entire database anyways, then this is a better way to do it. No logs for truncating, and much faster.
Tim Drisdelle
2010-01-22 14:15:22
A:
R van Rijn
2010-01-22 14:16:40
+1
A:
You can do something like this:
export PGUSER=your_pg_user
export PGHOST=database.host
export PGPORT=port
export PGDATABASE=your_database
psql -qAtX -c "select 'TRUNCATE table ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' from information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX
It will do what's necessary.
Of course these exports are not necessary, but they will make it simpler to run 2 copies of psql without having to givem them all standard -U, -d, and so on, switches.
One thing though - using TRUNCATE to do so, while faster than DELETE, has it's drowbacks - for example - it is not being replicated by Slony and any other replication system that works on triggers. Unless you are working on PostgreSQL 8.4, and your replication knows how to use triggers on TRUNCATE.
depesz
2010-01-22 14:28:06