如何通过Bash脚本用psql更新PostgreSQL的Numeric类型数据（去末尾零）

核心问题 #

你的脚本执行后显示UPDATE 1但数据库无变化，本质原因是**numeric(9,6)类型的特性**：该类型强制保留6位小数，无论你传入的是6.94还是6.940000，PostgreSQL都会自动补零到6位小数存储，所以看起来数据没有变化。

另外，脚本中的WHERE atomic_mass = $ATOMIC_MASS存在风险：当多个行的atomic_mass值相同时，会误更新所有匹配行；而且numeric类型中6.940000和6.94是等价的，更新操作相当于把值改成了它本身，自然看不到实际变化。

修复方案 #

方案1：修改字段类型（彻底解决存储显示问题） #

如果业务允许不需要固定6位小数，将atomic_mass字段的类型改为可变精度的numeric类型，比如：

ALTER TABLE properties ALTER COLUMN atomic_mass TYPE numeric(9);

或者直接用无精度限制的numeric：

ALTER TABLE properties ALTER COLUMN atomic_mass TYPE numeric;

修改后，存储时会保留有效数字，不会自动补零，此时再运行优化后的脚本就能看到数据变化。

方案2：优化脚本，用主键定位更新（避免误操作） #

如果必须保留numeric(9,6)类型，且确实需要更新（规范数据），建议用atomic_number（主键）作为WHERE条件，避免误更新：

#!/bin/bash
PSQL="psql -X --username=****** --dbname=periodic_table --tuples-only -c"

echo -e "\n~~~~~ Remove trailing zeros & update db ~~~~~\n"

# 获取原子序数和原子量
ELEMENTS=$($PSQL "SELECT atomic_number, atomic_mass FROM properties")
echo "$ELEMENTS" | while read ATOMIC_NUMBER BAR ATOMIC_MASS
do
  # 去除末尾零和多余小数点
  ATOMIC_MASS_CLEAN=$(echo "$ATOMIC_MASS" | sed -e 's/0*$//' -e 's/\.$//')
  echo "$ATOMIC_NUMBER | $ATOMIC_MASS | $ATOMIC_MASS_CLEAN"
  # 用主键atomic_number定位更新，避免误操作
  UPDATE_DB=$($PSQL "UPDATE properties SET atomic_mass = '$ATOMIC_MASS_CLEAN' WHERE atomic_number = $ATOMIC_NUMBER")
  echo "$UPDATE_DB"
done

注意：这里将$ATOMIC_MASS_CLEAN用单引号包裹，避免数值格式导致的SQL语法错误；同时用atomic_number作为更新条件，确保只更新目标行。

方案3：不修改存储，查询时格式化输出 #

如果不需要改变存储，只是希望查询时显示无末尾零的数值，可以直接在查询时处理，不需要修改数据库：

SELECT 
  atomic_number,
  TRIM(trailing '0' FROM TRIM(trailing '.' FROM atomic_mass::text)) AS atomic_mass_clean
FROM properties;

这样查询结果会自动去除末尾的零和小数点，不影响原始存储。

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