Lists: | pgsql-general |
---|
From: | "Bob Powell" <Bob(at)hotchkiss(dot)org> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | CREATE TABLE AS COMMAND |
Date: | 2003-12-05 18:20:27 |
Message-ID: | sfd08625.059@grpwise.hotchkiss.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
To whom it may concern,
The following select works fine until I use the CREATE TEMP TABLE AS command because of the matching participant_id's in each table.
SELECT * FROM addresses LEFT OUTER JOIN contacts ON
contacts.participant_id = addresses.participant_id;
Running the select obviously works fine, it's just when I try to create a table when I runn into problems. Do I have to list all of the fields except the one I don't want, or is there another way? Thanks in advance.
Bob Powell
From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Bob Powell <Bob(at)hotchkiss(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: CREATE TABLE AS COMMAND |
Date: | 2003-12-05 18:39:25 |
Message-ID: | 20031205183925.GA17602@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Dec 05, 2003 at 13:20:27 -0500,
Bob Powell <Bob(at)hotchkiss(dot)org> wrote:
> To whom it may concern,
>
> The following select works fine until I use the CREATE TEMP TABLE AS command because of the matching participant_id's in each table.
>
> SELECT * FROM addresses LEFT OUTER JOIN contacts ON
> contacts.participant_id = addresses.participant_id;
>
> Running the select obviously works fine, it's just when I try to create a table when I runn into problems. Do I have to list all of the fields except the one I don't want, or is there another way? Thanks in advance.
You probably want to use an explicit column list instead of * so that
things get associated correctly.
From: | Greg Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: CREATE TABLE AS COMMAND |
Date: | 2003-12-05 18:49:47 |
Message-ID: | 874qwf2j78.fsf@stark.dyndns.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> On Fri, Dec 05, 2003 at 13:20:27 -0500,
> Bob Powell <Bob(at)hotchkiss(dot)org> wrote:
>
> > SELECT * FROM addresses LEFT OUTER JOIN contacts ON
> > contacts.participant_id = addresses.participant_id;
> >
> > Running the select obviously works fine, it's just when I try to create a
> > table when I runn into problems. Do I have to list all of the fields
> > except the one I don't want, or is there another way? Thanks in advance.
You could use USING (participant_id) instead of ON. That only produces a
single participant_id column. It's also less typing.
--
greg