postpresql 表结构修改、添加字段、外键、主键自增逻辑设置

本文链接：https://blog.youkuaiyun.com/nandao158/article/details/112982925

postpresql数据库最近很是流行，使用过程中有很多注意的问题，下面我们一一讲解：

一、表结构添加字段：

1、添加一个字段

ALTER TABLE "public"."document_info"
  ADD COLUMN "file_key" varchar(50);

2、添加多个字段

ALTER TABLE "public"."user_info"
  ADD COLUMN "row_id" varchar(4),
  ADD COLUMN "colunm_id" varchar(4);

3、删除字段：

ALTER TABLE "port_relation"  DROP COLUMN "work_id";

二、修改字段的数据类型

1、把字符串类型修改为int类型：

ALTER TABLE "user_info"alter COLUMN "area" set data TYPE int4 using 0 ;

2、把int类型修改为字符串类型：

ALTER TABLE "user_info" alter  COLUMN  "address"  type VARCHAR(50) ;

三、子表加外键

举例：给TableA 表加外键，B列为TableB的主键，B在TableA中作为外键。语法：

alter table TableA add constraint FK_B foreign key (B) references TableB(B);

1、sql方式添加：

alter table "port_relation_part" add constraint "groupId" foreign key ("group_id") REFERENCES "port_relation" ("group_id") ON DELETE CASCADE ON UPDATE NO ACTION;

2、客户端方式添加：

四、索引创建和删除

1、--查询索引


select * from pg_indexes where tablename='tab1';   

select * from pg_indexes where tablename='schedule_task';  

SELECT indexname FROM pg_indexes WHERE tablename = '表名';  
-- 示例：SELECT indexname FROM pg_indexes WHERE tablename = 'users'

2、--创建索引

tab1_bill_code_index 为索引名，
create index tab1_bill_code_index on "db1".tab1(bill_code);

#举例 B-Tree‌：默认适合大多数场景（范围查询、排序）‌
CREATE INDEX idx_table_column ON 表名 (列名);  
-- 示例：CREATE INDEX idx_users_email ON users (email) ‌ 

CREATE INDEX tb_user_id_seq ON tb_user (user_id);

#唯一索引‌（强制列值唯一）
CREATE UNIQUE INDEX tb_user_id_seq ON tb_user (user_id);

#‌Hash 索引‌（仅适用于等值查询，不支持范围查询）
CREATE INDEX idx_table_column ON 表名 USING hash (列名);  
-- 示例：CREATE INDEX idx_users_id ON users USING hash (id) ‌ 

#‌GiST 索引‌（适用于空间数据或全文搜索）
CREATE INDEX idx_table_column ON 表名 USING gist (列名);  
-- 示例：CREATE INDEX idx_geo_location ON locations USING gist (coordinates) 

#GIN 索引‌（适用于数组、JSONB 或全文检索）
CREATE INDEX idx_table_column ON 表名 USING gin (列名); 
-- 示例：CREATE INDEX idx_docs_content ON documents USING gin (content) 

#多列索引‌（联合查询优化）
CREATE INDEX idx_table_col1_col2 ON 表名 (列1, 列2);  
-- 示例：CREATE INDEX idx_orders_user_date ON orders (user_id, order_date)

CREATE UNIQUE INDEX "rowId" ON "public"."schedule_task" USING btree (
"row_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

3、--删除索引
drop index tab1_bill_code_index ;

drop index "rowId" ;

五、时间自动更新

当前状态

CREATE TABLE "public"."home_info" (
  "home_id" int4,
  "addvcd" varchar(255) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP
)
;

navicat状态

1、创建触发器

CREATE OR REPLACE FUNCTION update_modified_column()  
RETURNS TRIGGER AS $$  
BEGIN  
    NEW.update_time = NOW();  -- 强制更新时间字段  
    RETURN NEW;  
END;  
$$ LANGUAGE plpgsql;

2、关联表

CREATE TRIGGER trigger_update_time  
BEFORE UPDATE ON home_info  
FOR EACH ROW  
EXECUTE FUNCTION update_modified_column();

3、测试

SELECT * from home_info where addvcd='62044'

INSERT INTO home_info (home_id, addvcd) VALUES (1, '62044');  

UPDATE home_info SET remark = 'updated' WHERE home_id = 1;

查看修改时间是否更新为当前时间

六、主键自增逻辑

1、手动创建以支持主键自增逻辑

CREATE SEQUENCE user_info_user_id_seq START 1;

2、关联表结构

如果表结构创建好了

通过navicat 直接添加：

 nextval('user_info_user_id_seq'::regclass)

如图

或者通过脚本执行：

ALTER TABLE user_info 
  ALTER COLUMN user_id SET DEFAULT nextval('user_info_user_id_seq');

2)如果还没有创建表结构

CREATE TABLE "public"."user_info" (
    "user_id" int4 NOT NULL DEFAULT nextval('user_info_user_id_seq'::regclass),#重点
    "name" varchar(30) COLLATE "pg_catalog"."default",
    "age" varchar(30) COLLATE "pg_catalog"."default",
    "telephone" varchar(12) COLLATE "pg_catalog"."default",
    "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
    "deleted" bool DEFAULT false,
    CONSTRAINT "user_info_pkey" PRIMARY KEY ("user_id")#重点
);  

#修改表权限
ALTER TABLE "public"."user_info" OWNER TO "postgres";

今天postpresql功能分享到此，后续我们还会分享更多功能点，敬请期待！