postgresql duplicate table names best practice

Question

My company has a handful of apps that we deploy in the websites we build. Recently a very old app needed to be included along side a newer app and there was a conflict w/ a duplicate table name needed to be used by both apps.

We are now in the process of updating an old app and there will be some DB updates. I'm curious what people consider best practice (or how do you do it) to help ensure these name collisions don't happen.

I've looked at schema's but not sure if thats the right path we want to take. As the documentation prescribes, I don't want to "wire" a particular schema name into an application and if I add schema's to the user search path how would it know which table I was referring to if two schema's have the same table name. although, maybe I'm reading to much into this.

Any insights or words of wisdom would be greatly appreciated!

Answer 1

Postgres has namespaces which is definately the most efficient way of handling that sort of thing :)

Just create a new namespace for the old database and you're done. The way most PHP/MySQL solutions solve it (specific prefix per app) is also an option, but not one I'd recommend.

[edit]Specifically, you can call your tables like this the_namespace.tablename besides changing the search_path. For the rest, the search_path will decide your namespace if you don't add the namespace.