I want to use the hash function for the primary key in the PostgreSQL table so that PostgreSQL itself generates this value.

  1. Is it possible to generate hashCode using PostgreSQL?
  2. Is it generally accepted to do so? Or is it a bad idea?
  3. If the answers to the first two questions are yes, then how?
  • 2
    And what do you want to achieve with this? - Mike
  • 2
    And I hope you understand that the hash itself cannot be a primary key in principle. because any hash allows collisions, i.e. same hash value for two different inputs - Mike
  • @Mike I watched the techstrim lessons from the maillu on a latency and the guys there explained that it’s better to use hash for security because it prevents potential attackers from understanding the primary key how much in the table of records and you can ignore collisions because of one to seven billions. So I wondered how I could implement it. - Pavel
  • one
    Now I understand why all the projects are so buggy. Do not rely on probabilities. id in the database is better to do the usual autoincrement. Fortunately, the standard mechanisms of the database are designed for this. If security is important, then when transmitting to the client, you encrypt your id with any reversible block cipher and give the client the encrypted value encoded in base64 (or even base85). It will look completely random. And when you receive the request back just decrypt. Without knowing your encryption key, it’s impossible to find the next value or even know the approximate value - Mike
  • although you can still uuid pozamorachivatsya, but I see no reason to inflate the database in volume. you can also refer to these primary keys from other tables ... - Mike

2 answers 2

  1. md5()
  2. Depends on the task
  3. ..

Example:

 t=# create table so78 (h text primary key default md5(now()::text),i int); CREATE TABLE Time: 9.060 ms t=# insert into so78 (i) select 1; INSERT 0 1 Time: 3.317 ms t=# insert into so78 (i) select 1; INSERT 0 1 Time: 6.169 ms t=# select * from so78; h | i ----------------------------------+--- 2e346ec46b4ae31f54bf3ad3d90d3183 | 1 356c21c892c0e523395a6330dff3b4ce | 1 (2 rows) Time: 0.205 ms

can uuid be better? .. Although it can be used like:

 t=# select md5(now()::text)::uuid; md5 -------------------------------------- 1060bdc9-cff8-955c-71bb-7c62d4543965 (1 row) Time: 1.218 ms

or better without the invention of the uuid_generate_v4 wheel . as default PK it ...

    For the reasons stated by Mike in the commentary to the question, it is absolutely not necessary to make the primary key out of the hash due to its non-uniqueness (due to a collision). With a small amount of data, one may get the impression that there is uniqueness, but this is obviously deceptive, and the problem with duplicating the key can "crawl out" unexpectedly.

    Without knowing the details of your task, I cannot judge the advisability of using hashing as such and advise anything.