How to truncate a Postgresql's table from JDBC

Question

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();
}

CallableStatement is used to invoke stored procedure inside DB. TRUNCATE is not a SP, hence, it should not work. What error did you get when you used plain Statement? You can also enable JDBC driver tracing to check what is happening inside. — Bimalesh Jha
– Bimalesh Jha, Commented Feb 25, 2014 at 15:13
No error was ever reported. For any of the tries. How can I enable that JDBC tracing? — Olivier Grégoire
– Olivier Grégoire, Commented Feb 25, 2014 at 15:14
Looks like your errors are getting swalloed somewhere in the client code. Put a catch(Exception e) {e.printStacktrace();} block before finally. You can enable tracing by following DriverManager.setLogWriter() API or refer to actual Driver guide. What JDBC Driver you are using — Bimalesh Jha
– Bimalesh Jha, Commented Feb 25, 2014 at 15:18
No, the exceptions are not swallowed. I changed the code in the 2nd try to be: boolean result = statement.execute("TRUNCATE " + tableName); System.out.println("Everything is ok"); return result; and I see "Everything is ok" on the console. Thanks for the info about the info on logwriter. I'm using the driver org.postgresql:postgresql:9.3-1101-jdbc4 (retrieved from Maven). Right now you're focusing on the wrong part. Don't tell me what I do wrong, please tell me how to do it properly, how you would do it yourself. — Olivier Grégoire
– Olivier Grégoire, Commented Feb 25, 2014 at 15:28

Olivier Grégoire · Accepted Answer · 2016-06-30 08:40:02Z

17

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.

edited Jun 30, 2016 at 8:40

answered Feb 25, 2014 at 15:41

Olivier Grégoire

35.8k23 gold badges101 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marteng Over a year ago

Shouldn't it be "TRUNCATE TABLE " + tableName?

Olivier Grégoire Over a year ago

@Marteng No, according to the doc

Mark Rotteveel · Accepted Answer · 2015-02-19 19:39:44Z

4

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();
}

edited Feb 19, 2015 at 19:39

Mark Rotteveel

110k240 gold badges160 silver badges232 bronze badges

answered Feb 19, 2015 at 19:33

John Hogan

1621 silver badge7 bronze badges

Comments

Tihamer · Accepted Answer · 2019-10-29 16:31:10Z

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;
}

Collectives™ on Stack Overflow

How to truncate a Postgresql's table from JDBC

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related