Mam kolumnę, którą chciałbym default do losowo wygenerowanego int8 w określonym zakresie. Chciałbym również, aby ta kolumna była unikalna, więc jeśli generowana jest losowa wartość, która już istnieje, należy ją ponownie wyrzucić.

Więc moje pytanie brzmi: jaki najbardziej idiomatyczny sposób na wykonanie powyższego jest w PostgreSQL, najlepiej z dobrą wydajnością i obsługą wstawiania zbiorczego.

Na przykład, gdybym miał Person tabelę z kolumnami name i id i chciałbym, aby id była losowym unikatem int8 w zakresie {{X5 }}. Chciałbym móc wstawić Paul, Kelly, David i Katie i uzyskać coś takiego:

| Name  |   id   |
+-------+--------+
| Paul  | 314563 |
| Kelly | 592103 |
| David | 127318 |
| Katie | 893134 |

Bez ryzyka duplikatów i bez ryzyka niepowodzenia wstawiania.

Zasięg nie będzie na tyle duży, żebym mógł bezpiecznie założyć, że nigdy się nie zderzą (czyli Paradoks Urodzinowy).

Powinienem również powiedzieć, że chcę prawdziwej nieprzewidywalnej losowości, więc szyfr w sekwencji się nie liczy.

Istnieje wiele odpowiedzi na temat generowania liczb losowych, więc głównym celem pytania jest aspekt niepowtarzalności.

Mając to na uwadze, doceniony byłby czysty i skuteczny sposób generowania int8 równomiernie w dowolnie dużym zakresie. random() * n zaczyna mieć luki, gdy n > 2 ^ 53 (być może wcześniej).

1
semicolon 19 grudzień 2019, 15:09
Proszę pokazać przykładowe dane. Co masz na myśli przez unikalną, losowo wygenerowaną liczbę?
 – 
Gordon Linoff
19 grudzień 2019, 15:10
Zmieniłem pytanie. Mam na myśli tylko int8 w przybliżeniu jednolicie wygenerowany w zakresie, który byłbym w stanie określić.
 – 
semicolon
19 grudzień 2019, 15:16
To się nazywa kodowanie danych
 – 
Himanshu Ahuja
19 grudzień 2019, 15:18
Co masz na myśli?
 – 
semicolon
19 grudzień 2019, 15:19
1
Klucze będą używane w adresach URL i wewnętrznie w kodzie klienta, więc nie chciałbym ujawniać informacji, takich jak kolejność tworzenia lub współczynnik/liczba tworzenia wierszy.
 – 
semicolon
19 grudzień 2019, 15:56

1 odpowiedź

Możliwe rozwiązanie:

create table t (name varchar (50), id int);

-- 1. generate a list of possible ids
-- 2. cast the id in varchar to make a string after that
-- 3. aggregate all the possible ids in a string with a ',' separator
-- 4. make the string a list
-- 5. select a random value in this list
-- 6. insert the new id for the wanted name. Here 'test'
with cte as 
(
  SELECT a.n as possible_id
  from generate_series(1, 150000) as a(n)
  where not exists (select 1 from t where t.id = a.n)
)
, cte_s as 
(
  select 
    (
        string_to_array( 
            string_agg( 
                cast(possible_id as varchar)
                , ','
            )
            , ','
        )
    )[floor(random() * 150000 + 1)] as new_id
  from cte
)
insert into t
values ('test', (select new_id from cte_s)::int); 

-- test that your code doing what you want
select *
from t;

http://sqlfiddle.com/#!17/17d42/26

Możesz oczywiście zmienić maksymalną kwotę, jak chcesz.

0
Jaisus 7 styczeń 2020, 11:41
Czy mógłbyś trochę przejrzeć kod? Nie jestem pewien, do czego służą wszystkie łańcuchy, wygląda na to, że chciałbym trzymać się int8 oprócz nazwy 'test'.
 – 
semicolon
27 grudzień 2019, 03:13
Zmodyfikowałem odpowiedź zgodnie z żądaniem. Masz link sqlfiddle, aby go przetestować, ale nie wahaj się zapytać, czy masz inne pytania.
 – 
Jaisus
7 styczeń 2020, 11:42