Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP?

  • 3
    I heard of an app that was coded using timestamp as a PK, back in the 1990's. Ten years later PCs became faster and timestamps were duplicated. This caused very serious problems, as the app's functionality was highly critical. Also, PK uniqueness was not properly enforced throughout the app. – Victor Di Leo Aug 6 at 1:14
  • Is there a possibility that two or more different INSERTs, get the same CURRENT_TIMESTAMP? It's enough one query inserted 2 records for collision. So the answer for a subject question is "NO". – Akina Aug 6 at 4:40
  • 5
  • 3
    I'm curious as to why you'd want this? – Nanne Aug 7 at 6:55
  • @Nanne I suspect this: MySQL has a very nice handling of automatically incremented integer ids (simply an auto_increment attribute to the field). PostgreSQL has not, it has a serial type which is far lesser beautiful. – peterh Aug 7 at 15:16

As per the documentation, the precision of the CURRENT_TIMESTAMP is microseconds. Thus, the probability of a collision is low, but possible.

Now imagine a bug which happens very rarely, and causes database errors. How hard is to debug it? It is a far worser bug than one which is at least deterministic.

The more broad context: you probably want to avoid these little nuances with the sequences, which is particularly annoying if you are accustomed to MySQL.

Furthermore, if you are using transactions (most web frameworks, particularly the Java ones, do!), then the timestamps will be the same inside a transaction! A demonstration:

postgres=# begin;
BEGIN
postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2018-08-06 02:41:42.472163+02
(1 Zeile)

postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2018-08-06 02:41:42.472163+02
(1 Zeile)

See you? Two selects, exactly the same result. I don't type so fast. ;-)

--

If you want easily IDs, avoiding the usage of the sequences, then generate some hash value from the real identifiers of the records. For example, if your database has humans, and you know that their birthdate, mother's maiden name and real name uniquely identifies them, then use an

md5(mother_name || '-' || given_name || '-' birthday);

as id. Beside that, you can use a CreationDate column, after what you index the table, but it is not a key (which is the id).

P.s. In general, it is a very good practice to make your DB so deterministic, as it is possible. I.e. the same operation should create exactly the same change in the DB. Any timestamp-based ID fails this important feature. What if you want to debug or simulate anything? You replay an operation and the same object will be created with a different id... it is really not hard to follow, and it spares a lot of work hours.

P.s.2 Anybody checking your code in the future, won't have the best opinion seeing timestamp-generated ids, on the reasons above.

  • Even if you are not using transactions, you are in fact using transactions (because Postgres doesn't have a no-transactions mode, it just has autocommit). So if you do an INSERT of multiple rows, they all get the same current_timestamp. And then you have triggers... – Kevin Aug 6 at 5:32
  • 2
    I have heard about an app that broke because of the 2 guys had the same name and born on the same day and their mother names were identical. Ouch. If it CAN happen it WILL happen, sooner or later. – Balazs Gunics Aug 6 at 14:12
  • @BalazsGunics Helló :-) It was just an example. For example, in real scenarios, I think id as email address or the chosen user name (which can be registered only if it doesn't exist yet) is enough. Government tends to use some personal identification number, like 1 870728 0651. The important thing is, binding an id to a timestamp or to a random value is imho a bad practice, because it makes the DB lesser deterministic. – peterh Aug 6 at 16:24
  • @BalazsGunics Beside that, two people with the same mother_name + given_name + birthday, it would cause still a deterministic error. Primary key collision due to that two transactions having inserts happened in in the same microsecond, it still a non-deterministic, and very hardly reproducable problem. – peterh Aug 6 at 16:56

Not really because it’s possible for CURRENT_TIMESTAMP to provide two identical values for two subsequent INSERTs (or a single INSERT with multiple rows).

Use a time-based UUID instead: uuid_generate_v1mc().

Strictly speaking: No. Because CURRENT_TIMESTAMP is a function and only one or more table columns can form a PRIMARY KEY constraint.

If you mean to create a PRIMARY KEY constraint on a column with the default value CURRENT_TIMESTAMP, then the answer is: Yes, you can. Nothing keeps you from doing it, like nothing keeps you from shooting apples from your son's head. The question still wouldn't make sense while you don't define the purpose of it. What kind of data are column and table supposed to hold? What rules are you trying to implement?

Typically, the idea is bound to run into duplicate key errors since CURRENT_TIMESTAMP is a STABLE function returning the same value for the same transaction (the start time of the transaction). Multiple INSERTs in the same transaction are bound to collide - like other answers already illustrated. The manual:

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within the same transaction bear the same time stamp.

Postgres timestamps are implemented as 8-byte integers representing up to 6 fractional digits (microsecond resolution).

If you are building a table that is supposed to hold no more than one row per microsecond and that condition is not going to change (something named sensor_reading_per_microsecond), then it might make sense. Duplicate rows are supposed to raise a duplicate key violation error. That's an exotic exception, though. And the data type timestamptz (not timestamp) would probably be preferable. See:

I would still rather use a surrogate serial primary key instead. And add a UNIQUE constraint on the timestamp column. Fewer possible complications, not relying on implementation details of the RDBMS.

  • Even sensor_reading_per_microsecond may collide if you can't absolutely guarantee that the timing of each reading is perfectly synchronised with respect to the previous one; a sub-microsecond deviation (which is often not impossible) breaks the scheme. In general I'd still totally avoid this. (Mind you, as you've indicated, in such a case, the resulting collision may be desirable!) – Lightness Races in Orbit Aug 7 at 10:07
  • @Lightness: I am in complete agreement. Your example with unintended time-shift after a rounded tiny deviation illustrates another caveat. – Erwin Brandstetter Aug 8 at 0:44

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.