drop all tables sharing the same prefix in postgres

To do this in one command you need dynamic SQL with EXECUTE in a DO statement (or function):

DO$do$DECLARE   _tbl text;BEGINFOR _tbl  IN    SELECT quote_ident(table_schema) || '.'        || quote_ident(table_name)      -- escape identifier and schema-qualify!    FROM   information_schema.tables    WHERE  table_name LIKE 'prefix' || '%'  -- your table name prefix    AND    table_schema NOT LIKE 'pg\_%'    -- exclude system schemasLOOP   RAISE NOTICE '%',-- EXECUTE  'DROP TABLE ' || _tbl;  -- see belowEND LOOP;END$do$;

This includes tables from all schemas the current user has access to. I excluded system schemas for safety.

If you do not escape identifiers properly the code fails for any non-standard identifier that requires double-quoting.
Plus, you run the risk of allowing SQL injection. All user input must be sanitized in dynamic code - that includes identifiers potentially provided by users.

Potentially hazardous! All those tables are dropped for good. I built in a safety. Inspect the generated statements before you actually execute: comment RAISE and uncomment the EXECUTE.

If any other objects (like views etc.) depend on a table you get an informative error message instead, which cancels the whole transaction. If you are confident that all dependents can die, too, append CASCADE:

  'DROP TABLE ' || _tbl || ' CASCADE;

Closely related:

• Update column in multiple tables
• Changing all zeros (if any) across all columns (in a table) to... say 1

Alternatively you could build on the catalog table pg_class, which also provides the oid of the table and is faster:

...FOR _tbl  IN    SELECT c.oid::regclass::text  -- escape identifier and schema-qualify!    FROM   pg_catalog.pg_class c    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace    WHERE  n.nspname NOT LIKE 'pg\_%'     -- exclude system schemas    AND    c.relname LIKE 'prefix' || '%' -- your table name prefix    AND    c.relkind = 'r'                -- only tables...

System catalog or information schema?

• How to check if a table exists in a given schema

How does c.oid::regclass defend against SQL injection?

• Table name as a PostgreSQL function parameter

Or do it all in a single DROP command. Should be a bit more efficient:

DO$do$BEGIN   RAISE NOTICE '%', (-- EXECUTE (   SELECT 'DROP TABLE ' || string_agg(format('%I.%I', schemaname, tablename), ', ')   --  || ' CASCADE' -- optional   FROM   pg_catalog.pg_tables t   WHERE  schemaname NOT LIKE 'pg\_%'     -- exclude system schemas   AND    tablename LIKE 'prefix' || '%'  -- your table name prefix   );END$do$;

Related:

• Is there a postgres command to list/drop all materialized views?

Using the conveniently fitting system catalog pg_tables in the last example. And format() for convenience. See:

• How to check if a table exists in a given schema
• Table name as a PostgreSQL function parameter

Suppose the prefix is 'sales_'

Step 1: Get all the table names with that prefix

SELECT table_nameFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE 'sales_%';

Step 2: Click the "Download as CSV" button.

Step 3: Open the file in an editor and replace "sales_ with ,sales and " with a space

Step 4: DROP TABLE sales_regist, sales_name, sales_info, sales_somthing;

This is sql server command, can you try this one, is it worked in postgres or not. This query wil generate the sql script for delete

SELECT 'DROP TABLE "' || TABLE_NAME || '"' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '[prefix]%'

[EDIT]

begin    for arow in      SELECT 'DROP TABLE "' || TABLE_NAME || '"' as col1      FROM INFORMATION_SCHEMA.TABLES       WHERE TABLE_NAME LIKE '[prefix]%'    LOOP   --RAISE NOTICE '%',        EXECUTE 'DROP TABLE ' || arow ;END LOOP;  end;