How to update multiple tables at the same time in Postgresql?

Question

So I have many tables in a db and I want to add two new columns for them. For example, I have the columns "created_at" and "modified_at" and I want to create the columns "client_created_at" and "client_modified_at" and at the same time populate these new columns with the values of "created_at" and "modified_at" of each table.

I imagine and have tried something like this:

ALTER TABLE patients, folders, auscultations, auscultations_notes, folder_ausc_association
    ADD COLUMN client_created_at bigint, client_modified_at bigint;

UPDATE patients, folders, auscultations, auscultations_notes, folder_ausc_association
    SET client_created_at = created_at, client_modified_at = modified_at

I'm not sure about how to structure it, any help would be appreciated!

Answer 1

In addition to the solution from Laurenz Albe, you could create an anonymous code block to do this job. Such a query can be very handy when you have many tables and don't want to create one statement per table.

DO $$
DECLARE
  row record;
BEGIN
    FOR row IN SELECT * FROM pg_tables WHERE schemaname = 'public' 
    LOOP
      EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' ADD COLUMN client_created_at bigint, ADD COLUMN client_modified_at bigint;';
      EXECUTE 'UPDATE ' || quote_ident(row.tablename) || ' SET client_created_at = created_at, client_modified_at = modified_at;';
    END LOOP;
END;
$$;

Note: This code block adds the columns you want into all tables in the schema public - use it with care! You can adapt it to the tables you need by changing this query in the block:

SELECT * FROM pg_tables WHERE schemaname = 'public'