How to truncate a Postgresql's table from JDBC

I have a Postgresql database and I want to truncate some tables using JDBC. How do I do that?

This is what I tried, but none worked... without even any error being reported:

Using CallableStatement.

try (Connection connection = getConnection(); CallableStatement statement = connection.prepareCall("TRUNCATE " + tableName)) { return statement.execute(); } 

Using Statement.

try (Connection connection = getConnection(); Statement statement = connection.createStatement()) { return statement.execute("TRUNCATE " + tableName); } 

Using PreparedStatement.

try (Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement("TRUNCATE " + tableName)) { return statement.execute(); } 

3 Answers

After the truncate, I need to commit:

try (Connection connection = getConnection(); Statement statement = connection.createStatement()) { int result = statement.executeUpdate("TRUNCATE " + tableName); connection.commit(); return result; } 

From the documentation:

TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

You may run into issues if the table has dependencies. If so, truncate the parent tables first, and also use the CASCADE option.

Connection connection = getConnection(); try { PreparedStatement statement = connection.prepareStatement("TRUNCATE " + parentTable1, parentTable2, ... + " CASCADE"); try { return statement.execute(); } finally { statement.close(); } } finally { connection.close(); } 

First, if you are truncating a table, you probably want to also RESTART IDENTITY (in addition to possibly doing CASCADE, as John Hogan mentioned).

Second, as far as doing a connection.commit(), the assumption is that you have autocommit set to OFF. My Postgres was set up with it set to ON (apparently, that is sometimes the default). If it is set to ON, then calling the commit is unnecessary, and will result in the error: "org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled."

Third, you may not have permission to truncate a table (or restart identity). In that case, you will need to:

DELETE from your_table SELECT setval('your_table_id', 1) 

The following worked for me:

public String truncateTable(String tableName, boolean cascadeFlag) { String message = ""; try { connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); String truncation = "TRUNCATE TABLE yourSchema." + tableName + " RESTART IDENTITY" + (cascadeFlag ? " CASCADE" : ""); System.out.println("truncateTable: Executing query '" + truncation + "'."); int result = statement.executeUpdate(truncation); // connection.commit(); // If autocommit is enabled (which it is for our DB), then throws exception after truncating the table. statement.close(); connection.close(); } catch (SQLException sqlex) { message = "Could not truncate table " + tableName + ". " + sqlex.getMessage(); System.err.println(message); sqlex.printStackTrace(); } return message; } 

Also:

public int deleteResetTable(String tableName, String fieldName) { int affectedRows = 0; try { connection = DriverManager.getConnection(url, username, password); String sql = "DELETE FROM yourSchema." + tableName; PreparedStatement preparedStatement = connection.prepareStatement(sql); affectedRows = preparedStatement.executeUpdate(); System.out.println("Deleted " + affectedRows+ " rows from table " + tableName + "."); sql = "SELECT setval('yourSchema." + fieldName + "', 1)"; preparedStatement = connection.prepareStatement(sql); affectedRows = preparedStatement.executeUpdate(); System.out.println("Reset " + affectedRows+ " values from table " + tableName + "."); } catch (SQLException ex) { System.out.println("Failed to delete rows from " + tableName + " " + ex.getMessage()); } return affectedRows; }