Thread: finding the most recent records

I didn't find who originally asked how to do this, but it IS possible to 
identify the most recent if you have a field in the table defined as 
"field1 datetime not null default text 'now'".  you'll only run into a 
problem if more than one record is inserted AT THE EXACT SAME SECOND -- but 
postgres will just put the same timestamp in there (don't define the field 
as unique).  I am putting this field (along with one that defaults to 
current_user) in ALL my tables for our application.  the values are input 
"behind the scenes" by default whenever anyone adds a record, and it gives 
me a simple little audit trail.  for instance, when that deceptive employee 
creates a "vendor" for himself and then creates a false purchase order and 
receipt thereby setting in motion a payment due to himself, i can see by 
whom and when the records were created.  hopefully i have other controls in 
place to prevent this from happening, but the point is that the timestamp 
works fine and can help you identify the most recent records!

jt

On Fri, 30 Apr 1999, JT Kirkpatrick wrote:

> I didn't find who originally asked how to do this, but it IS possible to 
> identify the most recent if you have a field in the table defined as 

This will work, but Postgresql contains a specific datatype that you can
have as a unique identifier. Every time a row is inserted it will take the
number and increase by one. Uses an integer to do this.

Cheers,

Mark.
--
Mark Jewiss
Knowledge Matters Limited

JT Kirkpatrick <jt-kirkpatrick@mpsllc.com> writes:
> "field1 datetime not null default text 'now'".  you'll only run into a
> problem if more than one record is inserted AT THE EXACT SAME SECOND
> -- but postgres will just put the same timestamp in there (don't
> define the field as unique).

Actually, I think "now" means "the time at the start of the current
transaction", so that you'll get the same timestamp applied to all
records inserted within a transaction.  This is good for some things;
for example, you can insert related records into several different
tables and expect them to have the same timestamp, even though the
operation may take more than a second to complete.  But it does mean
that you can't expect a timestamp to serve as a unique ID.  (Use a
SERIAL column for that, instead...)
        regards, tom lane