postgresql change table

--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";

 

--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;

　　

　　

转载于:https://www.cnblogs.com/panpanwelcome/p/9207917.html