【数据库_Postgresql】数据库主键自增长之加序列和不加序列2种方法

将表的主键进行序列增加之后可以在数据库层面自动主键id增长

方法如下：先建序列，然后建表关联id主键，然后添加语句，不用考虑id主键

DROP SEQUENCE
IF EXISTS "public"."sp_warehouse_id_seq";

CREATE SEQUENCE "public"."sp_warehouse_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

DROP TABLE
IF EXISTS "public"."sp_warehouse";

CREATE TABLE "public"."sp_warehouse" (
"id" NUMERIC (10) DEFAULT nextval(
'sp_sn_main_id_seq' :: regclass
) NOT NULL,
"whid" VARCHAR (20) COLLATE "default",
"whname" VARCHAR (50) COLLATE "default",
"province" VARCHAR (20) COLLATE "default",
"city" VARCHAR (20) COLLATE "default",
"district" CHAR (20) COLLATE "default",
"addr" TEXT COLLATE "default"
) WITH (OIDS = FALSE);

INSERT INTO "public"."sp_warehouse" (

"whid",
"whname",
"province",
"city",
"district",
"addr"
)
VALUES
(
'whid1',
'ads',
'dsf',
'sdf',
'dsf ',
'sdf'
);

 

不用序列的话可以在sql中id部分加一句子查询，例如：

<insert id="insertSelective" parameterType="java.util.Map">

        INSERT INTO sp_sn_main
        <trim prefix="(" suffix=")" suffixOverrides=",">
        snmasterid,
            <if test="snnumber != null">
                snnumber,
            </if>
            <if test="whid != null">
                whid,
            </if>
            <if test="itemnumber != null">
                itemnumber,
            </if>
            <if test="inboundnumber != null">
                inboundnumber,
            </if>
            <if test="outboundnumber != null">
                outboundnumber,
            </if>
            <if test="inbounddate != null">
                inbounddate,
            </if>
            <if test="outbounddate != null">
                outbounddate,
            </if>
            <if test="employeeid != null">
                employeeid,
            </if>
            <if test="status != null">
                status,
            </if>

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
        
        (
            (
                SELECT
                    MAX (snmasterid)
                FROM
                    sp_sn_main
            ) + 1
        ),
            <if test="snnumber != null">
                #{snnumber,jdbcType=VARCHAR},
            </if>
            <if test="whid != null">
                #{whid,jdbcType=VARCHAR},
            </if>
            <if test="itemnumber != null">
                #{itemnumber,jdbcType=VARCHAR},
            </if>
            <if test="inboundnumber != null">
                #{inboundnumber,jdbcType=VARCHAR},
            </if>
            <if test="outboundnumber != null">
                #{outboundnumber,jdbcType=VARCHAR},
            </if>
            <if test="inbounddate != null">
                #{inbounddate,jdbcType=DATE},
            </if>
            <if test="outbounddate != null">
                #{outbounddate,jdbcType=DATE},
            </if>
            <if test="employeeid != null">
                #{employeeid,jdbcType=VARCHAR},
            </if>
            <if test="status != null">
                #{status,jdbcType=VARCHAR},
            </if>

        </trim>
    </insert>