is it possible to populate a primary key of a table based on a column of another table?

Question

I have a table that contains a text-type column(for example graph path : 12 5 7 19 30 ..) and I want to split this text by space and put all of the output strings as a primary key in the other table.
Note that the strings found can be repeated so each time I will be adding a new primary key, I will check if it is already there or not.

a_horse_with_no_name · Answer

Sounds like a simple INSERT ... SELECT statement:
insert into target_table (the_pk_column)
select u.key
from regexp_split_to_table('12 5 7 19 30', 's+') as u(key)
on conflict (the_pk_column) do nothing;

or based on the column of the source table
insert into target_table (the_pk_column)
select u.key
from source_table st
   cross join regexp_split_to_table(st.graph_path, 's+') as u(key)
where st.id = ... --<< whatever you want to select
on conflict (the_pk_column) do nothing;

is it possible to populate a primary key of a table based on a column of another table?

One Answer

Add your own answers!

Ask a Question