Postgres中如何在同一列存储int、float和boolean值？

针对你需要在同一列存储boolean、int、float类型值，且要支持数值比较的需求，结合你当前使用jsonb的场景，提供以下几种可行方案：

方案一：基于现有jsonb类型实现比较逻辑 #

利用PostgreSQL的jsonb_typeof函数判断值的类型，通过CASE语句将不同类型统一转换为numeric类型（符合你将true视为1、false视为0的需求），再进行比较：

直接使用CASE表达式查询 #

SELECT *
FROM points
WHERE 
  CASE jsonb_typeof(value)
    WHEN 'boolean' THEN (value::boolean)::numeric
    WHEN 'number' THEN value::numeric
    ELSE NULL -- 若存在其他类型可按需处理，比如返回0或直接过滤
  END > 0;

封装为自定义函数复用 #

如果需要多次使用该逻辑，可以封装成一个不可变函数，简化查询语句：

CREATE OR REPLACE FUNCTION jsonb_to_numeric(val jsonb)
RETURNS numeric AS $$
BEGIN
  RETURN CASE jsonb_typeof(val)
    WHEN 'boolean' THEN (val::boolean)::numeric
    WHEN 'number' THEN val::numeric
    ELSE NULL
  END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用函数查询：

SELECT *
FROM points
WHERE jsonb_to_numeric(value) > 0;

方案二：统一存储为numeric类型 #

如果不需要保留原始类型信息，可以在写入数据时将所有值转换为numeric类型：

• 将true转成1，false转成0
• int和float直接转成numeric

写入示例（假设应用传入的原始值为text类型）：

INSERT INTO points (rid, time, value)
VALUES (
  '2d9c5bdc-dfc5-4ce5-888f-59d06b5065d0',
  '2021-01-01 00:00:10.000000 +00:00',
  CASE 
    WHEN 'true' IN ('true', 'false') THEN ('true'::boolean)::numeric
    ELSE 'true'::numeric
  END
);

这种方式下查询非常直接：

SELECT * FROM points WHERE value > 0;

如果需要保留原始类型信息，可以额外添加一列value_type（比如存'boolean'、'int'、'float'）来记录原始类型。

方案三：使用text类型存储（不推荐） #

将所有值存为text类型，查询时手动转换：

SELECT *
FROM points
WHERE 
  CASE 
    WHEN value IN ('true', 'false') THEN (value::boolean)::numeric
    ELSE value::numeric
  END > 0;

该方案的缺点是无法保证存入数据的格式合法性，若写入了无法转换为数值的字符串，查询会报错，不如jsonb能原生保证类型有效性。

方案四：自定义复合类型（繁琐，仅适合需严格保留类型的场景） #

定义一个包含三种类型字段的复合类型，存储时仅填充对应字段：

CREATE TYPE mixed_value AS (
  bool_val boolean,
  int_val integer,
  float_val double precision
);

查询时通过COALESCE统一转换为数值比较：

SELECT *
FROM points
WHERE 
  COALESCE(
    (value).bool_val::numeric,
    (value).int_val::numeric,
    (value).float_val::numeric
  ) > 0;

此方案操作繁琐，仅当必须严格区分原始类型且无法通过其他方式记录时考虑使用。

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