为现有表手机号列添加约束：仅允许8位固话或10位手机号

Got it, let's sort out that phone number constraint issue. Your original regex ^[0-9]{8,10}$ matches any digit string between 8 and 10 characters long—so 9-digit numbers slip through, which is exactly what you want to avoid.

The Correct Constraint #

To only allow 8-digit fixed-line numbers or 10-digit mobile numbers (no spaces, no area codes), use a regex that explicitly matches those two exact lengths:

ALTER TABLE people 
ADD CONSTRAINT digit_phoneno_8_10 
CHECK (phoneno ~ '^[0-9]{8}$|^[0-9]{10}$');

Breakdown of the Regex #

• ^[0-9]{8}$: Matches exactly 8 digits from start (^) to end ($) of the string
• |: Acts as an "OR" operator between the two patterns
• ^[0-9]{10}$: Matches exactly 10 digits from start to end

This way, any number that's 9 digits long (or any length other than 8 or 10) will fail the check, which aligns with your requirements.

Optional: Extra Validation (If Needed) #

If you want to add stricter rules for mobile numbers (e.g., most mobile numbers start with 1 in many regions), you can tweak the regex to:

CHECK (phoneno ~ '^[0-9]{8}$|^1[0-9]{9}$');

This ensures 10-digit numbers start with 1 while still allowing 8-digit fixed lines.

内容的提问来源于stack exchange，提问作者Zog Upton