postgresql change table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--if cloumn exist
SELECT  EXISTS (SELECT 1
FROM information_schema.columns
WHERE table_schema='ent' AND table_name='AdsPlatform' AND column_name='Name');
 
 
--add cloumn
ALTER TABLE finance."MappingInfo" ADD COLUMN IF NOT EXISTS  "Active" boolean;
UPDATE finance."MappingInfo" SET "Active"=false where "Active" is null  ;
ALTER TABLE finance."MappingInfo" ALTER COLUMN "Active" set NOT NULL;
--or
ALTER TABLE finance."MappingInfo" ALTER COLUMN "Active" Drop NOT NULL;
--remove column
ALTER TABLE finance."MappingInfo" DROP COLUMN IF EXISTS "WarnMsg";

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--add column & set column value from another
 
ALTER TABLE ent."AdsPlatform" ADD COLUMN IF NOT EXISTS "PlatfromSettlementParty" character varying(100);
 
DO
$do$
BEGIN
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema='ent' AND table_name='AdsPlatform' AND column_name='Name') THEN
   UPDATE ent."AdsPlatform" SET "PlatfromSettlementParty"="Name" WHERE "PlatfromSettlementParty" is null;
ELSE
    UPDATE ent."AdsPlatform" SET "PlatfromSettlementParty"= '' where "PlatfromSettlementParty" is null ;
END IF;
END
$do$
 
ALTER TABLE ent."AdsPlatform" ALTER COLUMN "PlatfromSettlementParty" set NOT NULL;

  

  

posted @   PanPan003  阅读(425)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 没有源码,如何修改代码逻辑?
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?
历史上的今天:
2016-06-21 中介者模式,调停者模式
2016-06-21 职责链模式,chain of responsibility
点击右上角即可分享
微信分享提示