后端的同学应该都体会过跑一个 SQL 脚本正要收工psql 突然甩出一行刺眼的红色ERROR: column is_active is of type boolean but default expression is of type integer。第一次看到这个报错的人往往会愣一下——我只是给布尔字段设了个默认值 0怎么就不让建表了今天这篇文章就围绕这条 error 把话说透搞清楚 PostgreSQL 为什么对 boolean 列的 default expression 卡得这么严以及被 integer 默认值挡住之后该怎么定位、怎么改、怎么避免下次再犯。这篇内容适合几类人看正在把 MySQL 或其它数据库迁到 PostgreSQL 的开发者、手写迁移 SQL 结果被数据库“教育”的 ORM 使用者以及负责维护老系统、动不动就要处理奇奇怪怪 DDL 的场景。我会把报错原理、复现过程、修复脚本和踩坑经验一次性讲明白文末还会附上我处理线上事故时的一些习惯。1. 这个报错到底在说什么1.1 逐段拆解报错信息先别急着改代码我们把这条 error 按空格拆开读一遍column 列名PostgreSQL 明确指出是哪一列出了问题这里的“列名”是占位符实际报错时会显示真实的字段名比如is_active、is_vip。is of type boolean这一列在表结构里定义的类型是布尔类型。这是报错的前提条件说明类型本身没问题。but default expression这个短语是理解整条错误的关键。它说的是DEFAULT子句对应的“表达式”而不是“默认值”这个最终结果。PostgreSQL 在解析 DDL 时会把DEFAULT后面的内容解析成一棵表达式树然后检查这棵树的返回类型。is of type integer这棵表达式树最终推导出来的类型是整数。合起来意思就是一个 boolean 类型的列却配了一个返回 integer 的默认表达式。PostgreSQL 认为这两种类型之间没有合法的隐式转换通道于是直接在 DDL 阶段把这条语句拒了根本不会等你插入数据的时候才报错。这里有一个隐藏知识点报错说的是“默认表达式”而不是“默认值”。比如DEFAULT 11也会报同样的错因为11这个表达式的结果类型是 integer。PostgreSQL 做的是类型推导不是简简单单看“表面值”。1.2 boolean 列的默认值到底怎么才能过很多初学者最困惑的一点是明明INSERT INTO t VALUES (0)在某些情况下能成功为什么DEFAULT 0就不行这里要区分两个概念字符串输入转换和表达式类型检查。PostgreSQL 的 boolean 类型在接收“外部输入”时非常宽容。它的输入函数boolin接受true、false、t、f、yes、no、y、n、1、0以及这些词的大小写变体。所以当你写INSERT INTO t (is_active) VALUES (0)时这是字符串0它会经过 boolean 的输入函数被转换成false这是允许的。但如果你写DEFAULT 0这里的0是没有任何引号的整数常量解析器会把它的类型标记为 integer。DEFAULT子句不会走 boolean 的“输入函数”那条路而是直接要求表达式类型与列类型匹配。integer 到 boolean 在 PostgreSQL 的pg_cast系统表里没有定义为隐式转换于是就被拒绝了。打个不严谨但好懂的比方boolean 类型的大门上贴了一张“只收特定格式包裹”的告示。你送一张写着“0”的纸条字符串0门卫认。但你送一个写着“0”的塑料盒子integer 类型的 0门卫不认因为这个盒子没有贴 boolean 标签。所以真正合法且推荐的写法是-- 推荐的写法 ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT true; ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT false; -- 也可以这样写但不推荐没必要绕弯子 ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT 0::boolean;注意如果你在迁移脚本里看到DEFAULT 0带引号这在 PostgreSQL 里居然能过因为它走的是字符串输入转换。但我不建议这么写原因有两个一是语义不清晰看到0的人会以为默认值是字符串二是如果将来列类型改成其它类型这类写法的兼容性很差。1.3 谁最常踩到这个坑从我处理过的 case 来看这个错误集中出现在三类场景里第一类是从 MySQL 迁过来的人。MySQL 里的BOOL/BOOLEAN本质上是TINYINT(1)的别名底层存的就是 0 和 1给布尔列设置默认值 0/1 是完全合法的。很多 MySQL 老脚本里到处都是is_active tinyint(1) DEFAULT 1。这类脚本直接改成 PostgreSQL 语法时如果只把字段类型改成 boolean、默认值还保留1立刻就会炸出标题上的错误。第二类是用 ORM 但手写了 SQL 的人。以 SQLAlchemy 为例如果写Column(Boolean, server_default0)SQLAlchemy 会原样把0塞进默认值表达式到了 PostgreSQL 这边就是DEFAULT 0必然报错。Django、Rails 也有类似的情况只要迁移文件里出现RunSQL带着手写的DEFAULT 0/1就跑不掉。第三类是纯粹手滑。老一批开发习惯用 0/1 表达“假/真”写 DDL 的时候肌肉记忆直接把DEFAULT 1打出来了。这种情况没有历史包袱改掉习惯就行。2. 亲手复现一次报错全过程2.1 CREATE TABLE 阶段就会爆先建一张带问题的表让错误完整地出现在眼前CREATE TABLE t_user ( id integer PRIMARY KEY, is_active boolean DEFAULT 0 );在 psql 里执行这条语句你会看到ERROR: column is_active is of type boolean but default expression is of type integer LINE 2: is_active boolean DEFAULT 0 ^ HINT: You will need to rewrite or cast the expression.注意细节psql 不但告诉你哪一列出问题还指出了出错行和出错位置最后附上 hint——需要重写表达式或做类型转换。所以严格来说这个报错不是“无解”它是一个非常友好的提示。如果把DEFAULT 0改成DEFAULT 0CREATE TABLE 能成功。但这只是绕过了输入转换检查不是正路。我建议直接写成CREATE TABLE t_user ( id integer PRIMARY KEY, is_active boolean DEFAULT false );对于表示“是否有效”“是否启用”之类的字段用true/false表达语义最清晰。如果业务上默认是启用就写true默认是禁用就写false。2.2 ALTER TABLE 修改默认值也一样会翻车除了建表给已存在的表补默认值时也会触发同一个错误ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT 1;报错和上面一模一样。这里值得注意即使表里已经有数据甚至列里已经存了 0/1 这些值这个 ALTER 语句照样会被 PostgreSQL 拒绝。因为修改默认值这一步只关心“默认表达式”的类型不关心历史数据长什么样。正确的写法ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT true;这也是线上最常见的修复语句。改完之后已有的行不受任何影响默认值只对之后新插入的行生效。2.3 为什么 PostgreSQL 这么“轴”既然 MySQL 能容忍 0/1为什么 PostgreSQL 非要在 DDL 阶段拦一道这要从类型系统设计说起。PostgreSQL 的类型转换分三档隐式转换、赋值转换、显式转换。隐式转换是解析器自动选的比如integer到bigint这种转换不会丢失信息赋值转换发生在 INSERT 或 UPDATE 的目标列已知时显式转换就是::boolean用户亲手指定。pg_cast系统表里记录了所有允许的类型转换。你去查integer到boolean会发现根本没有隐式转换和赋值转换的条目只有一条显式转换路径。这意味着 PostgreSQL 压根不打算让你无声无息地把数字当布尔用因为 0 和 1 能表达“假和真”但 2、-1、100 呢如果允许隐式转换一个DEFAULT 2就会让 boolean 列出现第三态这在逻辑上是灾难。所以这个报错其实是在替你把关。它把类型问题暴露在迁移脚本执行的那一刻而不是等你线上跑了几百万行数据之后才发现默认值语义不对。MySQL 的宽松在这里反而成了坑。3. 定位与修复的完整实操流程3.1 用系统目录把所有可疑默认值揪出来线上出问题的时候往往不止一张表、一个列有问题。最怕的是你改完一个下一个又爆出来。与其一个个试错不如主动查一遍。PostgreSQL 把默认值表达式存在pg_attrdef系统表里但里面存的是内部节点格式不能直接读要用pg_get_expr函数转成可读的 SQL 文本。配合pg_attribute和pg_class等系统表可以写出这样一个排查脚本SELECT n.nspname AS schema_name, c.relname AS table_name, a.attname AS column_name, pg_get_expr(d.adbin, d.adrelid) AS default_expr FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_attrdef d ON d.adrelid a.attrelid AND d.adnum a.attnum WHERE a.atttypid boolean::regtype AND pg_get_expr(d.adbin, d.adrelid) ~ ^[-]?[0-9]$;这个查询的逻辑是先筛选出类型为 boolean 的列然后看它们的默认表达式中是不是纯整数。那串正则^[-]?[0-9]$匹配的就是类似0、1、-1这样的默认值。我自己在迁移项目里跑过这个脚本效果很好能在几分钟内把整个实例里所有有隐患的 boolean 列全部列出来。顺带一提System catalogs 里的adbin字段初看很劝退但pg_get_expr是处理它的标准函数任何 DDL 排查场景几乎都离不开它。3.2 修改默认值时的三种正确姿势定位到问题列之后修复方法其实就一个原则让默认表达式返回 boolean 类型。最简单的做法ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT true; ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT false;如果不想写死true/false也可以保留一个布尔表达式。比如默认值想表达“根据某条件计算得出”可以写ALTER TABLE t_user ALTER COLUMN is_active SET DEFAULT (1 1);但说实话这种绕弯子的写法没有实际价值反而会让后来的人看代码时摸不着头脑。直接用true或false是零沟通成本的选择。还有一种场景你手头遗留的脚本里用了DEFAULT 0/DEFAULT 1而且这脚本还牵扯到其它数据库。我建议也统一改成true/false因为0这类写法对 PostgreSQL 能过但换到某些严格要求类型的数据库或者跨库迁移工具时还是会有解读歧义。3.3 存在 0/1 历史数据的表怎么处理如果项目还处于开发阶段直接改默认值就完事了。但如果是从老系统迁过来的表原来的列类型是smallint或 MySQL 的tinyint(1)里面已经存了 0 和 1那你的工作分两步。第一步建表时先把类型定义成 boolean默认值写成false或true避开发默认值报错。第二步导入数据时处理类型转换。如果导入工具支持表达式转换可以直接映射UPDATE t_user SET is_active (old_column 0);这里推荐用 0而不是 1。为什么因为历史数据里很可能混入了一些脏数据比如有人误插了 2或者原字段允许 NULL。如果用old_column::boolean这种硬转遇到 2 会直接报“invalid input syntax for type boolean”而 0会把所有非 0 的非 NULL 值都转为 true语义上更稳妥。当然执行前最好先确认一下原字段的实际取值分布SELECT old_column, count(*) FROM raw_data GROUP BY old_column;如果发现除 0/1 之外的值先跟业务方确认它到底该算 true 还是 false不要自作主张。还有一种更保守的做法在 PostgreSQL 里先建smallint类型的临时列数据原样灌进来确认无误后再用ALTER COLUMN ... TYPE boolean USING (col 0)把列类型改掉。这种两步走方案适合数据量大、不容许返工的场合。3.4 ORM 场景下的默认值写法咨询群里被这个问题问得最多的其实是写 Django SelectRelated、SQLAlchemy 或者 ActiveRecord 迁移的人。这里我分别说下要注意的点。SQLAlchemy 的坑很典型。很多人这样写Column(Boolean, server_default0)在 PostgreSQL 底层生成的就是DEFAULT 0报错没商量。正确的写法有两种from sqlalchemy import text, Boolean, Column # 写法一推荐语义清楚 Column(Boolean, server_defaulttext(true)) # 写法二显式类型转换 Column(Boolean, server_defaulttrue)注意server_default接收的是一个字符串它会原样拼进 SQL所以在这个字符串里写true而不是0。Django 的情况好一点models.BooleanField(defaultFalse)在给 PostgreSQL 生成迁移时默认值会被序列化成正确的布尔字面量。但如果你的迁移文件是手写的或者用了RunSQL自定义 SQL那就要自己留意在 PostgreSQL 迁移里布尔默认值一律写true/false或true/false不要写 0/1。Rails 的 ActiveRecord 里t.boolean :active, default: 0在 PostgreSQL 适配器下会自动帮你处理成DEFAULT false但如果你在execute方法里手写了DEFAULT 0同样会撞上这个错误。结论是一样的手写 SQL 时注意类型模板生成时信得过但它不会替你擦手写脚本的屁股。4. 高频问题排查与一次线上事故复盘4.1 常见问题排查速查表把这几年遇见的相关问题整理成一张表方便你直接对照处理。错误信息常见原因快速解法column is of type boolean but default expression is of type integerboolean 列默认值用了 0/1 字面量ALTER TABLE ... SET DEFAULT true/falsecolumn is of type boolean but default expression is of type character varying默认值写了带引号的字符串如1改成1::boolean或直接true/falsecolumn is of type character varying but default expression is of type integer字符列默认值直接写了数字默认值加引号DEFAULT 0column is of type integer but default expression is of type boolean整数列默认值写了true/false改成1/0或转换成intinvalid input syntax for type boolean导入数据时遇到 0/1 之外的整数用USING (col 0)转换或先清洗数据另外补充两个 psql 小技巧。批量执行迁移脚本时每次都担心遇到错误还继续往下跑可以在 psql 里设置psql -d your_db -v ON_ERROR_STOP1 -f migrate.sql这样一旦遇到错误psql 会立即停止避免后面的脚本在已经错误的状态下继续执行、产生新的脏数据。遇到看不懂的报错时先执行\errverbose它会输出详细版本的错误信息经常包含额外提示。4.2 从 MySQL 迁移 PostgreSQL 时的类型映射建议如果你正打算把老项目从 MySQL 迁到 PostgreSQL建表脚本里的类型映射是关键中的关键。MySQL 的TINYINT(1)转为 PostgreSQL 的boolean虽然逻辑上对但默认值和数据内容必须一起处理不能只改类型。我建议机械地做一遍匹配检查把所有建表脚本里的tinyint(1)/bool/boolean字段摘出来逐个看它的 DEFAULT 子句。出现DEFAULT 0、DEFAULT 1的直接替换成DEFAULT false、DEFAULT true。如果脚本数量特别大可以用正则批量替换但一定要先备份并且替换后抽查搜一下DEFAULT 0和DEFAULT 1在脚本里是否还剩在 boolean 列后面。曾经有团队在迁移时用脚本机械地把tinyint(1)替换成boolean却漏掉了 DEFAULT 子句结果迁移执行到一半炸了排查了整整一个下午才发现是默认值没改。4.3 我处理过的一次线上事故复盘说个真实经历。前两年我接手过一个老项目的数据迁移源库是 MySQL目标库是 PostgreSQL。当时我用工具自动生成了建表脚本脚本里带了一大堆is_active boolean DEFAULT 0这样的写法。我心想这是工具生成的脚本应该靠谱就直接在生产库上跑。结果脚本执行到第 14 条语句时psql 甩出了标题里那个错误。本来预估 5 分钟跑完的事硬是花了半个多小时。我当时的第一反应是手动把DEFAULT 0改成DEFAULT false然后继续执行后面的数据导入。可没想到更隐蔽的问题在后面源库的is_active数据虽然是 0/1但导入工具在把字符串转成 boolean 时遇到源表里一个值为空字符串的行直接报错。最终我花了不少精力排查那行脏数据。这次事故之后我给自己定了几条规矩第一迁移前一定要先检查目标表结构不能完全信任自动生成的 DDL尤其是 DEFAULT 子句。第二导入数据之前先对源数据做一次分布统计所有布尔字段的取值必须先看清楚不能想当然认为只有 0 和 1。第三生产环境执行任何 ALTER 语句前要确认业务低峰期因为ALTER TABLE会拿ACCESS EXCLUSIVE锁表一大就可能堵塞读写。这个经验同样适用于日常开发只要你在 PostgreSQL 里写 DDL就要记住它的类型检查是前置的、强制的。不匹配的表达式在建立阶段就会爆出来这是一个设计上的坦白信号提醒你审视自己的设计而不是头疼医头的临时绕行。最后分享一个小习惯。我每次写迁移脚本开头都会先跑一遍前面的查询把所有 boolean 列的默认表达式列出来看一遍再决定下手改哪句。特别是接手老系统时你不知道里面藏着多少DEFAULT 0、DEFAULT 1或者更奇怪的写法。多花两分钟查一下比执行到一半被报错打断再回头找要省心得多。这种类型的报错一旦摸清规律其实是所有 PostgreSQL 错误里最好对付的那一类因为它给了你精确的列名、精确的原因就差直接告诉你该怎么改了。
