嵌套的Case语句类型错误(postgres) [英] Nested Case statement type error (postgres)

问题描述

我创建了一些postgres代码，给我一个错误:

I have some postgres code I have created that is giving me an error:

ERROR:  CASE types character varying and numeric cannot be matched

代码:

CREATE TABLE current_condition_joined AS SELECT
a.id, a.geom, a.condition_join_1, a.condition_join_2, a.condition_join_3,
(CASE WHEN b.condition = 'ERROR' THEN (CASE WHEN c.condition2 = 'ERROR' THEN d.condition3
                                       ELSE c.condition2
                                       END)
 ELSE b.condition
 END) current_condition,
(CASE WHEN b.condition= 'ERROR' THEN (CASE WHEN c.condition2 = 'ERROR' THEN d.ecosite3
                                      ELSE c.ecosite2
                                      END)
 ELSE b.ecosite
 END) current_ecosite,
(CASE WHEN b.condition = 'ERROR' THEN (CASE WHEN c.condition2 = 'ERROR' THEN d.ecophase3
                                       ELSE c.ecophase2
                                       END)
 ELSE b.ecophase
 END) current_ecophase,
(CASE WHEN b.condition = 'ERROR' THEN (CASE WHEN c.condition2 = 'ERROR' THEN d.consite3
                                       ELSE c.consite2
                                       END)
 ELSE b.consite
 END) current_consite,
(CASE WHEN b.condition = 'ERROR' THEN (CASE WHEN c.condition2 = 'ERROR' THEN d.conphase3
                                       ELSE c.conphase2
                                       END)
 ELSE b.conphase
 END) current_conphase
 FROM current_condition a, boreal_mixedwood_labeled b, boreal_mixedwood_labeled c, boreal_mixedwood_labeled d
 WHERE a.label = b.label_join_1
   and a.label2 = c.label_join_2
   and a.label3 = d.label_join_3;

b，c和d生态位和相位都是数字类型.唯一的条件是varchar.

b, c, and d ecosite and phase are all numeric type. Only condition is varchar.

在第二和第三列中创建是发生问题的地方.我假设我遇到错误，因为在案例的第一部分中，它引用了一个varchar，但是第二种情况的结果是数值.我想使用条件"ERROR"来选择要使用的数字值.

In the second and third column creations is where the problem is occurring. I assume that I am getting the error as in the first part of the case it is referencing a varchar but the result of the second case is numeric. I want to use the condition "ERROR" to select the number values to use.

我是Postgres(9.4.5)的新手，但精通sql.我正在Windows计算机上的pgAdmin(1.18.1版)中工作.

I am new to postgres (9.4.5) but fairly proficient in sql. I am working in pgAdmin (v. 1.18.1) on a windows machine.

我看过我的问题的其他实例，但它们不考虑嵌套语句. 我的机箱有什么问题?

I have looked at other instances of my problem but they do not consider nested statements. What's wrong with my CASE?

从PGAdmin的"SQL"窗格中创建表current_condition的代码:

CODE that creates the table current_condition from the SQL pane in PGAdmin:

