0

My app has a list of an external db's tables and it needs to be regularly updated. How do I check if a table has been renamed? i.e. how to differentiate between a new table and an old table that's been renamed? Looking for sql/python/sqlalchemy solutions that'll work in as many engines as possible.

I'm aware of OID in postgres but as far as I could tell it isn't very reliable and there's no equivalent in mysql. I suppose I could do something with the create time and modify time but I'm trying to see if there's an easier way.

Edit: The app is a data visualisation tool to which external dbs connect. I do not have control over the design of dbs that connect to the app which is why I need to account for tables getting renamed.

Improve this question
6
  • It may help: stackoverflow.com/questions/29693448/…
    – yildizyasin
    Mar 18, 2021 at 6:14
  • 1
    If renaming tables is an issue in your project then you might want to stop doing that or at least reduce it to the absolute minimum.
    – Klaus D.
    Mar 18, 2021 at 6:14
  • 1
    Renaming tables is an absolute no can do! Looks like your database design is broken.
    – juergen d
    Mar 18, 2021 at 6:15
  • Like I said it's for external dbs i.e. client dbs. I do not do the design, my app simply connect to their db to visualise the data. I need to cover all bases.
    – Judy T Raj
    Mar 18, 2021 at 6:27
  • If your tables are getting renamed an your app isn't being shut down and restarted during the migration you're living pretty dangerously. Usually migrations that alter anything are closely coordinated so the various systems hanging off that database can be notified and updated accordingly in sync.
    – tadman
    Mar 18, 2021 at 6:29

1 Answer 1

Reset to default
2

There is no other way to establish the identity of a database table in PostgreSQL than through the OID (system column tableoid). Creation time is not stored in PostgreSQL.

Relying on the table OID is not only proprietary to PostgreSQL, but also somewhat unsafe, since OIDs can get reused. I recommend that you keep an inventory of your table modifications in a separate database table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.