PostgreSQL 创建数据库、创建用户、赋予权限、创建表、主键总结

-- 创建数据库

CREATE DATABASE jw_admin;

-- 使用超级用户登录数据库 -> 然后执行 ->创建用户及密码 ->设置备注

CREATE USER jw_admin WITH PASSWORD 'Navie-admin';

comment on role jw_admin is '测试用户是否创建成功';

-- 创建scheme被赋予权限 ->在数据库jw_admin session下创建schema

create schema jw_admin;

-- 赋予用户对应的schema权限和表权限

ALTER DEFAULT PRIVILEGES IN SCHEMA jw_admin GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO jw_admin ;

ALTER DEFAULT PRIVILEGES IN SCHEMA jw_admin GRANT EXECUTE ON FUNCTIONS TO jw_admin ;

ALTER DEFAULT PRIVILEGES IN SCHEMA jw_admin GRANT ALL ON SEQUENCES TO jw_admin ;

ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO jw_admin ;

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA jw_admin TO jw_admin ;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA jw_admin TO jw_admin ;

GRANT ALL ON ALL SEQUENCES IN SCHEMA jw_admin TO jw_admin ;

GRANT USAGE,CREATE ON SCHEMA jw_admin TO jw_admin ;

GRANT TEMPORARY ON DATABASE jw_admin TO jw_admin ;

-- 赋予序列权限

ALTER DEFAULT PRIVILEGES IN SCHEMA jw_admin GRANT ALL PRIVILEGES ON SEQUENCES TO jw_admin;

-- 修改角色为超级用户角色

alter role jw_admin with superuser;

-- 创建表

-- Table structure for jw_crm_article

-- ----------------------------

DROP TABLE IF EXISTS "jw_admin"."jw_crm_article";

CREATE TABLE "jw_admin"."jw_crm_article" (

"id" int8 NOT NULL DEFAULT nextval('"jw_admin".article_id_seq'::regclass),

"title" varchar(250) COLLATE "pg_catalog"."default",

"content" text COLLATE "pg_catalog"."default",

"public_time" timestamp(6),

"create_time" timestamp(6),

"update_time" timestamp(6),

"delete_status" varchar(1) COLLATE "pg_catalog"."default" DEFAULT 1

)

;

COMMENT ON COLUMN "jw_admin"."jw_crm_article"."title" IS '标题';

COMMENT ON COLUMN "jw_admin"."jw_crm_article"."content" IS '文章内容';

COMMENT ON COLUMN "jw_admin"."jw_crm_article"."public_time" IS '发布时间';

COMMENT ON COLUMN "jw_admin"."jw_crm_article"."create_time" IS '创建时间';

COMMENT ON COLUMN "jw_admin"."jw_crm_article"."update_time" IS '更新时间';

COMMENT ON COLUMN "jw_admin"."jw_crm_article"."delete_status" IS '是否有效 1.有效 2无效';

COMMENT ON TABLE "jw_admin"."jw_crm_article" IS '文章表';

-- 插入数据

-- ----------------------------

-- Records of jw_crm_article

-- ----------------------------

INSERT INTO "jw_admin"."jw_crm_article" VALUES (13, 'xx', '<p>xxxx</p>', '2022-09-01 20:29:03', '2022-09-01 20:29:09.297', '2022-09-01 20:29:09.297', '1');

INSERT INTO "jw_admin"."jw_crm_article" VALUES (14, '111', '<p>111😊</p>', '2022-09-02 09:55:53', '2022-09-02 09:55:56.329', '2022-09-02 09:55:56.329', '1');

-- 表创建自动增长主键

1、在PostgreSQL当中，我们实现ID自增首先创建一个关联序列，以下sql语句是创建一个序列：

CREATE SEQUENCE article_id_seq START 30;

序列名称是article_id_seq，起始数为1。

2、然后在字段默认值里设 nextval('article_id_seq'::regclass) 即可。

 

3、保存字段属性变更。

ALTER TABLE jw_admin.jw_crm_article ALTER COLUMN id SET DEFAULT nextval('article_id_seq'::regclass);

4、修改id的自增起始数。

把当前最大的id做为当前的id自增起始数

select setval('article_id_seq',(select max(id) from jw_crm_article));

5. 如何获取自增id的当前值或者下一个值

select * from "article_id_seq";