0

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?

Viktor Andriichuk
  • 83
  • 2
  • 11
  • What do you mean with "obtained uniquely"? A collision-free hash function? – Laurenz Albe Aug 19 '21 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 Aug 19 '21 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 Aug 20 '21 at 09:28
  • Can you look on it https://stackoverflow.com/questions/68888827/postgresql-some-troubles-to-insert-from-select-with-on-conflict? – Viktor Andriichuk Aug 23 '21 at 07:58

1 Answers1

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)
                        ) ;
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • 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? – Viktor Andriichuk Aug 22 '21 at 04:17
  • 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. – Belayer Aug 22 '21 at 04:44