I have a schema foo_bar but also foo_x. During a particular session, is it possible to just set foo_bar to bar in PostgreSQL (8.4)?

  • 1
    Do you mean alter schema foo_bar rename to bar? Feb 10 '17 at 12:19
  • will that work only for the session ? or will it be permanent? Feb 10 '17 at 12:29
  • 1
    Lack of punctuation makes it somewhat difficult to understand what your question is exactly. Are you asking whether you can rename a schema so that it affects only the current session? Is that the issue you are trying to resolve?
    – Andriy M
    Feb 10 '17 at 12:49
  • yes. sorry about the lack of punctiation Feb 10 '17 at 13:07
  • Please note that psql is a client application to PostgreSQL (or Postgres for short). Also, 8.4 is very old, now out of support for years. Please consider upgrading to something newer.
    – dezso
    Feb 13 '17 at 15:42

I don't think you can. If you check the documentation on ALTER SCHEMA (PostgreSQL 8.4), there is not such a thing as ALTER SCHEMA TEMPORARILY or anything similar.

What you could actually do (per session), is change your search_path setting. Citing the documentation:

search_path (string)

This variable specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema component. When there are objects of identical names in different schemas, the one found first in the search path is used. An object that is not in any of the schemas in the search path can only be referenced by specifying its containing schema with a qualified (dotted) name.

Depending on your specific use-case (the need for a temporary change of schema name wasn't mentioned), this might be helpful.

  • This is the best idea, all he has to do is remove the schema qualification from everything or preproccess them and add it back with a macro. Feb 12 '17 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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