DataOps-platform/check_business_cards_table.sql

-- ===============================================
-- 检查business_cards表结构和字段状态
-- 用途: 验证age和native_place字段的添加情况
-- ===============================================

-- 1. 检查表是否存在
SELECT 
    table_name,
    table_type,
    table_schema
FROM information_schema.tables 
WHERE table_name = 'business_cards';

-- 2. 查看完整的表结构
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default,
    ordinal_position
FROM information_schema.columns 
WHERE table_name = 'business_cards'
ORDER BY ordinal_position;

-- 3. 专门检查age和native_place字段
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default,
    CASE 
        WHEN column_name = 'age' THEN '年龄字段'
        WHEN column_name = 'native_place' THEN '籍贯字段'
        ELSE '其他字段'
    END as field_description
FROM information_schema.columns 
WHERE table_name = 'business_cards' 
    AND column_name IN ('age', 'native_place')
ORDER BY column_name;

-- 4. 查看字段注释
SELECT 
    a.attname AS column_name,
    format_type(a.atttypid, a.atttypmod) AS data_type,
    COALESCE(pg_catalog.col_description(a.attrelid, a.attnum), '无注释') AS comment
FROM 
    pg_catalog.pg_attribute a
JOIN 
    pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN 
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE 
    c.relname = 'business_cards' 
    AND a.attname IN ('age', 'native_place')
    AND a.attnum > 0 
    AND NOT a.attisdropped
ORDER BY a.attname;

-- 5. 检查是否有数据使用了新字段
SELECT 
    COUNT(*) as total_records,
    COUNT(age) as records_with_age,
    COUNT(native_place) as records_with_native_place,
    COUNT(CASE WHEN age IS NOT NULL THEN 1 END) as non_null_age,
    COUNT(CASE WHEN native_place IS NOT NULL AND native_place != '' THEN 1 END) as non_empty_native_place
FROM business_cards;

-- 6. 如果有数据，显示样本
SELECT 
    id,
    name_zh,
    age,
    native_place,
    created_at
FROM business_cards 
WHERE age IS NOT NULL OR (native_place IS NOT NULL AND native_place != '')
LIMIT 5;

-- 7. 检查age字段的数据范围（如果有数据）
SELECT 
    MIN(age) as min_age,
    MAX(age) as max_age,
    AVG(age) as avg_age,
    COUNT(DISTINCT age) as distinct_age_values
FROM business_cards 
WHERE age IS NOT NULL;

-- 8. 检查native_place字段的数据统计（如果有数据）
SELECT 
    COUNT(DISTINCT native_place) as distinct_native_places,
    LENGTH(MAX(native_place)) as max_length,
    LENGTH(MIN(native_place)) as min_length
FROM business_cards 
WHERE native_place IS NOT NULL AND native_place != '';

-- 9. 使用psql命令查看表结构（需要在psql中执行）
-- \d business_cards

-- 10. 检查表的所有约束
SELECT 
    tc.constraint_name,
    tc.constraint_type,
    tc.table_name,
    kcu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu 
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_name = 'business_cards'
ORDER BY tc.constraint_type, tc.constraint_name;

-- ===============================================
-- 说明：
-- 1. 此脚本用于验证字段是否正确添加
-- 2. 可以多次执行，不会修改数据
-- 3. 帮助确认数据库结构变更是否成功
-- ===============================================