Re: Last inserted row id with complex PK - Mailing list pgsql-general
| From | Nelson Green |
|---|---|
| Subject | Re: Last inserted row id with complex PK |
| Date | |
| Msg-id | CAGo-KZkwWQkpy2mx4FYoU1vvwZvDCj+ae5Gs7OQdiXvFioxH5g@mail.gmail.com Whole thread Raw |
| In response to | Re: Last inserted row id with complex PK (Alban Hertroys <haramrae@gmail.com>) |
| List | pgsql-general |
On Wed, Jan 8, 2014 at 5:39 PM, Alban Hertroys <haramrae@gmail.com> wrote:
Hi Alban,
That is almost exactly what I am trying to do (create a FK relation). I am sorry
I was so vague, that was not intentional. I just have so many disparate things
going on at once and I just wasn't thinking my question through very clearly.
I am scripting the input of dummy data, where I have a list of projects and
users associated with those projects. My script chooses one pairing at "random"
and inserts a dummy log record. I wanted to be able to look at the entry's in
the order they were inserted in to verify my sequence trigger is working as
intended. The default timestamp gave me that ability, but I think your
suggestion of INSERT ... RETURNING would have been a bit more in line with my
original thought, where the script was doing an insert to the project table and
then logging the PK of that last insert to a temp table. The PK of the temp
table is an auto incrementing sequence, but I wasn't sure how to get the PK of
the log table from the previous INSERT. Since this is a test system I was able
to add the timestamp and then just perform a query, sorting by that. But, the
timestamp solution altered the model, the temp table did not.
Regards,
Nelson
On 08 Jan 2014, at 16:54, Nelson Green <nelsongreen84@gmail.com> wrote:I think the answer rather depends on what you mean with this last inserted row and for what purpose you need it.
> 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?
If you want that information right after it’s been inserted (for example because you need that information in an FK relation), you can use INSERT..RETURNING to return the values of the relevant PK fields.
Hi Alban,
That is almost exactly what I am trying to do (create a FK relation). I am sorry
I was so vague, that was not intentional. I just have so many disparate things
going on at once and I just wasn't thinking my question through very clearly.
I am scripting the input of dummy data, where I have a list of projects and
users associated with those projects. My script chooses one pairing at "random"
and inserts a dummy log record. I wanted to be able to look at the entry's in
the order they were inserted in to verify my sequence trigger is working as
intended. The default timestamp gave me that ability, but I think your
suggestion of INSERT ... RETURNING would have been a bit more in line with my
original thought, where the script was doing an insert to the project table and
then logging the PK of that last insert to a temp table. The PK of the temp
table is an auto incrementing sequence, but I wasn't sure how to get the PK of
the log table from the previous INSERT. Since this is a test system I was able
to add the timestamp and then just perform a query, sorting by that. But, the
timestamp solution altered the model, the temp table did not.
Regards,
Nelson
If that’s not what you’re after, then what is it that determines which record is the “last one”? If you can’t identify such a record from your data while you need that information, then something is missing from your model.
If you’re planning to add such information to your model based on your current data, you might be able to get somewhat reliable results looking at the transaction xid’s that the records were created in. There are a number of pitfalls to that approach though, the most obvious one being transaction xid wraparound. Of course, multiple inserts from the same transaction would (originally) have the same xid, so you wouldn’t be able to determine which one of those would be the latest (unless they’re for the same person/project, obviously).
Such information could then be used to add a field with, for example, an incrementing sequence.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
pgsql-general by date: