Truncating all the tables in a schema in PostgreSQL [duplicate]

Question

I am trying to truncate all the tables in a schema using PostgreSQL. It is showing this error:

ERROR:  relation "Building" does not exist
CONTEXT:  SQL statement "TRUNCATE TABLE "Building" CASCADE"
PL/pgSQL function truncate_schema(character varying) line 15 at EXECUTE statement

Here is the function I used:

CREATE OR REPLACE FUNCTION truncate_schema(schema IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = schema;
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.table_name) || ' CASCADE';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

How to do this properly?

Gannet

Answer 1

try like this

CREATE OR REPLACE FUNCTION truncate_schema(_schema character varying)
  RETURNS void AS
$BODY$
declare
    selectrow record;
begin
for selectrow in
select 'TRUNCATE TABLE ' || quote_ident(_schema) || '.' ||quote_ident(t.table_name) || ' CASCADE;' as qry 
from (
     SELECT table_name 
     FROM information_schema.tables
     WHERE table_type = 'BASE TABLE' AND table_schema = _schema
     )t
loop
execute selectrow.qry;
end loop;
end;
$BODY$
  LANGUAGE plpgsql

Backing

Don't you think there need a semicolon at end of last line.. – Krystakrystal

thank you, very helpful! – Tutelary

Answer 2

This is likely because you aren't including the name of the schema in your TRUNCATE statement, so it's looking for the table in the public schema.

Try changing the TRUNCATE statement to something like this:

EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.table_schema) || '.' ||
    quote_ident(stmt.table_name) || ' CASCADE';

Also, something to keep in mind about CASCADE is that it will TRUNCATE any table that has a foreign-key relationship to that table, which can include tables outside of that schema.

Edit in response to comment from OP:

You would also need to add table_schema to the query behind statements so that it is available within the EXECUTE statement.

Titrant

Thanks for the response.It is making an another error record "stmt" has no field "table_schema". – Gannet