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

最新推荐文章于 2024-08-26 17:11:22 发布

九月茅

最新推荐文章于 2024-08-26 17:11:22 发布

阅读量1.3w

点赞数 2

分类专栏： Postgres 学习笔记文章标签： 1024程序员节

本文链接：https://blog.csdn.net/mchdba/article/details/109257744

版权

学习笔记同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

Postgres

3 篇文章 0 订阅

订阅专栏

本文介绍了如何将使用MySQL习惯的自增主键概念迁移到PostgreSQL，并详细展示了在PostgreSQL中创建序列及设置自增主键的过程。此外，还分享了序列的维护方法，包括修改序列值、设置字段默认值以及一些序列相关的内置函数。最后，文章提到了PostgreSQL相对于MySQL的一些优势，如开源、丰富的配套软件和强大的功能。

摘要由CSDN通过智能技术生成

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的能力。