PostgreSQL添加、删除、更新表字段、添加主键、自增序列操作

表修改操作

字段修改操作

新增字段（列）

ALTER TABLE "表名" ADD COLUMN"字段名" "数据类型";

ALTER  TABLE t_user ADD COLUMN user_attach text;

删除字段（列）

ALTER TABLE "表名" DROP COLUMN "字段名";

ALTER TABLE t_user DROP COLUMN user_attach;

更新字段（列）名

ALTER  TABLE"表名" RENAME "旧字段名" TO "新字段名";

ALTER TABLE t_user RENAME user_attach TO u_attach;

更新字段（列）类型

ALTER TABLE "表名" ALTER  "字段名"  TYPE "数据类型" USING "字段名"::"数据类型";
--或
ALTER TABLE "表名" ALTER  "字段名"  TYPE "数据类型";

-- 将字段修改为INTEGER类型
ALTER TABLE t_user ALTER COLUMN  u_id TYPE INTEGER USING u_id::INTEGER;
ALTER TABLE t_user ALTER COLUMN name type text;

更新字段默认值

--设置默认值
ALTER  TABLE"表名" ALTER COLUMN "字段名" SET DEFAULT "值";
ALTER TABLE t_user alter column year_ set default  extract(year from now());
--删除默认值
ALTER  TABLE"表名" ALTER COLUMN "字段名" DROP DEFAULT;

主/外键操作

添加主键

ALTER TABLE "表名" alter ADD PRIMARY KEY ("字段名");
-- 或
ALTER TABLE "表名" ADD PRIMARY KEY("字段名") ;
-- 将字段修改为INTEGER类型
ALTER TABLE t_user ADD PRIMARY KEY (u_id);

添加外键

# 修改表添加外键（可添加多个外键）
ALTER TABLE "A表名" ADD CONSTRAINT "外键关系名" FOREIGN KEY ("A表字段名")references "B表名"("B表字段名")

删除主/外键

# 修改表删除主、外键
ALTER TABLE "表名" DROP CONSTRAINT "主/外键关系名"
#主键关系名默认是 "表名"+"_pkey" 
#例如：
ALTER TABLE t_user DROP CONSTRAINT t_user _pkey

由于PostgreSQL级联删除，主键删除则外键数据记录同时会被删除

自增序列

添加自增标识

-- 创建自增序列，从1开始每次增加1   序列名称命名规范：表名_id_seq
CREATE SEQUENCE IF NOT EXISTS "序列名称"
    INCREMENT 1
    START 1
    MINVALUE 1;
COMMENT ON SEQUENCE "序列名称"
    IS '序列说明';

-- 例子：
CREATE SEQUENCE IF NOT EXISTS t_user_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1;
COMMENT ON SEQUENCE t_user_id_seq
    IS '用户表id序列';

-- 给表设置序列
ALTER TABLE "表名" ALTER  "字段名" SET DEFAULT nextval("序列名称");
-- 例子：
ALTER TABLE t_user ALTER  u_id SET DEFAULT nextval(t_user_id_seq);

重置自增序列

# 重置t_user_id_seq 从1开始
ALTER SEQUENCE t_user_id_seq restart with 1;

判断：添加or重置自增序列

DO
$do$
BEGIN
IF  EXISTS (SELECT 0 FROM pg_class where relname = 't_user_id_seq') THEN
   ALTER SEQUENCE t_user_id_seq restart with 1;
ELSE 
   	CREATE SEQUENCE IF NOT EXISTS t_user_id_seq
		INCREMENT 1
		START 1
		MINVALUE 1;
END IF;
END
$do$