The majority of times we use integer sequences for primary keys to maintain uniqueness of row in a table. Couldn't we use timestamps instead of integers for this purpose? Is there any flaw in that strategy, because I assume that the machine cannot do two things at the same "time"...right?

I am working on a database with huge hibernate sequences in the keys, so I thought of using timestamps as a solution. Are there any approaches like this that are used in practise that work with any database? That would be good to know.

EDIT : What I am asking is that since no two moments of time are the same for us (humans), does that also hold true for computers? I know that the computers track time through the vibration of some crystal (not sure how small a time it can measure).

In the database I have some table where no primary key is applicable except time (Audit trail), and specifying a sequence (hibernate_sequence) increases the number to an extremely high degree which makes it inconvenient to work with other tables as it affects all tables with sequence as primary key.

So, if PostgreSQL can guarantee that no two insert/update operations will occur at the same timestamp, then I could use it as a primary key, BUT if not then obviously I cannot use this solution.

Basically the question is: Does PostgreSQL provide any guarantee (through some approach or configuration) that no two records will be inserted/updated at the same "time"?

Thanks

  • 2
    Do you forget that INSERT query may insert more than 1 row? If you try to use timestamp as a PK, all of this recodrs will obtain the same timestamp value, and no records will be inserted because query fails with PK duplication error. – Akina Aug 2 at 12:35
  • 3
    If your business rules allow that and guarantee uniqueness of timestamps, why not? – mustaccio Aug 2 at 12:54

Not a good idea.

PostgreSQL provides many functions that pertain to current timestamps,

  • transaction_timestamp()
  • statement_timestamp()
  • clock_timestamp()
  • timeofday()
  • now()

The problem is none of these are insertion time. You have multiple backends. Each backend can process one transaction. These can transactions can start at the same time, or they can call these functions at the same time (they'll be on different threads/processes).

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.