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

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/

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                                Oliver.Elphick@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

Oliver Elphick <olly@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