PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How do I get the current postgres sql or sqlx schema search path?

I am do ing a search on a post gres table and getting no records returned. The matching records are in the table I am searching. There is another table in aother schema where the data is not in any record. I set the schema search path =, howeve I do not know how to check what the current one is.

How do I determne the current schem search path?

Get the current schema search path in a postgress DB.

Tags:

2

Answers


  1. 0

    execute the following SQL query to determine the current schema search path in a PostgresDB:

    SHOW search_path;
    Login or Signup to reply.
  2. 0

    The current_schemas() session information function can give you the full context:

    current_schemas ( include_implicit boolean ) → name[]

    Returns an array of the names of all schemas presently in the effective search path, in their priority order. (Items in the current search_path setting that do not correspond to existing, searchable schemas are omitted.) If the Boolean argument is true, then implicitly-searched system schemas such as pg_catalog are included in the result.

    select current_schemas(/*include_implicit*/true);

    Better yet, you can ask directly whether the table you think is visible, is in fact visible to you, using the pg_table_is_visible() function:
    demo at db<>fiddle

    select pg_table_is_visible('schema1.the_table'::regclass);
    1. If you’re using a pool or multiple connections, the set search_path=another_schema will only affect the one session it happened to execute in. If you’re sending statements straight to the pool, the setting change and the query expecting the setting changed might end up in different connections.
    2. In autocommit mode that’s default for most client apps/libs, if the setting’s changed using set local search_path=schema1; or set_config('search_path', 'schema1', true), you’ll never see its effect because the transaction it was supposed to apply to, is finished immediately.
    3. If you alter the setting on system, database or role level, it becomes the new default and does not affect any ongoing session, including the one that issued the command.
    4. Lower level overrides higher meaning that setting it as role-level default overrides database and system-level defaults.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search