How do I remove all tables and not the schema in Postgresql?

The accepted answer is great, but you can do this in just one step taking advantage from anonymous blocks (PosgreSQL 9.0+):

DO $$
DECLARE 
    r record;
BEGIN
    FOR r IN SELECT quote_ident(tablename) AS tablename, quote_ident(schemaname) AS schemaname FROM pg_tables WHERE schemaname = 'public'
    LOOP
        RAISE INFO 'Dropping table %.%', r.schemaname, r.tablename;
        EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
    END LOOP;
END$$;

Try this from psql:

-- Turn off headers:
\t
-- Use SQL to build SQL:
select 'drop table if exists "' || tablename || '" cascade;' 
  from pg_tables
 where schemaname = 'jason'; 
-- If the output looks good, write it to a file and run it:
\g out.tmp
\i out.tmp

from stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql

Tags:

Sql

Postgresql