I don't consider myself a database admin guru, so when my goal has an underlying task which involves database work, I usually google fu the task. The search results bring me to some StackOverflow thread that has the solution. Recently, I was confronted with an interesting task to which my google fu did not help me.
The task was to merge many database tables into a single table. I was not able to find an example on the internet so I was on my own. After much trial and error I managed to script a and SQL query to merge all the database tables into a single table.
Without further a do, here is the SQL that I used to for the task :
DO $$ DECLARE rec record; BEGIN FOR rec IN SELECT table_schema, table_name FROM information_schema.columns LOOP EXECUTE format('INSERT INTO merged (SELECT * FROM %I.%I);', rec.table_schema, rec.table_name); END LOOP; END; $$