How does the search_path influence identifier resolution and the "current schema"

Question

Is it possible to define in which schema new tables get created by default? (Referred by "unqualified table names".)

I've seen some details about using the "search path" in Postgres, but I think it only works while retrieving data, not creating.

I have a bunch of SQL scripts, which create many tables. Instead of modifying the scripts, I want to set the database create tables in a specific schema by default - when they have unqualified names.

Is this possible?

Bernoulli

Answer 1

Search path is indeed what you want:

% create schema blarg;
% set search_path to blarg;
% create table foo (id int);
% \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 blarg  | foo  | table | pgsql

Phototypography

Yup, you were right... but you already knew that :). But I thought it did not work because I've tried it before, but on different sessions: on one I did the set search_path, on the other I created the tables. I thought the "set search_path" thing would stick for a given database. Can I make it stick? – Bernoulli

Btw, to test it again, I simply put the "set search_path" on top of the sql script, instead of doing the two things separately... and thank you! – Bernoulli

You should be able to set the search_path parameter in your config file, or make it permanent for a user via: ALTER USER <user> SET search_path = whatever; – Phototypography

You can also make it the default for a database: ALTER DATABASE db SET search_path = ... or even for a particular user/database combination (on 9.1): ALTER ROLE user IN DATABASE db SET search_path = ..., but if you use these settings too much they can cause confusion, and note that it's not clear when they get dumped. – Zeeba

Answer 2

What is the schema search path search_path?

The manual:

[...] 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 a list of schemas to look in.

Bold emphasis mine. This explains identifier resolution.

The “current schema” (or “default schema”) is, per documentation:

The first schema named in the search path is called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.

Bold emphasis mine. The system schemas pg_temp (schema for temporary objects of the current session) and pg_catalog are automatically part of the search path and searched first, in this order. The manual:

pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

Bold emphasis as per original. And pg_temp comes before that, unless it's put into a different position.

How to set and reset it?

There are various ways to set the runtime variable search_path.

1. Set a cluster-wide default for all roles in all databases in postgresql.conf (and reload). Careful with that!

    search_path = 'blarg,public'
   

   The factory default for this setting is:

    search_path = "$user",public
   

   Note:

   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.

   And:

   If one of the list items is the special name $user, then the schema having the name returned by CURRENT_USER is substituted, if there is such a schema and the user has USAGE permission for it. (If not, $user is ignored.)

2. Set it as default for one database:

    ALTER DATABASE test SET search_path = blarg,public;
   

3. Set it as default for the role you connect with (effective cluster-wide):

    ALTER ROLE foo SET search_path = blarg,public;
   

4. Or even (often best!) as default for a role in a database:

    ALTER ROLE foo IN DATABASE test SET search_path = blarg,public;
   

5. Write the command at the top of your script. Or execute it in your DB session:

    SET search_path = blarg,public;
   

6. Set a specific search_path for the scope of a function (to be safe from malicious users with sufficient privileges). Read about Writing SECURITY DEFINER Functions Safely in the manual.

   CREATE FUNCTION foo()
     RETURNS void
     LANGUAGE plpgsql SECURITY DEFINER SET search_path=blarg,public,pg_temp
     AS
   $func$
   BEGIN
      -- do stuff
   END
   $func$;

Higher number in the list trumps lower number.
The manual has even more ways, like setting environment variables or using command-line options.

To see the current setting:

SHOW search_path;

To reset it:

RESET search_path;

The manual:

The default value is defined as the value that the parameter would have had, if no SET had ever been issued for it in the current session.

How to know what this will reset to? Ask the system view pg_settings:

SELECT reset_val FROM pg_settings WHERE name = 'search_path';

reset_val text
Value that RESET would reset the parameter to in the current session

Or save the current state (which may differ from the reset value) before you change it in the first place, and SET it back to that state (rather than RESET). There is the convenient function set_config() taking a variable, as opposed to SET. Example PL/pgSQL code block:

DO
$do$
DECLARE
   _org_search_path text := current_setting('search_path');
BEGIN
   SET search_path = public, pg_temp;
   -- do stuff
   PERFORM set_config('search_path', _org_search_path, true);
END;
$do$

Or use SET LOCAL. But that lasts till the end of the current transaction, which may be past the end of a function. Subtle difference!

Brag

It was this I was talking about on the comments above :) Thanks for showing me these options ;) – Bernoulli

+1 for the ALTER DATABASE, did not know about that, very useful :) – Soapy

Thanks, @Erwin. After reading your anwser, I found that RESET search_path; did the trick for me. (#6?) – Queenqueena

@BKSpurgeon: blarg is just the ugly brother of foo and bar. A random name. – Brag

remember that setting the search path during a session might not compatible with the most desiderable pgBouncer strategies – Sycamine

How can one predict what RESET search_path; will reset to? The manual seems ambiguous on that point - presumably to number 1 in the list postgresql.conf - but if that’s the case why doesn’t the manual say so? – Devisal

@Matt: I added a bit on the topic of resetting. – Brag