mjhjmtu

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.

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:

• Ignoring time zones altogether in Rails and PostgreSQL

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.