0

Context: I'm new on a big project, with a huge number of PostgreSQL tables (46) and foreign keys, and no documentation. We work with Java, Spring, Hibernate and TestNG.

Goal: I'm looking for a script that's able to empty the database (to call it before every unit-test case). Unfortunately, I can't spend too much time to find foreign keys in order to empty tables in the correct order.

Question: is there a way to empty (I don't want to drop it) a table without checking constraints on foreign keys ?

Jonathan Lermitage
  • 669
  • 1
  • 5
  • 17

1 Answers1

0

As @a_horse_with_no_name commented, using truncate ... cascade for each table will work. The link also provides a function that automatically loops through all the tables in the database.

If you have large tables, with a lot of foreign keys, it will probably take a lot of time to travel through all the foreign key indexes. In that case it would probably be better to dump the schema, drop the database, recreate the database and reload the schema in a batch. There is an example in the link, but it doesn't tell you how to run them. This is a little more refined:

#!/bin/sh

# Run this as the postgres user because you need create database permissions
# I'm assuming you're using a Unix variant, 
# the postgresql database is at localhost,
# and that psql trusts the Unix account.  
# Otherwise add -h hostname to the command lines 
# and set $PGPASSWORD variable before the first command.

pg_dump –s yourdb > /tmp/clean.sql
psql –c "drop database yourdb;"
psql –c "create database yourdb with owner you;"
psql yourdb < /tmp/clean.sql
rm –f /tmp/clean.sql
prinsarian
  • 31
  • 3
  • As an alternative to `drop database/create database` you could use `drop owned` everything is owned by the same user in that database. That way superuser access is not required –  Feb 28 '17 at 11:26