Mistj's Digital Garden

Appearance

POSTGRESQL-可重复执行的SQL

建表 ​

sql
CREATE TABLE IF NOT EXISTS comm.dict_city ( city_id int8 , dict_city_name VARCHAR ( 50 ) NOT NULL DEFAULT '', input_code VARCHAR ( 50 ) NOT NULL DEFAULT '', full_code VARCHAR ( 300 ) NOT NULl DEFAULT '', order_no int8 NOT NULL DEFAULT 0, invalid_flag CHAR ( 1 ) NOT NULL DEFAULT 0, dict_province_id int8 NOT NULL DEFAULT 0, wb_code VARCHAR ( 50 ) NOT NULL DEFAULT '', his_org_id int8 NOT NULL DEFAULT 0, his_creater_id int8 NOT NULL DEFAULT 0, his_creater_name VARCHAR ( 64 ) NOT NULL DEFAULT '', his_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , his_updater_id int8 NOT NULL DEFAULT 0, his_update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, VERSION int2 NOT NULL DEFAULT 0, PRIMARY KEY ( city_id ) ); COMMENT ON TABLE comm.dict_city IS '城市字典'; COMMENT ON COLUMN comm.dict_city.city_id IS '城市的内部编号'; COMMENT ON COLUMN comm.dict_city.dict_city_name IS '城市的名称'; COMMENT ON COLUMN comm.dict_city.input_code IS '输入码'; COMMENT ON COLUMN comm.dict_city.full_code IS '全拼码'; COMMENT ON COLUMN comm.dict_city.order_no IS '排序编号'; COMMENT ON COLUMN comm.dict_city.invalid_flag IS '作废标志'; COMMENT ON COLUMN comm.dict_city.dict_province_id IS '身份字典id'; COMMENT ON COLUMN comm.dict_city.wb_code IS '五笔码'; COMMENT ON COLUMN comm.dict_city.his_org_id IS '机构ID'; COMMENT ON COLUMN comm.dict_city.his_creater_id IS '创建人身份ID'; COMMENT ON COLUMN comm.dict_city.his_creater_name IS '创建人名称'; COMMENT ON COLUMN comm.dict_city.his_create_time IS '创建时间'; COMMENT ON COLUMN comm.dict_city.his_updater_id IS '更新用户ID'; COMMENT ON COLUMN comm.dict_city.his_update_time IS '更新时间'; COMMENT ON COLUMN comm.dict_city.VERSION IS '乐观锁标志';
CREATE TABLE IF NOT EXISTS comm.dict_city ( city_id int8 , dict_city_name VARCHAR ( 50 ) NOT NULL DEFAULT '', input_code VARCHAR ( 50 ) NOT NULL DEFAULT '', full_code VARCHAR ( 300 ) NOT NULl DEFAULT '', order_no int8 NOT NULL DEFAULT 0, invalid_flag CHAR ( 1 ) NOT NULL DEFAULT 0, dict_province_id int8 NOT NULL DEFAULT 0, wb_code VARCHAR ( 50 ) NOT NULL DEFAULT '', his_org_id int8 NOT NULL DEFAULT 0, his_creater_id int8 NOT NULL DEFAULT 0, his_creater_name VARCHAR ( 64 ) NOT NULL DEFAULT '', his_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , his_updater_id int8 NOT NULL DEFAULT 0, his_update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, VERSION int2 NOT NULL DEFAULT 0, PRIMARY KEY ( city_id ) ); COMMENT ON TABLE comm.dict_city IS '城市字典'; COMMENT ON COLUMN comm.dict_city.city_id IS '城市的内部编号'; COMMENT ON COLUMN comm.dict_city.dict_city_name IS '城市的名称'; COMMENT ON COLUMN comm.dict_city.input_code IS '输入码'; COMMENT ON COLUMN comm.dict_city.full_code IS '全拼码'; COMMENT ON COLUMN comm.dict_city.order_no IS '排序编号'; COMMENT ON COLUMN comm.dict_city.invalid_flag IS '作废标志'; COMMENT ON COLUMN comm.dict_city.dict_province_id IS '身份字典id'; COMMENT ON COLUMN comm.dict_city.wb_code IS '五笔码'; COMMENT ON COLUMN comm.dict_city.his_org_id IS '机构ID'; COMMENT ON COLUMN comm.dict_city.his_creater_id IS '创建人身份ID'; COMMENT ON COLUMN comm.dict_city.his_creater_name IS '创建人名称'; COMMENT ON COLUMN comm.dict_city.his_create_time IS '创建时间'; COMMENT ON COLUMN comm.dict_city.his_updater_id IS '更新用户ID'; COMMENT ON COLUMN comm.dict_city.his_update_time IS '更新时间'; COMMENT ON COLUMN comm.dict_city.VERSION IS '乐观锁标志';

备注：

  • 除text、json类型字段，其它字段都要加not null 和默认值
  • varchar 或char类型的默认值'' 或'具体值'
  • int 类型默认值为 -1,0 或具体数值 （根据具体业务）
  • date 类型默认值为当前时间(CURRENT_TIMESTAMP)或'0001-01-01 00:00:00'

修改表名 ​

sql
alter table if exists comm.dict_city rename to comm.city;
alter table if exists comm.dict_city rename to comm.city;

删除表 ​

sql
drop table if exists comm.dict_city;
drop table if exists comm.dict_city;

更新字段 ​

增加字段

关键字 ADD COLUMN IF NOT EXISTS

sql
ALTER TABLE IF EXISTS "comm"."dict_city" ADD COLUMN IF NOT EXISTS "version" int2; COMMENT ON COLUMN "comm"."dict_city"."version" IS '乐观锁标志';
ALTER TABLE IF EXISTS "comm"."dict_city" ADD COLUMN IF NOT EXISTS "version" int2; COMMENT ON COLUMN "comm"."dict_city"."version" IS '乐观锁标志';

