Drop All Tables Sharing The Same Prefix in Postgres

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;
BEGIN
FOR _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 schemas
LOOP
   RAISE NOTICE '%',
-- EXECUTE
  'DROP TABLE ' || _tbl;  -- see below
END 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

In postgres how can I delete all columns that share the same prefix

You could write it as PL/pgSQL function:

CREATE OR REPLACE FUNCTION drop_columns_with_prefix(tbl_name TEXT, column_prefix TEXT)  RETURNS VOID AS
$BODY$
DECLARE
    _column TEXT;
BEGIN
    FOR _column  IN
        SELECT quote_ident(column_name)
        FROM   information_schema.columns
        WHERE  table_name = tbl_name
        AND    column_name LIKE column_prefix || '%'
        AND    table_schema NOT LIKE 'pg_%'
    LOOP
      --  RAISE NOTICE '%',
       EXECUTE
      'ALTER TABLE ' || tbl_name || ' DROP COLUMN ' || _column;
    END LOOP;
END
$BODY$
LANGUAGE plpgsql VOLATILE;

Call it using:

SELECT drop_columns_with_prefix('tbl_name', 'prefix_');

Or if you don't want to use it as a function:

DO
$do$
DECLARE
    _column TEXT;
BEGIN
FOR _column  IN
    SELECT quote_ident(column_name)
    FROM   information_schema.columns
    WHERE  table_name = 'tbl_name'
    AND    column_name LIKE 'prefix_%'
    AND    table_schema NOT LIKE 'pg_%'
LOOP
  --  RAISE NOTICE '%',
   EXECUTE
  'ALTER TABLE tbl_name DROP COLUMN ' || _column;
END LOOP;
END
$do$

How to truncate all tables that start with the same prefix - Postgresql

You cannot execute TRUNCATE from a SELECT statement. You could do something like

SELECT concat('TRUNCATE TABLE ',table_catalog,'.',table_schema,'.',table_name)
FROM information_schema.tables 
WHERE table_name LIKE 'prefix%';  

which would return a TRUNCATE statement for each table with the prefix. You could even look into using a stored procedure to execute each single row.

How to drop multiple tables in PostgreSQL using a wildcard

Use a comma separated list:

DROP TABLE foo, bar, baz;

If you realy need a footgun, this one will do it's job:

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE ';
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

SELECT footgun('public', 'tablename');

Drop all tables whose names begin with a certain string

You may need to modify the query to include the owner if there's more than one in the database.

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

This is cleaner than using a two-step approach of generate script plus run. But one advantage of the script generation is that it gives you the chance to review the entirety of what's going to be run before it's actually run.

I know that if I were going to do this against a production database, I'd be as careful as possible.

Edit Code sample fixed.

Union tables with same prefix in postgresql

Below is one of the solution based on the answer by spatialhast.

CREATE OR REPLACE FUNCTION maskunion(IN _schema TEXT, IN _parttionbase TEXT, TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || $3;
    EXECUTE 'CREATE TABLE ' || $3 || '
        (
          col1 double precision,
          col2 double precision,
          col3 double precision,
          col4 double precision,
          col5 double precision
        )';

    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'INSERT INTO ' || $3 || ' SELECT * FROM ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

SELECT maskunion('schema1', 'tb_', 'schema1.new_table');

SQL: deleting tables with prefix

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you:

In the MySQL shell or through PHPMyAdmin, use the following query

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'myprefix_%';

This will generate a DROP statement which you can than copy and execute to drop the tables.

EDIT: A disclaimer here - the statement generated above will drop all tables in all databases with that prefix. If you want to limit it to a specific database, modify the query to look like this and replace database_name with your own database_name:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';

Postgres How to drop all tables from a schema with group in name?

Well this is probably not the coolest way to do this, but you can run the query below, copy and paste the output and run that.

SELECT 'DROP TABLE ' || schemaname || '.' || relname || ';' "statement"
FROM pg_catalog.pg_statio_user_tables
WHERE schemaname = 'abcd' AND relname ILIKE 'group%';

---

Related Topics