Postgres: how to generate a unique number based on a string?

Question

I need to make a query in PostgreSQL, which will put in a column that has the BIGINT type, a value and this value should be obtained uniquely from the connection of data from two adjacent columns, and in them, the records are stored in rows. And these lines are not numbers, but just words. Is it possible?

What do you mean with "obtained uniquely"? A collision-free hash function? — Laurenz Albe
– Laurenz Albe, Commented Aug 19, 2021 at 16:38
Please describe the issue you are trying to solve. Not, as you have done, how you are trying to solve it. If ensuring the combination of the columns is unique then add a unique constraint. Something like constraint col_ab_unique unique (a,b) — Belayer
– Belayer, Commented Aug 19, 2021 at 20:14
I'm Data Engineer and my manager get me a task for this column in DB: prompt_input_value_id - generate unique sequence number for the combination of each prompt_input_value and collect_project_id. prompt_input_value_id - has a bigint type, prompt_input_value and collect_project_id - strings (varchars). — Viktor Andriichuk
– Viktor Andriichuk, Commented Aug 20, 2021 at 9:28
Can you look on it stackoverflow.com/questions/68888827/…? — Viktor Andriichuk
– Viktor Andriichuk, Commented Aug 23, 2021 at 7:58

Belayer · Accepted Answer · 2021-08-20 21:48:55Z

1

There is no need to generate anything. Create a sequence/identity column with datatype bigint. Just allow Postgres the generate the number. Then as mentioned create a unique constraint on the 2 strings.

-- Postgres v10 and update 
create table some_table( st_id   bigint generated always as identity
                        , col_1  varchar
                        , col_2  varchar
                        , constraint some_table_pk 
                                     primary key (st_id) 
                        , constraint some_table_bk 
                                     unique (col_1, col_2)
                        ) ;
                        
-- For prior to v10 
create table some_table( st_id   bigserial
                        , col_1  varchar
                        , col_2  varchar
                        , constraint some_table_pk 
                                     primary key (st_id) 
                        , constraint some_table_bk 
                                     unique (col_1, col_2)
                        ) ;

answered Aug 20, 2021 at 21:48

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Viktor Andriichuk Over a year ago

Thnx a lot! Can you please tell me what will happen цгер st_id from your code if the (col_1, col_2) is not unique?

Belayer Over a year ago

It the combination is not unique Postgres throws the error SQL Error [23505]: ERROR: duplicate key value violates unique constraint ... when you attempt to insert the second set. Of course if they were not unique then you could never have a value and this value should be obtained uniquely from the connection anyway.

Collectives™ on Stack Overflow

Postgres: how to generate a unique number based on a string?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related