ERROR: JOIN/USING types 'integer' and 'character varying' not matched

[Michael .]

I had an error before involving a temporary table, and
that has been taken care of...
The last message I wrote where it seemed to have
needed it after I added it was because of different
\connects. Could someone please help me with this
next error as I'm importing to a new server?
I am getting quite a few of these...
CREATE VIEW care_plan_note_state AS
SELECT p.firm_id, p.patient_id, p.visit_id,
p.problem_id, p.patient_problem_id, p.note,
p."sequence", p.deleted, p.created, p.mo
dified, p.edited_by, p.inked, i.visit_id AS visit_in,
v.inked AS "_inked" FROM ((care_plan_note_history p
JOIN (SELECT vh.firm_id, v
h.patient_id, vh.visit_id, vh.discipline_id,
vh.person_id, vh.visit_type_id, vh.arrival, vh.depart,
vh.systolic, vh.diastolic, vh.we
ight, vh.temperature, vh.pulse, vh.respiration,
vh.progress_notes, vh.log, vh.inked, vh."sequence",
vh.deleted, vh.created, vh.modif
ied, vh.edited_by FROM visit_history vh WHERE
(vh."sequence" = (SELECT max(visit_history."sequence")
AS max FROM visit_history WHERE
(((vh.firm_id = visit_history.firm_id) AND
(vh.patient_id = visit_history.patient_id)) AND
(vh.visit_id = visit_history.visit_id)))
)) v USING (firm_id, patient_id, visit_id)) JOIN
care_plan_note_history i ON (((((((p.firm_id =
i.firm_id) AND (p.patient_id = i.pat
ient_id)) AND (p.visit_id = i.visit_id)) AND
(p.problem_id = i.problem_id)) AND
(p.patient_problem_id = i.patient_problem_id)) AND (
i."sequence" = 1)))) WHERE ((p."sequence" = (SELECT
max(care_plan_note_history."sequence") AS max FROM
care_plan_note_history WHERE
(((((care_plan_note_history.firm_id = p.firm_id) AND
(care_plan_note_history.patient_id = p.patient_id))
AND (care_plan_note_history
..visit_id = p.visit_id)) AND
(care_plan_note_history.problem_id = p.problem_id))
AND (care_plan_note_history.patient_problem_id = p.
patient_problem_id)))) AND (NOT ((p.deleted =
'1'::"varchar") AND (i.visit_id = p.visit_id))));
ERROR: JOIN/USING types 'integer' and 'character
varying' not matched

Thank you,
Mike

__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Oliver Elphick]

On Thu, 2003-12-11 at 20:14, Michael . wrote:

I had an error before involving a temporary table, and
that has been taken care of...
The last message I wrote where it seemed to have
needed it after I added it was because of different
\connects. Could someone please help me with this
next error as I'm importing to a new server?
I am getting quite a few of these...
CREATE VIEW care_plan_note_state AS
SELECT p.firm_id, p.patient_id, p.visit_id,
p.problem_id, p.patient_problem_id, p.note,
p."sequence", p.deleted, p.created, p.mo
dified, p.edited_by, p.inked, i.visit_id AS visit_in,
v.inked AS "_inked" FROM ((care_plan_note_history p
JOIN (SELECT vh.firm_id, v
h.patient_id, vh.visit_id, vh.disci ....snip... ERROR: JOIN/USING types 'integer' and 'character
varying' not matched

I gave up on trying to follow all the nested brackets - I think you
would help both yourself and us if you formatted such a query!

I think the error means that you are joining on columns of differing
types. (E.g.: SELECT * FROM a JOIN b ON a.id = b.id, where a.id and
b.id are of different types.) That suggests an error in the query or a
discrepancy in the data structures; but if it is intentional, cast one
of them to match the other.

--
Oliver Elphick Ol************@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"The spirit of the Lord GOD is upon me; because the
LORD hath anointed me to preach good tidings unto the
meek; he hath sent me to bind up the brokenhearted, to
proclaim liberty to the captives, and the opening of
the prison to them that are bound."
Isaiah 61:1
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Oliver Elphick <ol**@lfix.co.uk> writes:

I think the error means that you are joining on columns of differing
types. (E.g.: SELECT * FROM a JOIN b ON a.id = b.id, where a.id and
b.id are of different types.)

More specifically, SELECT ... FROM a JOIN b USING (foo), where a.foo
and b.foo are of incompatible types. The result of the JOIN is supposed
to have just one merged column "foo", but Postgres can't figure out what
type to make that column.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings