Syntax error on Truncate SELECT table_name

Question

I'm using PostgreSQL and I try to truncate all tables starting with 'beach'.
In other words I would like to do something like:

Truncate (SELECT table_name
          FROM information_schema.tables
          WHERE table_schema = 'public' AND table_name LIKE 'beach_%')

Any ideas?

Answer 1

That's not valid syntax. You can't use TRUNCATE with a sub-query (see doc). Further, you probably need a procedure that iterates through these tables and truncates them.

Answer 2

Start by reading the manual on TRUNCATE for instructions on proper syntax.

Next, you are looking for dynamic SQL, since you cannot use wilcards or patterns for table names in TRUNCATE. You need to compose the SQL command dynamically. You can do this in two round-trips to the server like has been advised, or you can use a single DO statement:

DO
$do$
BEGIN

EXECUTE (
-- RAISE NOTICE '%', (   -- better check before you execute ..
   SELECT 'TRUNCATE ' || string_agg(c.oid::regclass::text, ', ')
   FROM   pg_class c
   JOIN   pg_namespace n ON n.oid = c.relnamespace
   WHERE  c.relname LIKE 'beach_%'
   AND    c.relkind = 'r'
   AND    n.nspname = 'public'
   );

END
$do$;

Creates and executes a command of the form:

TRUNCATE beach_foo, beach_bar, ...

You may want to / have to add CASCADE at the end of the command.

Related answer:
DROP All Views PostgreSQL

Consider the subtle differences between information schema and system catalogs:
How to check if a table exists in a given schema

You may or may not want to query information_schema.tables instead. Most importantly (per documentation):

Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

Bold emphasis mine.

Answer 3

You could run this query

SELECT 'truncate ' || table_name || ';'
FROM information_schema.tables
WHERE table_schema='public' AND table_name LIKE 'beach%'

This will return a row like

truncate someTableName;

for each table starting with beach.

Copy the result set, paste it, and execute it.

See this SQL Fiddle Example

Answer 4

Here is the way you would go about it by creating dynamic SQL:

1. Create the string to truncate
2. Execute the string using EXECUTE IMMEDIATE

Read here..

http://www.postgresql.org/docs/9.2/static/ecpg-dynamic.html

Answer 5

At the end I tryied this

     ResultSet resultSet=stmt.executeQuery("SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_name LIKE 'beach_%'");
     while (resultSet.next()) {
         list_tables.add(resultSet.getString("table_name"));
    }
     for (String table : list_tables) {
         stmt.executeUpdate("TRUNCATE "+table); 
    }

and it works fine