Home Add timestamp to all tables in Postgres schema from table names
Reply: 0

Add timestamp to all tables in Postgres schema from table names

user687
1#
user687 Published in July 23, 2018, 9:51 am

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" 
    FROM information_schema.tables 
    WHERE table_schema='public' ;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300587 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO