Postgres 데이터베이스의 모든 테이블 잘라내기

Postgres 데이터베이스의 모든 테이블 잘라내기

정기적으로 Postgre에서 모든 데이터를 삭제해야 합니다.재구성 전 SQL 데이터베이스입니다.SQL에서 직접 이 작업을 수행하려면 어떻게 해야 합니까?

지금은 실행에 필요한 모든 명령을 반환하는 SQL 문을 만들었습니다.

SELECT 'TRUNCATE TABLE ' ||  tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';

하지만 일단 가지고 있으면 프로그래밍 방식으로 실행할 수 있는 방법이 보이지 않습니다.

좌절한FormsDesigner가 올바르면 PL/pgSQL에서 이 작업을 수행할 수 있습니다.대본은 다음과 같습니다.

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

이렇게 하면 저장된 기능이 생성되며(이 작업은 한 번만 수행해야 함) 나중에 다음과 같이 사용할 수 있습니다.

SELECT truncate_tables('MYUSER');

PL/pgSQL에서는 명시적 커서가 거의 필요하지 않습니다.루프의 더 간단하고 빠른 암시적 커서를 사용합니다.

테이블 이름은 데이터베이스별로 고유하지 않으므로 테이블 이름을 스키마로 한정해야 합니다.또한 기본 스키마 'public'으로 함수를 제한합니다.에 맞게.pg_*그리고.information_schema.

이러한 기능은 매우 주의해야 합니다.그들은 당신의 데이터베이스를 파괴합니다.어린이 안전 장치를 추가했습니다.댓글 달기RAISE NOTICE and 대와주석사주석EXECUTE폭탄을 터뜨리기 위해서라면...

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl text;
   _sch text;
BEGIN
   FOR _sch, _tbl IN 
      SELECT schemaname, tablename
      FROM   pg_tables
      WHERE  tableowner = _username
      AND    schemaname = 'public'
   LOOP
      -- dangerous, test before you execute!
      RAISE NOTICE '%',  -- once confident, comment this line ...
      -- EXECUTE         -- ... and uncomment this one
         format('TRUNCATE TABLE %I.%I CASCADE', _sch, _tbl);
   END LOOP;
END
$func$;

format() Postgres 9.1 이상이 필요합니다.이전 버전에서는 다음과 같이 쿼리 문자열을 연결합니다.

'TRUNCATE TABLE ' || quote_ident(_sch) || '.' || quote_ident(_tbl)  || ' CASCADE';

단일 명령, 루프 없음

한 번에 여러 테이블을 사용할 수 있으므로 커서나 루프가 전혀 필요하지 않습니다.

• 배열에서 테이블 이름 전달

모든 테이블 이름을 집계하고 단일 문을 실행합니다.더 단순하고 빠름:

CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- dangerous, test before you execute!
   RAISE NOTICE '%',  -- once confident, comment this line ...
   -- EXECUTE         -- ... and uncomment this one
  (SELECT 'TRUNCATE TABLE '
       || string_agg(format('%I.%I', schemaname, tablename), ', ')
       || ' CASCADE'
   FROM   pg_tables
   WHERE  tableowner = _username
   AND    schemaname = 'public'
   );
END
$func$;

통화:

SELECT truncate_tables('postgres');

정제된 쿼리

기능도 필요 없습니다.Postgres 9.0+에서는 문에서 동적 명령을 실행할 수 있습니다.또한 Postgres 9.5+에서는 구문이 훨씬 더 간단해질 수 있습니다.

DO
$do$
BEGIN
   -- dangerous, test before you execute!
   RAISE NOTICE '%',  -- once confident, comment this line ...
   -- EXECUTE         -- ... and uncomment this one
   (SELECT 'TRUNCATE TABLE ' || string_agg(oid::regclass::text, ', ') || ' CASCADE'
    FROM   pg_class
    WHERE  relkind = 'r'  -- only tables
    AND    relnamespace = 'public'::regnamespace
   );
END
$do$;

사의차대해에이의 .pg_class,pg_tables그리고.information_schema.tables:

• 지정된 스키마에 테이블이 있는지 확인하는 방법

에 대하여regclass로 묶은 이름

• Postgre로 테이블 이름 지정SQL 함수 매개 변수

반복사용시

"템플릿" 데이터베이스 작성(이름 지정)my_template당신의 바닐라 구조와 모든 빈 테이블과 함께.그런 다음 / 사이클을 수행합니다.

DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE my_template;

Postgres는 전체 구조를 파일 수준에서 복사하기 때문에 매우 빠릅니다.동시성 문제나 다른 오버헤드로 인해 속도가 느려지지 않습니다.

동시 연결로 인해 DB를 삭제할 수 없는 경우 다음 사항을 고려하십시오.

• 다른 것들이 연결되어 있는 동안 강제로 db를 삭제합니다.

만약 내가 이것을 해야 한다면, 나는 단순히 현재 db의 스키마 sql을 만든 다음, db를 드롭하고 만든 다음, db를 스키마 sql로 로드할 것입니다.

