Postgre에서 여러 테이블을 삭제하는 방법와일드카드를 사용한 SQL

Postgre에서 여러 테이블을 삭제하는 방법와일드카드를 사용한 SQL

파티션 작업을 수행할 때 모든 파티션을 한 번에 삭제해야 하는 경우가 많습니다.

하지만

DROP TABLE tablename*

작동하지 않습니다. 와일드카드는 사용할 수 없습니다.

와일드카드를 사용하여 하나의 명령으로 여러 테이블을 삭제할 수 있는 우아한(읽기: 기억하기 쉬운) 방법이 있습니까?

쉼표로 구분된 목록 사용:

DROP TABLE foo, bar, baz;

만약 당신이 정말로 발총이 필요하다면, 이것은 그것의 일을 할 것입니다.

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');

여기 이 문제에 대한 또 다른 진부한 대답이 있습니다.에서 작동합니다.ubuntu그리고 아마도 다른 OS들도 있을 것입니다. 도.\dtpostgres 명령 프롬프트(명령 프롬프트가 내부에서 실행됨)genome-terminal나의 경우).그러면 터미널에 테이블이 많이 보일 것입니다.지금 사용ctrl+click-drag의 기능성genome-terminal모든 테이블의 이름을 복사합니다. python을 열고 문자열 처리를 좀 하면('로 ','로 바꾼 다음 '\n'을 ','로 바꿉니다) 모든 테이블의 쉼표 목록이 나타납니다.이제 psql 셸에서 작업을 수행합니다.drop table CTRL+SHIFT+V그리고 당신은 끝났습니다.너무 구체적이라는 걸 알아요 그냥 공유하고 싶었어요:)

이거 썼어요.

echo "select 'drop table '||tablename||';' from pg_tables where tablename like 'name%'" | \
    psql -U postgres -d dbname -t | \
    psql -U postgres -d dbname

적절한 값으로 대체dbname그리고.name%.

데이터베이스가 파괴되지 않도록 plpgsql을 올바르게 가져오는 것보다 실행하기 전에 검토하고 테스트할 수 있는 SQL 스크립트를 만드는 것이 훨씬 편했습니다.카탈로그에서 테이블 이름을 선택한 다음 나를 위해 드롭 문을 만드는 간단한 bash.8.4.x의 경우 다음과 같은 기본 쿼리를 얻을 수 있습니다.

SELECT 'drop table '||n.nspname ||'.'|| c.relname||';' as "Name" 
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
     AND n.nspname <> 'pg_catalog'
     AND n.nspname <> 'information_schema'
     AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid);

where 절을 추가할 수 있습니다.(where c.relname ilike 'bubba%')

출력은 다음과 같습니다.

         Name          
-----------------------
 drop table public.a1;
 drop table public.a2;

따라서 이 파일을 .sql 파일에 저장하고 psql -f 파일 이름으로 실행합니다.sql

공개: 이 답변은 Linux 사용자를 위한 것입니다.

@prongs가 말한 것에 몇 가지 구체적인 지침을 추가하겠습니다.

• \dt와일드카드를 지원할 수 있음: 실행 가능\dt myPrefix*예를 들어, 삭제할 테이블만 선택합니다.
• 끝나고CTRL-SHIFT-DRAG그때 선택하기 위해CTRL-SHIFT-C텍스트를 복사합니다.
• 에vim,에 가다INSERT MODE테이블을 붙여넣습니다.CTRL-SHIFT-V;
• 누르다ESC그 다음에 달려라:%s/[ ]*\n/, /g쉼표로 구분된 목록으로 변환하려면 마지막 쉼표를 제외하고 붙여넣을 수 있습니다.DROP TABLE % CASCADE.

Linux 명령줄 도구를 사용하면 다음과 같은 방법으로 수행할 수 있습니다.

psql -d mydb -P tuples_only=1 -c '\dt' | cut -d '|' -f 2 | paste -sd "," | sed 's/ //g' | xargs -I{} echo psql -d mydb -c "drop table {};"

