Databases
StackOverflow2013
Postsdbo
POperform true on joined tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POperform true on joined tables
    primarykey
    data
    text
    <p>In PostgreSQL 8.4.13 I have two tables representing card games (each with 3 players) and the score results of those games. The players are identified by <code>id</code> column and the games by <code>gid</code>:</p> <pre><code># \d pref_games Table "public.pref_games" Column | Type | Modifiers --------+-----------------------------+---------------------------------------------------------- gid | integer | not null default nextval('pref_games_gid_seq'::regclass) rounds | integer | not null stamp | timestamp without time zone | default now() Indexes: "pref_games_pkey" PRIMARY KEY, btree (gid) Referenced by: TABLE "pref_scores" CONSTRAINT "pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid) ON DELETE CASCADE # \d pref_scores Table "public.pref_scores" Column | Type | Modifiers ---------+-----------------------+----------- id | character varying(32) | not null gid | integer | not null money | integer | not null last_ip | inet | quit | boolean | Indexes: "pref_scores_pkey" PRIMARY KEY, btree (id, gid) "pref_scores_gid_index" btree (gid) Foreign-key constraints: "pref_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES pref_games(gid) ON DELETE CASCADE "pref_scores_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) ON DELETE CASCADE </code></pre> <p>I'm trying to find out if a player with an <code>id='OK261593357402'</code> has ever played with the player <code>id='MR3689735717800138910'</code> - so that I can allow them to grade each other at my web site.</p> <p>I think I need to call a <code>perform true</code> on an <code>inner join</code> - is this correct please?</p> <p>So I'm trying:</p> <pre><code># select * from pref_games g inner join pref_scores s on (s.gid=g.gid) where s.id='OK261593357402'; gid | rounds | stamp | id | gid | money | last_ip | quit ---------+--------+----------------------------+----------------+---------+-------+----------------+------ 2124241 | 20 | 2013-05-01 12:26:54.052086 | OK261593357402 | 2124241 | 28 | 93.232.91.105 | f 2125575 | 17 | 2013-05-01 16:53:21.090822 | OK261593357402 | 2125575 | 32 | 93.232.91.105 | f 2125881 | 20 | 2013-05-01 17:47:26.15633 | OK261593357402 | 2125881 | -31 | 93.232.91.105 | f 2126242 | 0 | 2013-05-01 18:41:06.769132 | OK261593357402 | 2126242 | 0 | 93.232.91.105 | f 2126244 | 0 | 2013-05-01 18:41:12.495192 | OK261593357402 | 2126244 | 0 | 93.232.91.105 | t 2126259 | 0 | 2013-05-01 18:42:39.974518 | OK261593357402 | 2126259 | 0 | 93.232.91.105 | t 2126613 | 33 | 2013-05-01 19:27:11.88462 | OK261593357402 | 2126613 | -132 | 93.232.91.105 | f 2126813 | 0 | 2013-05-01 19:57:05.23061 | OK261593357402 | 2126813 | 0 | 93.232.91.105 | t 2127299 | 20 | 2013-05-01 20:36:42.021133 | OK261593357402 | 2127299 | 136 | 93.232.91.105 | f 2127821 | 0 | 2013-05-01 21:33:32.168757 | OK261593357402 | 2127821 | 0 | 93.232.91.105 | f 2127830 | 0 | 2013-05-01 21:34:47.694645 | OK261593357402 | 2127830 | 0 | 93.232.91.105 | t 2128012 | 21 | 2013-05-01 22:04:03.850061 | OK261593357402 | 2128012 | 55 | 93.232.91.105 | f 2129551 | 13 | 2013-05-02 10:08:37.348426 | OK261593357402 | 2129551 | -32 | 79.250.39.175 | f 2129818 | 13 | 2013-05-02 11:21:50.998484 | OK261593357402 | 2129818 | 71 | 79.250.39.175 | f 2130467 | 11 | 2013-05-02 13:55:00.034698 | OK261593357402 | 2130467 | -79 | 79.250.39.175 | f 2130470 | 0 | 2013-05-02 13:55:41.298932 | OK261593357402 | 2130470 | 0 | 79.250.39.175 | f 2130476 | 0 | 2013-05-02 13:56:22.359713 | OK261593357402 | 2130476 | 0 | 79.250.39.175 | f ..... </code></pre> <p>But I only see the <code>id='OK261593357402'</code> above, i.e. I was expecting to see all his game partners too, but they are not delivered.</p> <p>I've also tried:</p> <pre><code># select * from pref_games g left outer join pref_scores s on (s.gid=g.gid) where s.id='OK261593357402'; gid | rounds | stamp | id | gid | money | last_ip | quit ---------+--------+----------------------------+----------------+---------+-------+----------------+------ 2124241 | 20 | 2013-05-01 12:26:54.052086 | OK261593357402 | 2124241 | 28 | 93.232.91.105 | f 2125575 | 17 | 2013-05-01 16:53:21.090822 | OK261593357402 | 2125575 | 32 | 93.232.91.105 | f 2125881 | 20 | 2013-05-01 17:47:26.15633 | OK261593357402 | 2125881 | -31 | 93.232.91.105 | f 2126242 | 0 | 2013-05-01 18:41:06.769132 | OK261593357402 | 2126242 | 0 | 93.232.91.105 | f 2126244 | 0 | 2013-05-01 18:41:12.495192 | OK261593357402 | 2126244 | 0 | 93.232.91.105 | t 2126259 | 0 | 2013-05-01 18:42:39.974518 | OK261593357402 | 2126259 | 0 | 93.232.91.105 | t 2126613 | 33 | 2013-05-01 19:27:11.88462 | OK261593357402 | 2126613 | -132 | 93.232.91.105 | f 2126813 | 0 | 2013-05-01 19:57:05.23061 | OK261593357402 | 2126813 | 0 | 93.232.91.105 | t ... </code></pre> <p>Unfortunately same result...</p> <p>Should I maybe just make a series of <code>select</code>s combined with <code>exists in</code> instead?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlexander Farber
    1. USAlexander Farber
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POperform true on joined tables
      1. USrenqHIT
      1. VTFavorite
    1. COWhat's `perform true`? Some kind of Oracle-ism or a feature of a proprietary PostgreSQL extension?
      singulars
      1. POperform true on joined tables
      1. USCraig Ringer
    2. COI meant: `PERFORM TRUE FROM ....` and then `IF FOUND THEN .... ENDIF`
      singulars
      1. POperform true on joined tables
      1. USAlexander Farber
    3. COOh, you're talking about PL/PgSQL's `PERFORM`, the no-results-returned alternative to `SELECT`, then testing for `FOUND`. Ok, that makes some sense. You never said anything about PL/PgSQL or that you were writing a stored function. (This one reason why showing your code in questions is a good idea).
      singulars
      1. POperform true on joined tables
      1. USCraig Ringer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload