Postgres: Preventing objects with same name

I just spent one hour debugging a query which had previously worked but suddenly created strange results. The cause of this bug was that I have a table in one schema which I reference in the query. Someone had, however, added a view in the current schema which has the same name as the table in the other schema. This caused the new view in the current schema to "take over" the reference in the query causing it to reference the view instead of the table. Obviously this caused me to start looking in the wrong place for this bug and getting completely confused. My question is therefore, how can I prevent this from happening? For example by enforcing that all views an tables in the database needs unique names. By enforcing this, I mean that it will cause and error if one attempts to create two objects with identical names.

Because I am using some external tools, I have set up postgres to search for tables in multiple schemas (I have forgot how I did this), which is probably partly to blame for this mistake, but I cannot change this.

That's like asking Microsoft or GNU.org to remove PATH from the command line. Ain't gonna happen.

Use fully qualified names or always run ALTER ROLE SET search_path TO a,b,c; at the beginning of your session. I'm not sure if you can run this with psqlrc but you could try.

You can view your search_path with show search_path

You could write a cron or pgagent job to run often and check information_schema for views and tables with the same name

StackExchange Database Administrators Q#138744, answer score: 2