1

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

ERROR:  CASE types character varying and numeric cannot be matched

CODE:

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, and d ecosite and phase are all numeric type. Only condition is varchar.

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.

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?

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);

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'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.

6
  • You forgot the table qualification for geom and your version of Postgres. Please add both to the question. Ideally, you would add table definitions to clarify things quickly (what you get with \d tbl in psql). Commented Dec 3, 2015 at 23:17
  • sorry, new to postgres... what do you mean by table qualification for geom?? Commented Dec 3, 2015 at 23:26
  • Which table does geom originate from? You have a.id, geom, a.condition_join_1, ... So id and condition_join_1 come from a. And geom? More generally, if you provide table definitions I can give you a much simpler query instead of playing this guessing game. Since you are using pgAdmin: You can also just copy the CREATE TABLE script from the SQL pane. Commented Dec 3, 2015 at 23:29
  • geom was supposed to come from a. That is the only location geom existed. The boreal_mixedwood table is a non-spatial table. Added sql pane for current_condition Commented Dec 3, 2015 at 23:41
  • Almost there. I removed the irrelevant bits. Can you provide the same for boreal_mixedwood_labeled? Commented Dec 3, 2015 at 23:44

2 Answers 2

1

Try eliminating the nested case. Maybe it makes a difference to the compiler for some reason:

CASE
    WHEN b.condition = 'ERROR' AND c.condition2 = 'ERROR' THEN d.condition3
    WHEN b.condition = 'ERROR' THEN c.condition2
    ELSE c.condition
END as current_condition,
...
Sign up to request clarification or add additional context in comments.

3 Comments

It doesn't make any difference, though.
@Erwin, I certainly agree that it shouldn't but it wouldn't be the first time rearranging an expression mysteriously fixed a problem.
It won't hurt to try, but this I know.
0

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.

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

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

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

and:

d.ecophase3
c.ecophase2
b.ecophase

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.

How?

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

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

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.

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 ...

1 Comment

b, c, and d ecosite and phase are all numeric type. Only condition is varchar. From what I have read so far, it seems as though the first case identifies a varchar so that is what it is looking for out of ecosite and ecophase. However, I just want condition to select the records of ecosite and ecophase to use. I do not see how it can be simplified to yield the result that I am looking for, but that is not what I am after right now anywys. thx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.