【PG】常见数据库、表属性设置

本文主要是介绍【PG】常见数据库、表属性设置，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

数据库复制、备份相关

表的复制标识

单表操作

通过ALTER语句单独更改一张表的复制标识。

ALTER TABLE [tablename] REPLICA IDENTITY FULL;

批量表操作

通过代码块的方式，对某个schema中的所有表一起更新其复制标识。

SELECT tablename, CASE relreplidentWHEN 'd' THEN 'default'WHEN 'n' THEN 'nothing'WHEN 'f' THEN 'full'WHEN 'i' THEN 'index'END AS replica_identity
FROM pg_tables 
JOIN pg_class ON pg_tables.tablename = pg_class.relname
LEFT JOIN pg_index ON pg_class.oid = pg_index.indrelid AND indisprimary
WHERE schemaname = 'public'; -- 替换为您实际的模式名称，默认为public

DO $$
DECLARErow record;
BEGINFOR row IN SELECT tablename FROM pg_tables JOIN pg_class ON pg_tables.tablename = pg_class.relname LEFT JOIN pg_index ON pg_class.oid = pg_index.indrelid AND indisprimary WHERE schemaname = 'public' LOOPEXECUTE format('ALTER TABLE %I REPLICA IDENTITY FULL;', row.tablename);RAISE NOTICE 'Table % has been altered to have FULL replica identity.', row.tablename;END LOOP;
END $$;

链接

【1】REPLICA IDENTITY

这篇关于【PG】常见数据库、表属性设置的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---