PostgreSQL KingBase人大金仓 迁移数据库用到的SQL 修改字段名、字段类型

平时用可视化页面修改，服务器不允许安装数据库连接软件，用SQL形式更改。

1.添加字段

ALTER TABLE "work_order_bug"
  ADD COLUMN "admin_examine"  VARCHAR(255)

修改字段名

ALTER TABLE work_order_bug
RENAME COLUMN category TO deployment_category;

修改字段类型

ALTER TABLE work_order_bug ALTER COLUMN deployment_category TYPE TEXT;

修改字段和类型

ALTER TABLE work_order_bug
RENAME COLUMN deployment_category TO new_deployment_category;

ALTER TABLE work_order_bug
ALTER COLUMN new_deployment_category TYPE text;

将其他表数据迁入

INSERT INTO training_course_new (
	record_id,
-- 	'is_news',
-- 	'news_content',
-- 	'news_state',
-- 	'news_date',
-- 	'news_author',
) SELECT ID
-- is_news,
-- news_content,
-- news_state,
-- news_date,
-- news_author,
FROM
	tz_ly_event;

根据创建人添加创建人部门

UPDATE training_course_new tcn 
SET tcn.create_org_id = dept.dept_id,
		tcn.create_org_name = dept.dept_name 
FROM
	training_course_new course
	LEFT JOIN sys_users users ON users.id = course.create_user
	LEFT JOIN sys_dept dept ON dept.dept_id = users.org_id 
WHERE
	tcn.record_id = course.record_id;

替换某个字段枚举

UPDATE train_signup_man 
SET sign_in_status = 0
WHERE
	status =3;