Re: BUG #3778: Natural join with filter problem

Please keep the list CC'd.

Laurent HERVE wrote:
> in fact, i don't know why the column "code_document" is used because it
> is not in the primary key of the tables.
> So as this column is nullable, it gives unexpected results.
>
> I thought only the columns on the primary key are taken into account
> when building a join. Maybe i misunderstood something in how postgresql
> builds the join.

Ah, no. According to the docs:

NATURAL is shorthand for a USING list that mentions all columns in
the two tables that have the same names.

Because there's a column called code_document in both tables, you'll
have to use an INNER JOIN to get what you want. If you want to eliminate
the duplicate columns from the result set like a NATURAL JOIN does, you
can use INNER JOIN ... USING (list of columns).

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com