PostgreSQL修改文本列转numeric类型时报invalid input syntax错误

Hey Glenn, let's break down why your ALTER TABLE statement is failing and how to fix it!

What's Causing the Error? #

The error ERROR: invalid input syntax for type numeric: "" tells us exactly what's wrong: your _2010_10 column contains empty strings (""). When you run trim(_2010_10) on an empty string, it stays empty—and PostgreSQL can't convert an empty string to a numeric type.

How to Fix It #

You have a few options depending on how you want to handle those empty values:

Option 1: Convert Empty Strings to NULL

If empty values should be treated as NULL (which is allowed in a numeric column), adjust your USING clause to check for empty strings first:

ALTER TABLE cadata.pricetorentratio 
ALTER COLUMN _2010_10 TYPE numeric 
USING (CASE WHEN trim(_2010_10) = '' THEN NULL ELSE trim(_2010_10)::numeric END);

Option 2: Replace Empty Strings with a Default Value

If you want to replace empty strings with a specific numeric value (like 0, adjust this to fit your business logic), first update the rows, then convert the column type:

-- Update empty strings to your default value
UPDATE cadata.pricetorentratio 
SET _2010_10 = '0' 
WHERE trim(_2010_10) = '';

-- Now convert the column type
ALTER TABLE cadata.pricetorentratio 
ALTER COLUMN _2010_10 TYPE numeric 
USING (trim(_2010_10)::numeric);

Option 3: First Check for All Invalid Values

If you're unsure if there are other non-numeric values in the column (like letters or special characters), run this query to find all problematic rows first:

SELECT _2010_10 
FROM cadata.pricetorentratio 
WHERE trim(_2010_10) = '' OR trim(_2010_10) !~ '^[0-9]+(\.[0-9]+)?$';

This will return any empty strings or values that don't match a valid numeric format (integers or decimals), so you can clean them up before converting.

Key Note #

PostgreSQL is strict about converting strings to numeric—only valid numeric strings (e.g., 18.74, 5, 100.0) work. Empty strings or non-numeric text will always throw this error, so handling those edge cases first is essential.

内容的提问来源于stack exchange，提问作者Glenn G.