관련된 단계는 다음과 같습니다.

의 스키마 덤프 Create Schema (--schema-only)

pg_dump mydb -s > schema.sql

데이터베이스 삭제

drop database mydb;

데이터베이스 만들기

create database mydb;

스키마 가져오기

psql mydb < schema.sql

아래 쿼리를 실행합니다.

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.tablename) || '';
    END LOOP;
END $$;

이 경우 템플릿으로 사용하는 빈 데이터베이스를 사용하고 새로 고쳐야 할 경우 기존 데이터베이스를 삭제하고 템플릿에서 새 데이터베이스를 만드는 것이 좋습니다.

여러분, 더 좋고 깨끗한 방법은 다음과 같습니다.

데이터베이스(--schema-only) pg_dump mydb -s > 스키마의 스키마 덤프를 만듭니다.sql

데이터베이스 삭제 데이터베이스 mydb;

데이터베이스 만들기 데이터베이스 mydb;

스키마 psql mydb < 스키마를 가져옵니다.sql

나한텐 일이라구!

좋은 하루 되세요.히람 워커

소AUTO_INCREMENT버전:

CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';

        IF EXISTS (
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name=quote_ident(stmt.tablename) and column_name='id'
        ) THEN
           EXECUTE 'ALTER SEQUENCE ' || quote_ident(stmt.tablename) || '_id_seq RESTART WITH 1';
        END IF;

    END LOOP;
END;
$$ LANGUAGE plpgsql;

동적 SQL을 사용하여 각 문을 차례로 실행할 수 있습니까?이 작업을 수행하려면 PL/pgSQL 스크립트를 작성해야 합니다.

http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html (제38.5.4절).동적 명령 실행)

여기서 보이지 않는 한 가지는 시퀀스를 잘라낸 다음 다시 설정하는 것입니다.여기에 제공된 모든 것과 같은 단순 잘라내기는 테이블을 잘라내기만 할 뿐, 잘라내기 전 값으로 시퀀스를 남겨둡니다.잘라낼 때 시퀀스를 시작 값으로 재설정하려면 다음을 수행합니다.

TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

원하는 답변에 RESTART ID를 추가하면 됩니다. 여기서 이 ID를 반복할 필요가 없습니다. 캐스케이드는 사용자가 직면할 수 있는 모든 외부 키 제약 조건에 해당합니다.

bash에서도 이 작업을 수행할 수 있습니다.

#!/bin/bash
PGPASSWORD='' psql -h 127.0.0.1 -Upostgres sng --tuples-only --command "SELECT 'TRUNCATE TABLE ' || schemaname || '.' ||  tablename || ';' FROM pg_tables WHERE schemaname in ('cms_test', 'ids_test', 'logs_test', 'sps_test');" | 
tr "\\n" " " | 
xargs -I{} psql -h 127.0.0.1 -Upostgres sng --command "{}"

스키마 이름, 암호 및 사용자 이름을 스키마와 일치하도록 조정해야 합니다.

pgAdmin에서 데이터를 제거하고 테이블 구조를 보존하기 위해 다음 작업을 수행할 수 있습니다.

• 데이터베이스 -> 백업을 마우스 오른쪽 단추로 클릭하고 "Schema only"를 선택합니다.
• 데이터베이스 삭제
• 새 데이터베이스를 만들고 이전 데이터베이스와 같은 이름 지정
• 새 데이터베이스를 마우스 오른쪽 버튼으로 클릭 -> 복원 -> 백업을 선택하고 "Schema only"를 선택합니다.

psql을 사용할 수 있다면 사용할 수 있습니다.\gexec쿼리 출력을 실행하기 위한 meta 명령;

SELECT
    format('TRUNCATE TABLE %I.%I', ns.nspname, c.relname)
  FROM pg_namespace ns 
  JOIN pg_class c ON ns.oid = c.relnamespace
  JOIN pg_roles r ON r.oid = c.relowner
  WHERE
    ns.nspname = 'table schema' AND                               -- add table schema criteria 
    r.rolname = 'table owner' AND                                 -- add table owner criteria
    ns.nspname NOT IN ('pg_catalog', 'information_schema') AND    -- exclude system schemas
    c.relkind = 'r' AND                                           -- tables only
    has_table_privilege(c.oid, 'TRUNCATE')                        -- check current user has truncate privilege
  \gexec 

:\gexec9.에서 9.6으로 되었습니다.

간단히 다음 SQL을 실행할 수 있습니다.

DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname =current_schema()) LOOP
    EXECUTE 'TRUNCATE TABLE ' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
END $$;

이와 같은 방법을 사용하여 모든 잘라내기 쿼리를 가져올 수 있습니다.

SELECT 'TRUNCATE TABLE ' ||  table_name || ';' 
  FROM information_schema.tables
 WHERE table_schema='schema_name'
   AND table_type='BASE TABLE';

언급URL : https://stackoverflow.com/questions/2829158/truncating-all-tables-in-a-postgres-database