PSQL error ambigous column name when doing multi join statment

December 22, 2021

I’m trying to solve somehow the error in my PSQL query of ambiguous origin_type column name

The query

SELECT
    *
FROM
    "message"
    INNER JOIN "member" ON "member"."id" = "message"."member_id"
    INNER JOIN "conversation" ON "conversation"."id" = "message"."conversation_id"
WHERE
    "message_type" in('USER_MESSAGE')
    AND "origin_type" in('CONSENTEE_RECIPIENT')
    AND "origin_type" in('CONSENTEE')

I have 2 columns with the same name and need a way to avoid this error but as I’m not practical at this point I’m blocked as don’t know the better way of fixing this.

I tried to use aliases without success

>Solution :

You should use "member"."origin_type" or "conversation"."origin_type" or "message"."origin_type" instead of just "origin_type" depending on which table’s origin_type you need.