From: | Nelson Green <nelsongreen84(at)gmail(dot)com> |
---|---|
To: | pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Last inserted row id with complex PK |
Date: | 2014-01-08 18:16:07 |
Message-ID: | CAGo-KZ=Zcc+LMNnNWmoD+q1Pe7yboHqNvPR+sqMPQsgWwTNRaA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jan 8, 2014 at 10:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Francisco Olarte <folarte(at)peoplecall(dot)com> writes:
> > Hi Nelson:
> > On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green <nelsongreen84(at)gmail(dot)com>
> wrote:
> >> I have a projects log table with a three column PK, project_num,
> person_num,
> >> and sequence, where each new entry for a project/person combination
> >> increments the sequence, which is not an auto incrementing sequence. Is
> >> there any way to retrieve the last entry to the table? For instance, if
> the
> >> last entry for person 427 on project 15 was sequence number 125, is that
> >> information available to me anywhere?
>
> > SELECT MAX(sequence) FROM project_log_table WHERE person_num=427 AND
> > project_num=15
>
> Note that this will not work terribly well if there are concurrent
> insertions for the same person/project. If that's not an issue, though,
> it should be fine. It may be OK even if there are sometimes concurrent
> insertions, if you are prepared to retry duplicate-key failures.
>
Concurrency is not likely to be an issue for this system, but I would
certainly plan for it since I can not rule it out.
>
> > If it's a PK it should have an index and I believe it will be quite fast.
>
> It will be fast as long as sequence is the low-order column in the index.
>
> regards, tom lane
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2014-01-08 18:55:25 | Re: Consistent file-level backup of pg data directory |
Previous Message | Nelson Green | 2014-01-08 18:14:22 | Re: Last inserted row id with complex PK |