Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] DELETE and JOIN - Mailing list pgsql-general

From David G. Johnston
Subject Re: [GENERAL] DELETE and JOIN
Date
Msg-id CAKFQuwZ8NwcZJQMTcTdBp8xG_CpRF95ae42t1Puun3Nr4kH_rQ@mail.gmail.com
Whole thread Raw
In response to [GENERAL] DELETE and JOIN  (Alexander Farber <alexander.farber@gmail.com>)
Responses Re: [GENERAL] DELETE and JOIN  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Tree view
On Mon, Mar 13, 2017 at 9:39 AM, Alexander Farber <alexander.farber@gmail.com> wrote:
Good evening,

In a 9.5 database I would like players to rate each other and save the reviews in the table:

CREATE TABLE words_reviews (
        uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users ON DELETE CASCADE,
        author integer NOT NULL REFERENCES words_users(uid) ON DELETE CASCADE,
        nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
        review varchar(255),
        updated timestamptz NOT NULL,
        PRIMARY KEY(uid, author)
);

while user names and IP addresses are saved in the other database:

CREATE TABLE words_users (
        uid SERIAL PRIMARY KEY,
        ip inet NOT NULL,
        ..........
);​
 
​[...]
 all previous reviews coming from the same IP in the past 24 hours:

​SELECT (uid, author)  -- locate reviews
FROM word_reviews 
JOIN words_users USING (u_id) 
WHERE u_id IN ( -- from each of the following users...
SELECT wu.u_id
FROM words_users wu
WHERE wu.ip = (SELECT wui.ip FROM words_users wui WHERE wui,uid = in_uid) -- find all users sharing the ip address of this supplied user
)​
AND updated >= [...]  -- but only within the specified time period

David J.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] DELETE and JOIN
Next
From: John Iliffe
Date:
Subject: [GENERAL] Large and Growing Group of Files