Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p></p> <h2>What is the search path?</h2> <p><a href="http://www.postgresql.org/docs/current/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH">Per documentation:</a></p> <blockquote> <p>[...] tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is <strong>a list of schemas to look in</strong>.</p> </blockquote> <p>Bold emphasis mine. This explains <strong>identifier resolution</strong>, and the <strong>“current schema”</strong> is, <a href="http://www.postgresql.org/docs/current/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH">per documentation:</a></p> <blockquote> <p>The <strong>first schema named in the search path</strong> is called the <strong>current schema</strong>. Aside from being the first schema searched, it is also the schema in which new tables will be created if the <code>CREATE TABLE</code> command does not specify a schema name.</p> </blockquote> <p>Bold emphasis mine. The system schemas <strong><code>pg_temp</code></strong> (schema for temporary objects of the current session) and <strong><code>pg_catalog</code></strong> are automatically part of the search path and searched <em>first</em>, in this order. <a href="http://www.postgresql.org/docs/current/interactive/ddl-schemas.html#DDL-SCHEMAS-CATALOG">Per documentation:</a></p> <blockquote> <p><code>pg_catalog</code> is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched <strong>before</strong> searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place <code>pg_catalog</code> at the end of your search path if you prefer to have user-defined names override built-in names.</p> </blockquote> <p>Bold emphasis as per original. And <code>pg_temp</code> comes before that, unless it's put into a different position.</p> <h2>How to set it?</h2> <p>You have various options to actually set the runtime variable <a href="http://www.postgresql.org/docs/current/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH"><strong><code>search_path</code></strong></a>. </p> <ol> <li><p>Set a <strong>cluster</strong>-wide default for all roles in all databases in <a href="http://www.postgresql.org/docs/current/interactive/runtime-config-client.html"><code>postgresql.conf</code></a> (and reload). Careful with that!</p> <pre class="lang-sql prettyprint-override"><code>search_path = 'blarg,public' </code></pre> <p>The <a href="http://www.postgresql.org/docs/current/interactive/runtime-config-client.html#GUC-SEARCH-PATH">shipped default for this setting</a> is:</p> <pre class="lang-sql prettyprint-override"><code>search_path = "$user",public </code></pre> <blockquote> <p>The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored.</p> </blockquote></li> <li><p>Set it as default for one <strong>database</strong>:</p> <pre class="lang-sql prettyprint-override"><code>ALTER DATABASE test SET search_path = blarg,public; </code></pre></li> <li><p>Set it as default for the <strong>role</strong> you connect with (effective cluster-wide):</p> <pre class="lang-sql prettyprint-override"><code>ALTER ROLE foo SET search_path = blarg,public; </code></pre></li> <li><p>Or even (often best!) as default for the <strong>role only in a given database</strong>:</p> <pre class="lang-sql prettyprint-override"><code>ALTER ROLE foo IN DATABASE test SET search_path = blarg,public; </code></pre></li> <li><p>Write the command at the top of your script (Or execute it at any point in your <strong>session</strong>:</p> <pre class="lang-sql prettyprint-override"><code>SET search_path = blarg,public; </code></pre></li> <li><p>Set a specific <code>search_path</code> for the <strong>scope of a function</strong> (to be safe from malicious users with sufficient privileges). Read about <a href="http://www.postgresql.org/docs/current/interactive/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY">Writing <code>SECURITY DEFINER</code> Functions Safely</a> in the manual.</p></li> </ol> <pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION foo() RETURNS void AS $func$ BEGIN -- do stuff END $func$ LANGUAGE plpgsql SECURITY DEFINER <b>SET search_path=blarg,public,pg_temp</b>;</code></pre> <p></p></p> <p>Higher number in my list trumps lower number.<br> The <a href="http://www.postgresql.org/docs/current/interactive/config-setting.html">manual has <em>even more ways</em></a>, like setting environment variables or using command-line options.</p> <p>To see the current setting:</p> <pre class="lang-sql prettyprint-override"><code>SHOW search_path; </code></pre> <p>To <a href="http://www.postgresql.org/docs/current/interactive/sql-reset.html">reset</a> it:</p> <pre class="lang-sql prettyprint-override"><code>RESET search_path; </code></pre> <p><a href="http://www.postgresql.org/docs/current/interactive/sql-reset.html">Per documentation</a>:</p> <blockquote> <p>The default value is defined as the value that the parameter would have had, if no <code>SET</code> had ever been issued for it in the current session.</p> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. POHow does the search_path influence identifier resolution and the "current schema"
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErwin Brandstetter
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt was this I was talking about on the comments above :) Thanks for showing me these options ;)
      singulars
      1. PO
      1. USthyandrecardoso
    2. CO+1 for the ALTER DATABASE, did not know about that, very useful :)
      singulars
      1. PO
      1. USTommaso Barbugli
    3. COThanks, @Erwin. After reading your anwser, I found that `RESET search_path;` did the trick for me. (#6?)
      singulars
      1. PO
      1. USChristian Campbell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload