I can concateante two columns using the following code:
engine = create_engine(f'postgresql+psycopg2://user:password@host/db')
result = engine.execute('SELECT concat(col_1, col_2) AS uid FROM db_table;')
and I can alter the table to create a new column:
engine.execute('ALTER TABLE db_table
ADD COLUMN concat_1_2 VARCHAR NOT NULL;')
But how can I insert the result query into the table in an efficient way (very large number of rows)?
if you need only an insert you could use an insert select
insert into db_table (concat_1_2 )
SELECT concat(col_1, col_2)
FROM db_table
otherwise just use an update
UPDATE db_table
SET concat_1_2 = concat(col_1, col_2)
If you want to replace columns col_1 and col_2 with new one col_1_2, you can alter the table in this way:
alter table db_table alter col_1 type varchar using concat(col_1, col_2);
alter table db_table rename col_1 to col_1_2;
alter table db_table drop col_2;
