postgresql 我的 CASE 有什么问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9707863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's wrong with my CASE?
提问by user519753
CASE WHEN table1.text IS NULL THEN table2.numbers ELSE table1.text END AS newcolumn
CASE WHEN table1.text IS NULL THEN table2.numbers ELSE table1.text END AS newcolumn
When running this code I keep getting this error: ERROR: CASE types character varying and integer cannot be matched
运行此代码时，我不断收到此错误： 错误：CASE 类型字符变化和整数无法匹配
You would think that this would not cause problems since I'm creating a new column within my query. I'd also like to point out that I'm using an old version of PostgreSQL if that helps.
您会认为这不会导致问题，因为我在查询中创建了一个新列。如果有帮助，我还想指出我使用的是旧版本的 PostgreSQL。
采纳答案by Clodoaldo Neto
CASE WHEN table1.text IS NULL THEN table2.numbers::text ELSE table1.text END AS newcolumn
回答by Kaf
Problem is you are trying to add table1.text and table2.numbers into a single column. These two columns are two diff data types. try following
问题是您试图将 table1.text 和 table2.numbers 添加到单个列中。这两列是两种不同的数据类型。尝试跟随
CASE WHEN table1.text IS NULL THEN CAST(table2.numbers AS VARCHAR(50)) ELSE table1.text END AS newcolumn
回答by Brian Driscoll
The data type represented by table2.numbersand table1.texthas to be the same, so it looks like in this case you'll need to CASTthe value of table2.numbers
由table2.numbers和表示的数据类型table1.text必须相同，因此在这种情况下，您需要使用CAST的值table2.numbers
回答by Anthony Grist
I think the error is pretty clear - the types of the two columns that may be used by the CASE statement aren't compatible.
我认为错误很明显 - CASE 语句可能使用的两列的类型不兼容。
Why would you think that trying to use values from two columns of different types would not cause problems? You may be creating a new column in the result set, but it still has to have a type and that has to match all the potential values.
为什么您认为尝试使用来自不同类型的两列的值不会导致问题？您可能正在结果集中创建一个新列，但它仍然必须有一个类型并且必须匹配所有潜在值。
It may be possible in a lot of cases for it to infer the type, but that's risky and may not necessarily be the choice that you want it to make, so it's better for it to force you to make the decision. You'll need to modify the type of one or the other columns in the CASE statement.
在很多情况下它可能会推断类型，但这是有风险的，并且可能不一定是您希望它做出的选择，因此它最好强迫您做出决定。您需要修改 CASE 语句中一列或其他列的类型。
回答by Vinnie
Try ISNULL
尝试 ISNULL
CASE WHEN ISNULL(table1.text) = 1 THEN table2.numbers ELSE table1.text END AS newcolumn
