9

I have a table in my db called 'mytable'. I'd like to clear it so that I can continue to collect and analyze 'fresh data' from it.

Something like 

conn = psycopg2.connect(database = mydb_name, host = mydb_server, user = mydb_uname, password = mydb_pwd)
cur = conn.cursor()
cur.execute("DROP TABLE mytable;")

Isn't going to work for me, because as far as I understand it, this destroys the table. I don't want to destroy/re-create... Just to flush all data.

How can I work this out?

jml
  • 1,745
  • 6
  • 29
  • 55

2 Answers2

10
 Truncate tablename

Is useful for this, table stays just dropping the data!

If you have foreign keys you need to use the following

 Truncate tablename CASCADE

For many tables do like this

 Truncate table1, table2, table3

Your example

 Cur.execute("truncate mytable;")
8bitcat
  • 2,206
  • 5
  • 30
  • 59
  • How can I avoid enumerating all of the table names and just truncate all data from all tables? `DELETE * FROM *;` ? Or maybe `TRUNCATE *` ? – jml Mar 29 '13 at 22:53
  • 1
    @jml check this out http://stackoverflow.com/questions/2829158/truncating-all-tables-in-a-postgres-database – 8bitcat Mar 29 '13 at 23:29
  • This code freezes up my machine, as there is a concurrent python executable which is propagating the db. Can you suggest an alternative? Do you think this could occur because the propagation code-base is relying on IDs? – jml Apr 01 '13 at 23:56
2

This sql query should delete all records from a table...

DELETE FROM mytable; // not DELETE * FROM mytable;
mtkcs
  • 1,696
  • 14
  • 27
  • 3
    As stated in the doc: TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables. But DELETE is safer if you are in a concurrent context. – Le Droid Dec 11 '13 at 22:03