CREATE TABLE current_condition (
  geom geometry,
  condition_join_1 text,
  condition_join_2 text,
  condition_join_3 text,
  id serial NOT NULL,
  CONSTRAINT current_condition_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_current_condition_geom
  ON current_condition USING gist (geom);

boreal_mixedwood_labeled表的代码:

CODE for the boreal_mixedwood_labeled TABLE:

CREATE TABLE boreal_mixedwood_labeled
(
  objectid serial NOT NULL,
  label character varying(255),
  label2 character varying(255),
  label3 character varying(255),
  condition character varying(255),
  ecophase numeric(15,6),
  ecosite numeric(15,6),
  conphase character varying(255),
  consite character varying(255),
  condition2 character varying(255),
  ecophase2 numeric(15,6),
  ecosite2 numeric(15,6),
  conphase2 character varying(255),
  consite2 character varying(255),
  condition3 character varying(255),
  ecophase3 numeric(15,6),
  ecosite3 numeric(15,6),
  conphase3 character varying(255),
  consite3 character varying(255),
  CONSTRAINT boreal_mixedwood_labeled_pkey PRIMARY KEY (objectid)

ERWIN的答案是正确的.尽管列中的值是数字，但出于某种原因，表中将它们作为字符.一定是从我的导入中自动发生的.

ERWIN's answer was correct. While the values in the columns were numeric, the table had them as character for some reason. Must have happened automatically from my import.

推荐答案

该错误的直接原因是错误消息告诉您的数据类型不匹配.

The immediate cause for the error is the data type mismatch, as the error message tells you.

出现问题的地方是第二列和第三列.

In the second and third column creations is where the problem is occurring.

CASE表达式的所有可能结果都需要共享兼容的数据类型，因此它们必须匹配，但显然不匹配. 手册说明:

All possible results of a CASE expression need to share compatible data types so those must match but, obviously, don't. The manual instructs:

所有 结果 表达式的数据类型必须可转换为 单输出类型.有关更多详细信息，请参见第10.5节.

The data types of all the result expressions must be convertible to a single output type. See Section 10.5 for more details.

假设您引用的是current_condition和current_ecosite-实际上是第6列和第7列，则这些列需要具有匹配的数据类型:

Assuming you refer to current_condition and current_ecosite - which are actually the 6th and 7th column, these need to have matching data types:

d.ecosite3
c.ecosite2
b.ecosite

和:

d.ecophase3
c.ecophase2
b.ecophase

更好的查询

在boreal_mixedwood_labeled的缺失表定义之前，我的有根据的猜测是您可以从根本上简化此查询:

Better query

Pending the missing table definition for boreal_mixedwood_labeled, my educated guess is you can radically simplify to this query:

SELECT a.id, a.geom, a.condition_join_1, a.condition_join_2, a.condition_join_3
     , COALESCE(d.condition3, c.condition2, b.condition) AS current_condition
     , COALESCE(d.ecosite3,   c.ecosite2,   b.ecosite)   AS current_ecosite
     , COALESCE(d.ecophase3,  c.ecophase2,  b.ecophase)  AS current_ecophase
     , COALESCE(d.consite3,   c.consite2,   b.consite)   AS current_consite
     , COALESCE(d.conphase3,  c.conphase2,  b.conphase)  AS current_conphase
FROM   current_condition a
LEFT   JOIN boreal_mixedwood_labeled b ON a.label  = b.label_join_1
LEFT   JOIN boreal_mixedwood_labeled c ON a.label2 = c.label_join_2
                                      AND b.condition = 'ERROR'
LEFT   JOIN boreal_mixedwood_labeled d ON a.label3 = d.label_join_3
                                      AND c.condition2 = 'ERROR';

数据类型必须仍然匹配.

Data types must still match.

这不仅更短，而且可能更快.

This is not just shorter, but probably faster, too.

如果b.condition = 'ERROR'开始等，则仅第二次加入boreal_mixedwood_labeled.

Only join to boreal_mixedwood_labeled a second time if b.condition = 'ERROR' to begin with, etc.

然后，您可以使用更简单的 COALESCE 表达式:除非我们需要，否则d.*为NULL，c.*也是如此.返回第一个非空值.

Then you can use a simpler COALESCE expression: d.* is NULL unless we need it, same goes for c.*. The first non-null values is returned.

如果boreal_mixedwood_labeled中的某些列可以为NULL，则逻辑可能会中断.这一切都取决于...多田！ ...您的实际表格定义. (我已经提到足够了吗?).别忘了在下一个问题中加入它...

If some of the columns in boreal_mixedwood_labeled can be NULL the logic can break. It all depends on ... tada! ... your actual table definition. (Did I mention that enough already?). Don't forget to include it with your next question ...

这篇关于嵌套的Case语句类型错误(postgres)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！