删除postgres*享相同前缀的所有表

I would like to delete all tables sharing the same prefix ('supenh_agk') from the same database, using one sql command/query.

我想使用一个sql命令/查询从同一个数据库中删除所有共享相同前缀('supenh_agk')的表。

3 个解决方案

#1

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

要在一个命令中执行此操作,您需要在DO语句(或函数)中使用EXECUTE进行动态SQL:

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;
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.

如果您没有正确地转义标识符,则代码将因任何需要双引号的非标准标识符而失败。另外,您冒着允许SQL注入的风险。必须在动态代码中清理所有用户输入 - 包括用户可能提供的标识符。

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.

潜在的危险!所有这些表都被丢弃了。我建立了安全。在实际执行之前检查生成的语句:comment RAISE并取消注释EXECUTE。

Closely related:

Update column in multiple tables

更新多个表中的列

Changing all zeros (if any) across all columns (in a table) to... say 1

将所有列(在表格中)中的所有零(如果有)更改为...说1

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

或者你可以在目录表pg_class上构建,它也提供表的oid并且速度更快:

...
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?

c.oid :: regclass如何防御SQL注入?

Table name as a PostgreSQL function parameter

表名作为PostgreSQL函数参数

#2

Suppose the prefix is 'sales_'

假设前缀是'sales_'

Step 1: Get all the table names with that prefix

步骤1:获取具有该前缀的所有表名

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'sales_%';

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

第2步:单击“下载为CSV”按钮。

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

第3步:在编辑器中打开文件,并用空格替换“sales_ with,sales”

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

第4步:DROP TABLE sales_regist,sales_name,sales_info,sales_somthing;

#3

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

这是sql server命令,你可以尝试这个,它是否在postgres中工作。此查询将生成用于删除的sql脚本

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;

#1