Selecting the most recent timestamptz

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Selecting the most recent timestamptz
Date: 2018-02-22 09:09:22
Message-ID: CAADeyWhjXDF=XfsiJi8G3=sPKw5i+7_M7bQ84OQ+UB-rwKwp1w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good morning, there are these 2 records in a table:

# select m.played, m.mid, m.action, m.gid, m.uid from words_moves m where
gid=10;
played | mid | action | gid | uid
-------------------------------+-----+--------+-----+-----
2018-02-19 14:42:08.46222+01 | 12 | play | 10 | 9
2018-02-20 15:06:01.430634+01 | 216 | expire | 10 | 11
(2 rows)

I try to get the record with the latest timestamp by adding a NOT EXISTS
condition -

# select m.played, m.mid, m.action, m.gid, m.uid from words_moves m where
gid=10 and not exists (select 1 from words_moves x where m.mid=x.mid AND
x.played > m.played);
played | mid | action | gid | uid
-------------------------------+-----+--------+-----+-----
2018-02-19 14:42:08.46222+01 | 12 | play | 10 | 9
2018-02-20 15:06:01.430634+01 | 216 | expire | 10 | 11
(2 rows)

Why are still 2 records returned? I am probably overlooking something
simple, sorry...

Thank you
Alex

P.S. In case more details are needed -

# explain select m.played, m.mid, m.action, m.gid, m.uid from words_moves m
where gid=10 and not exists ( select 1 from words_moves x where m.mid=x.mid
AND x.played > m.played);
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop Anti Join (cost=0.28..43.78 rows=1 width=29)
-> Seq Scan on words_moves m (cost=0.00..27.18 rows=2 width=29)
Filter: (gid = 10)
-> Index Scan using words_moves_pkey on words_moves x (cost=0.28..8.29
rows=1 width=16)
Index Cond: (m.mid = mid)
Filter: (played > m.played)
(6 rows)

# \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
--------+--------------------------+-----------+----------+------------------------------------------
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb | | |
score | integer | | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY (mid)
REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2018-02-22 09:18:04 Re: Selecting the most recent timestamptz
Previous Message Alexander Farber 2018-02-22 08:57:26 Re: Not sure if I should CREATE INDEX for text columns on which I plan to filter later