pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
finding the most recent records
3+ messages / 3 participants
[nested] [flat]

* finding the most recent records
@ 1999-04-30 14:31  JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
  0 siblings, 2 replies; 3+ messages in thread

From: JT Kirkpatrick @ 1999-04-30 14:31 UTC (permalink / raw)
  To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] finding the most recent records
@ 1999-04-30 14:55  Mark Jewiss <mark@office.knowledge.com>
  parent: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Mark Jewiss @ 1999-04-30 14:55 UTC (permalink / raw)
  To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; +Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] finding the most recent records
@ 1999-04-30 16:10  Tom Lane <tgl@sss.pgh.pa.us>
  parent: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-04-30 16:10 UTC (permalink / raw)
  To: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>; +Cc: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-04-30 16:10 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-30 14:31 finding the most recent records JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
1999-04-30 14:55 ` Mark Jewiss <mark@office.knowledge.com>
1999-04-30 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox