Postgres Pro
Downloads
Home   >   mailing lists

Thread: two foreign keys

two foreign keys

From
Brian
Date:
I have a table that has two foreign keys to the same table.

- operator_id_start
- operator_id_stop

Both point to the operator table.

When displaying the row from the table how do I join so that I can have both Operator table details available?

-----

Re: two foreign keys

From
"Josh Berkus"
Date:
Brian,

> I have a table that has two foreign keys to the same table.
>
> - operator_id_start
> - operator_id_stop
>
> Both point to the operator table.
>
> When displaying the row from the table how do I join so that I can
>  have both Operator table details available?

Aha!  I love the classic novice questions.  So much easier than "I
 can't get Perl::SSElay to connect with Pgsql>"

The bit of SQL knowledge you're missing is called "table aliasing."
  This is where you give a table a different name in the query than its
 real name.  Aliasing allows you to refer to the same table more than
 once in a query.

So, to answer your question, making up quite a bit of detail about your
 tables that I don't have:

SELECT session_id, session_name, op_start.operator_name,
 op_end.operator_name
FROM sessions
   JOIN operators op_start ON sessions.operator_start_id =
 op_start.operator_id
   JOIN operators op_end ON sessions.operator_end_id =
 op_start.operator_id;

Got it?  Now go buy yourself a copy of "SQL Queries for Mere Mortals."

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: two foreign keys

From
Tom Lane
Date:
"Josh Berkus" <josh@agliodbs.com> writes:
> SELECT session_id, session_name, op_start.operator_name,
>  op_end.operator_name
> FROM sessions
>    JOIN operators op_start ON sessions.operator_start_id =
>  op_start.operator_id
>    JOIN operators op_end ON sessions.operator_end_id =
>  op_start.operator_id;

I think you meant "ON sessions.operator_end_id = op_end.operator_id"
at the end there.  Otherwise a fine example...

            regards, tom lane

starting the postmaster

From
Brian
Date:
I just upgraded to RedHat 7.2 and I'm having a problem starting the postmaster with the -i argument for tcpip.

The /etc/rd.d/init.d/postgresql file has a line in it like this:


su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start  > /dev/null 2>&1" < /dev/null

if I insert an -i before the -D... or after it... or behind the -p or where ever it fails.

have been messing with the script... trying different things that I find in the documentation, some of it from my
imagination.Some of it works... but hangs the boot process at startup 

What's the correct way to do this?

thanks,
Brian

Re: starting the postmaster

From
John Taylor
Date:
On Thursday 07 March 2002 20:08, Brian wrote:
> I just upgraded to RedHat 7.2 and I'm having a problem starting the postmaster with the -i argument for tcpip.
>
> The /etc/rd.d/init.d/postgresql file has a line in it like this:
>
>
> su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start  > /dev/null 2>&1" < /dev/null
>
> if I insert an -i before the -D... or after it... or behind the -p or where ever it fails.

Try:

su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p '/usr/bin/postmaster -i' start  > /dev/null 2>&1" <
/dev/null

Regards
JohnT

Re: starting the postmaster

From
Brian Scandale
Date:
Hi John,

I am getting a FAILED notice on your construction.

if I delete the   >/dev/null 2>&1   I can see:

/usr/bin/pg_ctl: /usr/bin/postmaster -i: No such file or directory

as a response...

so I tried removing the single quotes ' and I get this as a response:

starting service pg_ctl: invalid option: -i

-------
still struggling here,
Brian




At 10:13 PM 3/7/02 +0000, John Taylor wrote:
>On Thursday 07 March 2002 20:08, Brian wrote:
>> I just upgraded to RedHat 7.2 and I'm having a problem starting the postmaster with the -i argument for tcpip.
>>
>> The /etc/rd.d/init.d/postgresql file has a line in it like this:
>>
>>
>> su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster start  > /dev/null 2>&1" < /dev/null
>>
>> if I insert an -i before the -D... or after it... or behind the -p or where ever it fails.
>
>Try:
>
>su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p '/usr/bin/postmaster -i' start  > /dev/null 2>&1" <
/dev/null
>
>Regards
>JohnT