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?

2 Answers 11

up vote 23 down vote accepted

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
upvote
  flag
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? – thyandrecardoso
upvote
  flag
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! – thyandrecardoso
1 upvote
  flag
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; – Alex Howansky
1 upvote
  flag
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. – araqnid

What is the search path?

Per documentation:

[...] 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, and the “current 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. Per documentation:

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 it?

You have various options to actually 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 shipped default for this setting is:

    search_path = "$user",public
    

    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.

  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 the role only in a given 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 at any point in your 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 AS
$func$
BEGIN
   -- do stuff
END
$func$ LANGUAGE plpgsql SECURITY DEFINER
       SET search_path=blarg,public,pg_temp;

Higher number in my 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;

Per documentation:

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.

upvote
  flag
It was this I was talking about on the comments above :) Thanks for showing me these options ;) – thyandrecardoso
1 upvote
  flag
+1 for the ALTER DATABASE, did not know about that, very useful :) – Tommaso Barbugli
1 upvote
  flag
Thanks, @Erwin. After reading your anwser, I found that RESET search_path; did the trick for me. (#6?) – Christian Campbell
upvote
  flag
thanks @ErwinBrandstetter but what is blarg? – BKSpurgeon
1 upvote
  flag
@BKSpurgeon: blarg is just the ugly brother of foo and bar. A random name. – Erwin Brandstetter
upvote
  flag
remember that setting the search path during a session might not compatible with the most desiderable pgBouncer strategies – John Smith

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