删除字段

sql
ALTER TABLE IF EXISTS "comm"."dict_city"  drop column if exists "version";
ALTER TABLE IF EXISTS "comm"."dict_city"  drop column if exists "version";

更改字段类型-字段修改类型

sql
ALTER TABLE IF EXISTS "comm"."account" ALTER COLUMN "version" TYPE int2
 USING "version" :: int2;  -- 从int8 更改为：int2
ALTER TABLE IF EXISTS "comm"."account" ALTER COLUMN "version" TYPE int2
 USING "version" :: int2;  -- 从int8 更改为：int2

更改字段类型-字段改名

sql
ALTER TABLE IF EXISTS comm.sex rename sex_name to dict_sex_name;
ALTER TABLE IF EXISTS comm.sex rename sex_name to dict_sex_name;

修改列注释

sql
COMMENT ON COLUMN "comm"."account"."version" IS '乐观锁标识';
COMMENT ON COLUMN "comm"."account"."version" IS '乐观锁标识';

数据 ​

插入数据 ​

数据冲突时，什么都不执行（on conflict do nothing）

jsx
INSERT INTO phpg_nbphs.menu_compare (compare_id, menu_id, affix, cache, hidden, invalid, creater, create_time,
                                     create_organization, modifier, update_time, update_organization, systemtime,
                                     data_source, change_source)
VALUES (126, 292, false, true, false, '1', 252, '2021-11-22 14:16:01.429000', 11, 252, '2021-11-22 14:16:01.429000', 11,
        '2021-11-22 14:16:01.429000', 990, 990)
on conflict do nothing;
INSERT INTO phpg_nbphs.menu_compare (compare_id, menu_id, affix, cache, hidden, invalid, creater, create_time,
                                     create_organization, modifier, update_time, update_organization, systemtime,
                                     data_source, change_source)
VALUES (126, 292, false, true, false, '1', 252, '2021-11-22 14:16:01.429000', 11, 252, '2021-11-22 14:16:01.429000', 11,
        '2021-11-22 14:16:01.429000', 990, 990)
on conflict do nothing;

如果有多条需要插入，只判断第一条是否存在即可

sql
DO $$
	BEGIN
	IF NOT EXISTS( select  1  from comm.sex  where sex_id=1)
	 THEN
	   INSERT  INTO comm.sex(sex_id,sex_name)values(1,'男');
	   INSERT  INTO comm.sex(sex_id,sex_name)values(2,'女');
	END IF;
END $$;
DO $$
	BEGIN
	IF NOT EXISTS( select  1  from comm.sex  where sex_id=1)
	 THEN
	   INSERT  INTO comm.sex(sex_id,sex_name)values(1,'男');
	   INSERT  INTO comm.sex(sex_id,sex_name)values(2,'女');
	END IF;
END $$;

删除数据 ​

sql
delete from "comm"."dict_city" where "dict_city_id" = 37010;
delete from "comm"."dict_city" where "dict_city_id" = 37010;

修改数据 ​

sql

UPDATE "comm"."config" SET "config_value" = 'new_value' WHERE "config_id" = 429308890568523778 and "config_value" = 'old_value';

UPDATE "comm"."config" SET "config_value" = 'new_value' WHERE "config_id" = 429308890568523778 and "config_value" = 'old_value';

索引 ​

添加索引 ​

sql
CREATE INDEX CONCURRENTLY  IF NOT EXISTS index_name ON table_name(column_name);
CREATE INDEX CONCURRENTLY  IF NOT EXISTS index_name ON table_name(column_name);

删除索引 ​

sql
DROP INDEX IF EXISTS index_name;
DROP INDEX IF EXISTS index_name;

索引重命名 ​

sql
alter index if exists old_name rename to new_index_name;
alter index if exists old_name rename to new_index_name;

判断约束 ​

sql
DO
$$
    BEGIN
        IF NOT EXISTS(
                SELECT conname
                FROM pg_constraint
                WHERE conname = 'ux_undo_log'
                  and conrelid = 'undo_log'::regclass)
        THEN
            ALTER TABLE "res"."undo_log"
                ADD CONSTRAINT "ux_undo_log" UNIQUE ("xid", "branch_id");
        END IF;
    END
$$;
DO
$$
    BEGIN
        IF NOT EXISTS(
                SELECT conname
                FROM pg_constraint
                WHERE conname = 'ux_undo_log'
                  and conrelid = 'undo_log'::regclass)
        THEN
            ALTER TABLE "res"."undo_log"
                ADD CONSTRAINT "ux_undo_log" UNIQUE ("xid", "branch_id");
        END IF;
    END
$$;

判断主键是否存在 ​

sql
DO 
$$ 
	BEGIN 
		IF NOT EXISTS(
			 select pg_constraint.conname AS pk_name 
			 from pg_constraint INNER 
			 JOIN pg_class ON pg_constraint.conrelid = pg_class.oid 
			 where pg_class.relname = 'dict_companies' AND pg_constraint.contype = 'p') 
		THEN 
		alter table comm.dict_companies add primary key (company_id); 
		END IF; 
	END 
$$;
DO 
$$ 
	BEGIN 
		IF NOT EXISTS(
			 select pg_constraint.conname AS pk_name 
			 from pg_constraint INNER 
			 JOIN pg_class ON pg_constraint.conrelid = pg_class.oid 
			 where pg_class.relname = 'dict_companies' AND pg_constraint.contype = 'p') 
		THEN 
		alter table comm.dict_companies add primary key (company_id); 
		END IF; 
	END 
$$;