pgsql创建表，设置自增id

最新推荐文章于 2025-03-17 15:16:10 发布

小石潭记丶

最新推荐文章于 2025-03-17 15:16:10 发布

阅读量401

点赞数 1

文章标签： sql

本文链接：https://blog.csdn.net/qq_33371766/article/details/145027931

版权

CREATE TABLE "public"."day_config" (
  "id" int4 NOT NULL DEFAULT nextval('day_config_id_seq'::regclass),
  "year" varchar(255) COLLATE "pg_catalog"."default",
  "month" varchar(255) COLLATE "pg_catalog"."default",
  "week" varchar(255) COLLATE "pg_catalog"."default",
  "day" varchar(255) COLLATE "pg_catalog"."default",
  "quarter" varchar(255) COLLATE "pg_catalog"."default",
  "last_year" varchar(255) COLLATE "pg_catalog"."default",
  "last_month" varchar(255) COLLATE "pg_catalog"."default",
  "last_week" varchar(255) COLLATE "pg_catalog"."default",
  "last_quarter" varchar(255) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6),
  CONSTRAINT "day_config_pkey" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."day_config" 
  OWNER TO "postgres";

CREATE TRIGGER "set_day_config_id" BEFORE INSERT ON "public"."day_config"
FOR EACH ROW
EXECUTE PROCEDURE "public"."trigger_set_id"();

COMMENT ON COLUMN "public"."day_config"."year" IS '年';

COMMENT ON COLUMN "public"."day_config"."month" IS '月';

COMMENT ON COLUMN "public"."day_config"."week" IS '周';

COMMENT ON COLUMN "public"."day_config"."day" IS '日';

COMMENT ON COLUMN "public"."day_config"."quarter" IS '季度';

COMMENT ON COLUMN "public"."day_config"."last_year" IS '是否是年的最后一天';

COMMENT ON COLUMN "public"."day_config"."last_month" IS '是否是月的最后一天';

COMMENT ON COLUMN "public"."day_config"."last_week" IS '是否是周的最后一天';

COMMENT ON COLUMN "public"."day_config"."last_quarter" IS '是否是季度的最后一天';

COMMENT ON COLUMN "public"."day_config"."create_time" IS '创建时间';

创建序列

-----创建序列
CREATE SEQUENCE day_config_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;



------- 创建触发器函数
CREATE OR REPLACE FUNCTION trigger_set_id()
RETURNS TRIGGER AS $$
BEGIN
    NEW.id := NEXTVAL('day_config_id_seq');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;



-- 创建触发器，在新行插入之前调用函数
CREATE TRIGGER set_day_config_id
BEFORE INSERT ON "public"."day_config"
FOR EACH ROW
EXECUTE FUNCTION trigger_set_id();