Add new column in PostgreSQL with a subquery and set a NOT NULL constraint

query.sql

BEGIN;

ALTER TABLE table1 ADD COLUMN exp_id text;

UPDATE table1
    SET exp_id = temp_query.exp_id
FROM (
    SELECT t2.exp_id FROM table2 t2 JOIN table1 t1 on t2.gid = t1.gid
) AS temp_query;

ALTER TABLE table1 ALTER COLUMN exp_id SET NOT NULL;


COMMIT;