참고: 마지막 에코는 드롭 명령 주위에 따옴표를 둘 방법을 찾을 수 없었기 때문에 출력을 복사하여 붙여넣고 직접 따옴표를 추가해야 합니다.

만약 누군가가 그 사소한 문제를 해결할 수 있다면, 그것은 훌륭한 소스가 될 것입니다.

그래서 저는 오늘 이 문제에 직면했습니다.저는 pgadmin3를 통해 서버 db를 로드하고 그렇게 했습니다.테이블이 알파벳 순으로 정렬되므로 이동 및 클릭 후 삭제가 잘 작동합니다.

저는 @Frank Heikens의 대답이 좋습니다.감사합니다.어쨌든 저는 조금 더 발전하고 싶습니다;

파티션된 테이블 이름이 다음과 같다고 가정합니다.partitioned_table그리고 우리는 매번 증가하는 숫자 접미사를 가지고 있습니다.맘에 들다partitioned_table_00,partitioned_table_01...partitioned_table_99

CREATE OR REPLACE drop_old_partitioned_tables(schema_name TEXT, partitioned_table_name TEXT, suffix TEXT)
    RETURNS TEXT
    LANGUAGE plpgsql
AS
$$
DECLARE
    drop_query text;
BEGIN
    SELECT 'DROP TABLE IF EXISTS ' || string_agg(format('%I.%I', table_schema, table_name), ', ')
    INTO drop_query
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
      AND table_schema = schema_name
      AND table_name <= CONCAT(partitioned_table_name, '_', suffix) -- It will also drop the table which equals the given suffix
      AND table_name ~ CONCAT(partitioned_table_name, '_\d{2}');
    IF drop_query IS NULL THEN
        RETURN 'There is no table to drop!';
    ELSE
        EXECUTE drop_query;
        RETURN CONCAT('Executed query: ', (drop_query));
    END IF;
END;
$$;

실행을 위해 아래 코드를 실행할 수 있습니다.

SELECT drop_old_partitioned_tables('public', 'partitioned_table', '10')

, 는 " ", "당매당분싶고다면하할을테이블다", "합니테는접다같음과되이야어미당사신이블의의신년이신만약▁like다합"와 같은 해가 .partitioned_table_2021할 수 가 더 큰 에도 월 할 수 .partitioned_table_2021_01필요에 따라 코드를 조정하는 것을 잊지 마십시오.

Jon이 답변해 준 또 다른 솔루션:

tables=`psql -d DBNAME -P tuples_only=1 -c '\dt' |awk -F" " '/table_pattern/ {print $3","}'`
psql -d DBNAME -c "DROP TABLE ${tables%?};";

전체 SQL 솔루션은 아니지만 의도를 달성하기 위해 사용할 수 있는 간단한 파이썬입니다.

import pandas as pd
from db import connections
from sqlalchemy.sql import text

engine = connections.pgsqlConnLifv100('your_db_name')

sql =   '''SELECT tablename FROM pg_catalog.pg_tables 
        WHERE schemaname='public'
        AND tablename LIKE 'temp_%%';'''
        
temp_tables = pd.read_sql(sql, engine)['tablename']

with engine.connect() as con:

    for table in temp_tables:
        sql = text(f"DROP table {table}")
        con.execute(sql)
        print(f"Dropped table {table}.")

파티에 늦었지만 작은 도우미 기능이 있는 가로 방향 조인을 사용하는 다른 접근법을 공유하고 싶었습니다.

CREATE OR REPLACE FUNCTION drop_table(tbl pg_tables)
  RETURNS void AS
$func$
  BEGIN
    execute 'drop table "'||tbl.tablename||'"';
  END
$func$ LANGUAGE plpgsql;

그리고 나서.

select t.tablename from pg_tables t, lateral drop_table(t) where t.tablename like 'your-pattern-here';

언급URL : https://stackoverflow.com/questions/4202135/how-to-drop-multiple-tables-in-postgresql-using-a-wildcard