Postgres Pro
Downloads
Home   >   mailing lists

Thread: CREATE TABLE AS COMMAND

CREATE TABLE AS COMMAND

From
"Bob Powell"
Date:
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
ineach 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
tolist all of the fields except the one I don't want, or is there another way?   Thanks in advance.   

Bob Powell

Re: CREATE TABLE AS COMMAND

From
Bruno Wolff III
Date:
On Fri, Dec 05, 2003 at 13:20:27 -0500,
  Bob Powell <Bob@hotchkiss.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
ineach 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
tolist 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.

Re: CREATE TABLE AS COMMAND

From
Greg Stark
Date:
> On Fri, Dec 05, 2003 at 13:20:27 -0500,
>   Bob Powell <Bob@hotchkiss.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