Re: 'natural join' and 'join ... using' giving different

On Wed, 9 Jan 2002, Bruno Wolff III wrote:

> I am seeing different results when using 'natural join' as opposed to
> 'join ... using' on what I think the equivalent columns should be.
> The 'join ... using' version of the query gives the expected answer.
> I have tried this on 7.1.3 (built locally) and 7.2b4 with a patch
> to how foreign keys are checked. The machines where both running
> Redhat linux 6.1 with a 2.2.16 kernel.
>
> Below is a sample psql session that I think illustrates the problem.

Wouldn't games natural join crate be on both gameid and touched
as opposed to only gameid?  I don't remember exactly but natural
join joins on columns of the same name right?

>
> Script started on Wed Jan  9 11:17:06 2002
> [bruno@wolff bruno]$ psql
> Welcome to psql, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> area=> \d games
>                          Table "games"
>  Attribute |           Type           |        Modifier
> -----------+--------------------------+------------------------
>  gameid    | text                     | not null
>  title     | text                     | not null
>  touched   | timestamp with time zone | not null default 'now'
> Indices: games_pkey,
>          title_idx
> Constraints: (gameid ~ '^[A-Z0-9]+$'::text)
>              (title ~ '^[!-~]+( [!-~]+)*$'::text)
>
> area=> \d crate
>                          Table "crate"
>  Attribute |           Type           |        Modifier
> -----------+--------------------------+------------------------
>  areaid    | text                     | not null
>  gameid    | text                     | not null
>  rate      | integer                  | not null default 5000
>  frq       | integer                  | not null default 0
>  opp       | integer                  | not null default 0
>  rmp       | integer                  | not null default 0
>  trn       | integer                  | not null default 0
>  touched   | timestamp with time zone | not null default 'now'