add_origin_source_field.sql 2.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263
  1. -- ===============================================
  2. -- 修改business_cards表,新增origin_source字段
  3. -- 执行日期: 2024年(请在执行前填写实际日期)
  4. -- 修改说明: 为名片表新增原始资料记录字段,采用JSON格式保存原始资料信息
  5. -- ===============================================
  6. -- 1. 新增origin_source字段 (原始资料记录字段)
  7. ALTER TABLE business_cards
  8. ADD COLUMN origin_source JSON;
  9. -- 2. 为新增字段添加注释
  10. COMMENT ON COLUMN business_cards.origin_source IS '原始资料记录字段 - 采用JSON格式保存原始资料信息,包括数据来源、MinIO路径等';
  11. -- 3. 验证字段是否成功添加
  12. SELECT
  13. column_name,
  14. data_type,
  15. is_nullable,
  16. column_default
  17. FROM information_schema.columns
  18. WHERE table_name = 'business_cards'
  19. AND column_name = 'origin_source'
  20. ORDER BY column_name;
  21. -- 4. 查看字段注释
  22. SELECT
  23. a.attname AS column_name,
  24. format_type(a.atttypid, a.atttypmod) AS data_type,
  25. COALESCE(pg_catalog.col_description(a.attrelid, a.attnum), '无注释') AS description
  26. FROM
  27. pg_catalog.pg_attribute a
  28. JOIN
  29. pg_catalog.pg_class c ON a.attrelid = c.oid
  30. JOIN
  31. pg_catalog.pg_namespace n ON c.relnamespace = n.oid
  32. WHERE
  33. c.relname = 'business_cards'
  34. AND a.attname = 'origin_source'
  35. AND a.attnum > 0
  36. AND NOT a.attisdropped
  37. ORDER BY a.attname;
  38. -- ===============================================
  39. -- 执行说明:
  40. -- 1. 请在生产环境执行前先在测试环境验证
  41. -- 2. 建议在业务低峰期执行此脚本
  42. -- 3. 执行前请备份相关数据
  43. -- 4. 新增字段允许NULL值,不会影响现有数据
  44. -- 5. origin_source字段用于存储原始资料的JSON数据
  45. -- ===============================================
  46. -- 可选:示例数据格式说明
  47. -- origin_source字段的JSON格式示例:
  48. -- {
  49. -- "type": "webpage_talent",
  50. -- "minio_path": "webpage_talent/webpage_talent_20240101_12345.md",
  51. -- "source_date": "2024-01-01 12:00:00",
  52. -- "talent_data": {...},
  53. -- "web_md_content": "部分网页内容..."
  54. -- }
  55. -- 执行完成后检查表结构
  56. \d business_cards;