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(at)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'