Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
7
down vote

favorite












Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?



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




postgresql database-design primary-key timestamp




share|improve this question

















  • 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
    yesterday











  • 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
    yesterday






  • 5




    n°32 of falsehoods programmers believe about time
    – Neyt
    yesterday






  • 3




    I'm curious as to why you'd want this?
    – Nanne
    11 hours ago










  • @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
    3 hours ago
















up vote
7
down vote

favorite












Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?



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




postgresql database-design primary-key timestamp




share|improve this question

















  • 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
    yesterday











  • 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
    yesterday






  • 5




    n°32 of falsehoods programmers believe about time
    – Neyt
    yesterday






  • 3




    I'm curious as to why you'd want this?
    – Nanne
    11 hours ago










  • @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
    3 hours ago












up vote
7
down vote

favorite









up vote
7
down vote

favorite











Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?



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




postgresql database-design primary-key timestamp




share|improve this question













Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?



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





postgresql database-design primary-key timestamp





share|improve this question












share|improve this question




share|improve this question








edited yesterday









Erwin Brandstetter

83.9k8148253




83.9k8148253









asked yesterday









John Puskin

4213




4213







  • 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
    yesterday











  • 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
    yesterday






  • 5




    n°32 of falsehoods programmers believe about time
    – Neyt
    yesterday






  • 3




    I'm curious as to why you'd want this?
    – Nanne
    11 hours ago










  • @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
    3 hours ago












  • 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
    yesterday











  • 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
    yesterday






  • 5




    n°32 of falsehoods programmers believe about time
    – Neyt
    yesterday






  • 3




    I'm curious as to why you'd want this?
    – Nanne
    11 hours ago










  • @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
    3 hours ago







3




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
yesterday





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
yesterday













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
yesterday




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
yesterday




5




5




n°32 of falsehoods programmers believe about time
– Neyt
yesterday




n°32 of falsehoods programmers believe about time
– Neyt
yesterday




3




3




I'm curious as to why you'd want this?
– Nanne
11 hours ago




I'm curious as to why you'd want this?
– Nanne
11 hours ago












@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
3 hours ago




@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
3 hours ago










3 Answers
3






active

oldest

votes

















up vote
15
down vote













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.








share|improve this answer























  • 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
    yesterday






  • 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
    yesterday










  • @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
    yesterday










  • @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
    yesterday

















up vote
9
down vote













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().






