526互联

2、postgres批量修改所有者

发布时间 2023-05-23 15:08:26作者: 站着说话不腰疼

目录

postgres批量修改所有者

1、修改表

SELECT
	'Alter table bpluser.' || d.relname || ' owner to bpluser;' 
FROM
	(
	SELECT C
		.relname
	FROM
		pg_class C 
	WHERE
		C.relname IN ( SELECT tablename FROM pg_tables WHERE tablename NOT LIKE'pg%' AND tablename NOT LIKE'sql_%'
		and relname like 'bpl_%' ) 
	ORDER BY
	C.relname 
	) d;

2、修改序列

SELECT
	'Alter SEQUENCE bpluser.' || C.relname || ' owner to bpluser;' 
FROM
	pg_class C 
WHERE
	C.relname IN ( SELECT relname FROM pg_class WHERE relkind = 'S' 
	and C.relname like 'bpl_%' );

3、修改schema

alter schema bpluser owner to bpluser;
alter schema dmuser owner to dmuser;