CODEWITHSUNDEEP
postgresqlgosqlx
Donald French
Donald French

Reputation: 31

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.

Upvotes: 2

Views: 120

Answers (2)

Zegarek
Zegarek

Reputation: 26467

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.

Upvotes: 1

astrica1
astrica1

Reputation: 1

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

SHOW search_path;

Upvotes: 0

Related Questions