CURRENT_TIMESTAMP be used as a
Is there a possibility that two or more different INSERTs, get the same
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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().
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
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.