How can I add a new field called
date to all tables in a schema called "public"? Is it possible to do that in one go?
Here is what I want to do. I have a date encoded in the table name. I want to extract the date and use it as a timestamp in the new field.
Now I can do the date extraction per table, but I don't know how I can add an extra column for timestamps and how to update it with the extracted timestamp.
This is how I extract the timestamp string from the table names:
SELECT table_name, substring(table_name,4,8) "foo_dates"
WHERE table_schema='public' ;