[postgre] 기존 테이블에서 인덱스/PK 생성-삭제문 출력하기

--  PK/INDEX 삭제문( 운영에서 추출하고 코드 실행 금지! 운영 인덱스가 제거됨! _new에서 추출! )
SELECT 	tablename,
	indexname,
	-- PK/INDEX 삭제문
	CASE indexname ~* 'PK' -- 정규식(~), 대소문자 무시(*), t/f 반환
		WHEN 'f' THEN
			'DROP INDEX '||indexname||';'
		ELSE 
			--ALTER TABLE   [테이블명]    DROP CONSTRAINT  [테이블명_PK];
			'ALTER TABLE '||tablename||' DROP CONSTRAINT '||indexname||';'
	END AS drop_text
FROM pg_indexes WHERE tablename in (  -- _new / _bak 만 실행 )
					'films',
					'films_new',
					'films_a'
);

 

-- 기존 테이블에서 PK/INDEX 생성문 추출
SELECT 	tablename,
	indexname,
	-- PK/INDEX 삭제문
	CASE indexname ~* 'PK' -- 정규식(~), 대소문자 무시(*), t/f 반환
		WHEN 'f' THEN
			'DROP INDEX '||indexname||';'
		ELSE 
			--ALTER TABLE   [테이블명]    DROP CONSTRAINT  [테이블명_PK];
			'ALTER TABLE '||tablename||' DROP CONSTRAINT '||indexname||';'
	END AS drop_text,
	
	-- PK/INDEX 생성문
	indexdef AS create_text
FROM(
	SELECT tablename,
		indexname,
		-- PK/INDEX 생성문
		CASE indexdef ~* 'PK' -- 정규식(~), 대소문자 무시(*), t/f 반환
		WHEN 'f' THEN
			regexp_replace(indexdef, '(idx_?[0-9]?[0-9]?)', 'idx99')
		ELSE 
			'ALTER TABLE '||tablename||'_new'||
			' ADD CONSTRAINT '||
			regexp_replace(indexname, '(pk|pkey)_?[0-9]?[0-9]?', 'pkey77')|| -- 수정할 부분 : 인덱스명 변경(숫자 추가)
			' PRIMARY KEY '||substring(indexdef, '\(.*\)')||
			';'
		END AS indexdef

	FROM(
		SELECT tablename,
			indexname,
			regexp_replace(indexdef, '(.*)(?=\sUSING)', '\1_new')||';' indexdef    -- 수정할 부분 : 테이블명 변경(_new)
		FROM pg_indexes WHERE tablename in (
							'films',
							'films_new',
							'films_a'
		) 
	)AS TABLE01
) AS TABLE02
ORDER BY tablename, create_text DESC;

 

-- 운영/배포 테이블 PK/INDEX 비교 
  ( 테이블명, _new or _bak)만 변경해주면 운영, 배포 테이블의 인덱스와 제약조건을 비교한다. )
select tablename, 
       string_agg(CASE WHEN indexdef ~ '(UNIQUE)' THEN 'UNIQUE' ELSE '' END||substring(indexdef from '\(.*\)') , ', ' ORDER BY indexdef) 
from pg_indexes 
where tablename like 'fil%' 
	and tablename not like '%new' -- 운영 데이터
group by tablename
except -- union all 
select replace(tablename, '_new',''), 
string_agg(CASE WHEN indexdef ~ '(UNIQUE)' THEN 'UNIQUE' ELSE '' END||substring(indexdef from '\(.*\)') , ', ' ORDER BY indexdef) 
from pg_indexes 
where tablename like 'films%new'  -- 배포 데이터
group by tablename;