share|improve this answer




























    up vote
    7
    down vote













    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.






    share|improve this answer























    • 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
      8 hours ago











    Your Answer







    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: false,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );








     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214110%2fcan-current-timestamp-be-used-as-a-primary-key%23new-answer', 'question_page');

    );

    Post as a guest

















    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    15
    down vote













    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.








    share|improve this answer























    • 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
      yesterday






    • 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
      yesterday










    • @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
      yesterday










    • @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
      yesterday














    up vote
    15
    down vote













    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.








    share|improve this answer























    • 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
      yesterday






    • 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
      yesterday










    • @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
      yesterday










    • @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
      yesterday












    up vote
    15
    down vote










    up vote
    15
    down vote









    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.








    share|improve this answer















    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.









    share|improve this answer















    share|improve this answer



    share|improve this answer








    edited yesterday


























    answered yesterday









    peterh

    77811028




    77811028











    • 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
      yesterday






    • 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
      yesterday










    • @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
      yesterday










    • @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
      yesterday
















    • 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
      yesterday






    • 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
      yesterday










    • @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
      yesterday










    • @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
      yesterday















    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
    yesterday




    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
    yesterday




    2




    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
    yesterday




    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
    yesterday












    @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
    yesterday




    @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
    yesterday












    @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
    yesterday




    @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
    yesterday












    up vote
    9
    down vote













    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().






    share|improve this answer

























      up vote
      9
      down vote













      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().






      share|improve this answer























        up vote
        9
        down vote










        up vote
        9
        down vote









        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().






        share|improve this answer













        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().







        share|improve this answer













        share|improve this answer



        share|improve this answer











        answered yesterday









        Linas

        2563




        2563




















            up vote
            7
            down vote













            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.






            share|improve this answer























            • 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
              8 hours ago















            up vote
            7
            down vote













            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.






            share|improve this answer























            • 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
              8 hours ago













            up vote
            7
            down vote










            up vote
            7
            down vote









            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.






            share|improve this answer















            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.







            share|improve this answer















            share|improve this answer



            share|improve this answer








            edited yesterday


























            answered yesterday









            Erwin Brandstetter

            83.9k8148253




            83.9k8148253











            • 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
              8 hours ago

















            • 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
              8 hours ago
















            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
            8 hours ago





            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
            8 hours ago













             

            draft saved


            draft discarded


























             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214110%2fcan-current-timestamp-be-used-as-a-primary-key%23new-answer', 'question_page');

            );

            Post as a guest
































































            Comments

            Post a Comment

            Popular posts from this blog

            What is the equation of a 3D cone with generalised tilt?

            Image
            Clash Royale CLAN TAG #URR8PPP up vote 2 down vote favorite What is the equation of a 3D cone with generalized tilt? I've noticed that in most equations given to represent a cone, there is no parameter which defines the tilt of the cone in 3D space and that most of them have their point at the origin $(0,0,0)$ - I was wondering if anyone could give me a more generalized cone equation for a cone in any position in 3D space 3d share | cite | improve this question edited Jul 25 '16 at 0:05 ervx 9,425 3 13 36 asked Jul 24 '16 at 15:38 Charlene 11 2 2 Welcome to Math.SE! Could you please explain a few things to help people give an answer useful to you: 1. What do you mean by "generalized tilt"? 2. Are you asking about a right circular cone? Does the angle at the vertex matter? 3. What form of answer (implicit, parametric...?) are you looking for? 4. If this is homework, what tools do you have available, an...
            Read more

            Color the edges and diagonals of a regular polygon

            Image
            Clash Royale CLAN TAG #URR8PPP up vote 5 down vote favorite 1 Here is the problem: For what $n$ is it possible to color the edges and diagonals of an $n$-side regular polygon with $dfracbinomn23$ colors, such that you use every color exactly three times and for every color the three segments (edges or diagonals) with that color form a triangle? Trivially $nequiv 0 text or 1 pmod3$. I can also prove that if the statement is true for $k$ than it is true for $3k$ as well. How to finish? Please help! Thanks combinatorial-geometry share | cite | improve this question edited Aug 6 at 21:57 alcana 118 4 asked Aug 6 at 21:15 Leo Gardner 356 11 Even assuming "polyhpn" in the title is a typo for polygon , it is unclear what you mean by "the edges and sides". Aren't the side of a regular polygon the same as the edges? A regular $n$-sided polygon has $n$ edges. – hardmath Aug 6 at 21:20 3 $n$ ne...
            Read more

            Relationship between determinant of matrix and determinant of adjoint?

            Image
            Clash Royale CLAN TAG #URR8PPP up vote 2 down vote favorite We are studying adjoints in class, and I was curious if there is a relationship between the determinant of matrix A, and the determinant of the adjoint of matrix A? I assume there would be a relationship because finding the adjoint requires creating a cofactor matrix and then transposing it. linear-algebra determinant adjoint-operators share | cite | improve this question asked Nov 17 '17 at 17:32 CluelessCoder 32 5 For a square matrix $A$ of order $n$, we have $A(operatornameadj A)=(operatornameadj A)A=|A|I_n$ where $I_n$ is the identity matrix of order $n$. This should tell you about the determinant of the adjoint in terms of that of $A$ (use multiplicative property and other elementary properties of determinants). – Prasun Biswas Nov 17 '17 at 17:35 1 @CluelessCoder: see here: math.stackexchange.com/questions/516127/…...
            Read more