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.
geomand 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 tblin psql).geomoriginate from? You havea.id, geom, a.condition_join_1, ...Soidandcondition_join_1come froma. Andgeom? 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 theCREATE TABLEscript from the SQL pane.boreal_mixedwood_labeled?