# 使用gs_probackup进行openGauss数据库物理备份与恢复
https://blog.csdn.net/GaussDB/article/details/128452082

# 查询数据库相应用户dsideal的所有序列
SELECT
	*
FROM
	pg_class
WHERE
	relowner = (SELECT usesysid FROM pg_user WHERE usename = 'dsideal')
	AND relkind = 'S'

# 查询所有表名字，默认schema名字是public
SELECT
	tablename
FROM
	pg_tables
WHERE
	schemaname = 'public'

# 查询已知表名tableName的所有字段的字段名、类型、是否为空、注释
SELECT
	A.attname AS NAME,
	format_type(A.atttypid, A.atttypmod) AS TYPE,
	A.attnotnull AS NOTNULL,
	col_description(A.attrelid, A.attnum) AS COMMENT
FROM
	pg_class AS C,
	pg_attribute AS A
WHERE
	C.relname = 'tableName'
	AND A.attnum > 0
	AND A.attrelid = C.oid

# 需要注意修改一下
mysql:ifnull <--> pgsql:coalesce

#************************************************************************************************************************************
# 查看有哪些序列
select * from pg_class where relkind='S';

# 创建一个序列
create sequence seq_t_base_student
increment  by 1         --步长
minvalue 1              --最小值
maxvalue 999999999      --最大值
start 1                 --起始值
cache 1                 --每次生成几个值
cycle;                  --到达最大值或最小值循环(不加默认不循环)


# 查看当前序列的值
select * from seq_t_base_student;

# 删除序列
drop sequence seq_t_base_student;

-- 查看所有序列名称
select relname from pg_class where relkind='S';

-- 查看序列当前值 (这玩意不要轻易查询，一查就大一个！)
 -- SELECT nextval('t_exam_person_person_id_seq');

-- 清库及序列
truncate table t_exam_person;
SELECT setval('t_exam_person_person_id_seq', 1,false);


# 使用序列
DROP TABLE IF EXISTS "public"."t_base_student";
CREATE TABLE "public"."t_base_student" (
  "student_id" int4 NOT NULL DEFAULT nextval('seq_t_base_student'::regclass),
  "student_name" varchar(255) COLLATE "pg_catalog"."default"
);

-- ----------------------------
-- Records of t_base_student
-- ----------------------------
INSERT INTO "public"."t_base_student" VALUES (1, '张三');
INSERT INTO "public"."t_base_student" VALUES (2, '李四');

-- ----------------------------
-- Primary Key structure for table t_base_student
-- ----------------------------
ALTER TABLE "public"."t_base_student" ADD CONSTRAINT "t_base_student_pkey" PRIMARY KEY ("student_id");


# 查询孤儿序列（没有字段绑定的，也没有被使用，在系统中占用资源，如果序列被绑定到一个serial类型的字段，删除该表时，序列会被自动删除，如果时int类型，则不会被删除，所以在大型数据库系统中应注意过多孤儿序列的产生）
SELECT ns.nspname AS schema_name, seq.relname AS seq_name
FROM pg_class AS seq
JOIN pg_namespace ns ON (seq.relnamespace=ns.oid)
WHERE seq.relkind = 'S'
  AND NOT EXISTS (SELECT * FROM pg_depend WHERE objid=seq.oid AND deptype='a')
ORDER BY seq.relname;

结论：PG中一旦使用了序列，就不能手动向这个字段写入数值，否则序列不变化，会导致主键重复！