pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding 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