学习笔记 - Postgres如何创建有自增主键的表

 

 

 

1、MySQL迁移到Postgres

之前用mysql习惯了,所以建表都是有自增主键的,建表规范里面大部分都是这样约定的,比如:(`id` BIGINT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id'),那么到了postgres里面,发现么有这样的类似定义,postgres是和oracle相近的体系,所以得换种方式来实现。oracle里面有序列,posgres里面也有,我们可以通过序列来实现自增主键的功能。

 

 

2、实现过程

2.1 创建普通表

create table test_c (

  id integer PRIMARY KEY comment 'primary key',
  nick_name character varying(128) comment 'nick name'
);  

 

2.2 创建序列

CREATE SEQUENCE "public"."csdn_id_20200928_seq"
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 99999999  -- no maxvalue,没有最大值
    CACHE 1;

 

2.3 将序列值赋予给主键字段    

alter table public.test_c alter column id set default nextval('csdn_id_20200928_seq');

【备注】,drop表test_c的时候,这个序列不会被drop掉,还可以继续使用。

 

3、序列Sequence的维护

3.1 修改自增序列

	-- 删除前先解除 id 对该序列的依赖
	ALTER TABLE tablename ALTER COLUMN id SET DEFAULT null;
	DROP SEQUENCE IF EXISTS sequence_name;

	-- 设置起始值,id_max 即 id 目前的最大值,可写为1,可通过 “SELECT MAX(id) FROM tablename” 得到
	CREATE SEQUENCE sequence_name START WITH id_max;
	ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('sequence_name'::regclass);

    -- 获得序列值
	csdn_gis=> select max(id) from public.csdn_20200928;
	]  max  
	-------
	 43739
	(1 row)

	csdn_gis=> ]

    -- 设置起始值
    alter sequence csdn_id_20200928_seq restart with 50000

    -- 验证
	SELECT nextval('sequence_name');

 

3.2 设置字段默认值

	-- 设置postgres自增表的日期字段默认值
	ALTER TABLE test_c ALTER COLUMN g_created  SET DEFAULT CURRENT_TIMESTAMP(3);
	ALTER TABLE test_c ALTER COLUMN g_modified   SET DEFAULT CURRENT_TIMESTAMP(3);

    -- 删除pgsql自增表的默认值
	ALTER TABLE test_c ALTER COLUMN nick_name DROP DEFAULT;

 

4、序列的函数

附赠序列的一些自带函数:

函数返回类型描述
nextval(regclass)bigint递增序列对象到它的下一个数值并且返回该值。这个动作是自动完成的。即使多个会话并发运行nextval,每个进程也会安全地收到一个唯一的序列值。
currval(regclass)bigint在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval,那么会报告一个错误。)请注意因为此函数返回一个会话范围的数值,而且也能给出一个可预计的结果,因此可以用于判断其它会话是否执行过nextval。
lastval()bigint在当前会话中返回最近一次nextval抓到的该序列的数值。(如果在本会话中从未在该序列上调用过 nextval,那么会报告一个错误。)请注意因为此函数返回一
返回当前会话里最近一次nextval返回的数值。这个函数等效于currval,只是它不用序列名为参数,它抓取当前会话里面最近一次nextval使用的序列。如果当前会话还没有调用过nextval,那么调用lastval将会报错。 
setval(regclass, bigint)bigint重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true,表示下一次nextval将在返回数值之前递增该序列。
setval(regclass, bigint, boolean)bigint重置序列对象的计数器数值。功能等同于上面的setval函数,只是is_called可以设置为true或false。如果将其设置为false,那么下一次nextval将返回该数值,随后的nextval才开始递增该序列。

 

 

5、PostgreSQL主要优势

整理下PostgresSQL的一些优势(不一定完全正确仅供参考)

  • PostgreSQL完全免费,而且是BSD协议,如果你把PostgreSQL改一改,然后再拿去卖钱,也没有人管你,这一点很重要,这表明了PostgreSQL数据库不会被其它公司控制。oracle数据库不用说了,是商业数据库,不开放。而MySQL数据库虽然是开源的,但现在随着SUN被oracle公司收购,现在基本上被oracle公司控制,其实在SUN被收购之前,MySQL中最重要的InnoDB引擎也是被oracle公司控制的,而在MySQL中很多重要的数据都是放在InnoDB引擎中的,反正我们公司都是这样的。所以如果MySQL的市场范围与oracle数据库的市场范围冲突时,oracle公司必定会牺牲MySQL,这是毫无疑问的。

  • 与PostgreSQl配合的开源软件很多,有很多分布式集群软件,如pgpool、pgcluster、slony、plploxy等等,很容易做读写分离、负载均衡、数据水平拆分等方案,而这在MySQL下则比较困难。

  • PostgreSQL源代码写的很清晰,易读性比MySQL强太多了,怀疑MySQL的源代码被混淆过。所以很多公司都是基本PostgreSQL做二次开发的。

  • PostgreSQL在很多方面都比MySQL强,如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL是多进程的,而MySQL是线程的,虽然并发不高时,MySQL处理速度快,但当并发高的时候,对于现在多核的单台机器上,MySQL的总体处理性能不如PostgreSQL,原因是MySQL的线程无法充分利用CPU的能力。


 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页