Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

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

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.

StackExchange Database Administrators Q#214110, answer score: 22