|
|
|
|
|
# 查询数据库相应用户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;
|
|
|
|
|
|
|
|
|
|
|
|
# 使用序列
|
|
|
|
|
|
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中一旦使用了序列，就不能手动向这个字段写入数值，否则序列不变化，会导致主键重复！
|
