Thread: search_path for schemas

Hi,

there is something wrong into use of search_path and psql "\d" command.

Look:

bash-2.05a$ psql try
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
[...]
try=> create schema first;
CREATE SCHEMA
try=> create schema second;
CREATE SCHEMA
try=> create schema third;
CREATE SCHEMA
try=> create table first.simple ( id int4 primary key, note text);
CREATE TABLE
try=> create table second.simple ( id int4 primary key, note text);
CREATE TABLE
try=> create table third.simple ( id int4 primary key, note text);
CREATE TABLE
try=> SHOW search_path;
 search_path
--------------
 $user,public
(1 row)

try=> SET search_path to 'first','second','third';
SET
try=> \d
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 first  | simple | table | fer
(1 row)
try=> SHOW search_path;
      search_path
------------------------
 first, "second", third
(1 row)


I think to see following result there instead:
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 first  | simple | table | fer
 second | simple | table | fer
 third  | simple | table | fer


Where I've failed?


Thank you in advance,             \fer

Ferruccio Zamuner <nonsolosoft@diff.org> writes:
> there is something wrong into use of search_path and psql "\d" command.

No, there isn't...

> I think to see following result there instead:
>         List of relations
>  Schema |  Name  | Type  | Owner
> --------+--------+-------+-------
>  first  | simple | table | fer
>  second | simple | table | fer
>  third  | simple | table | fer

You will not see that because first.simple is blocking your view of the
similarly-named tables behind it in the search path --- that is, if you
did "select * from simple" here, you'd get first.simple, not
second.simple nor third.simple.  The only way to get at the latter two
is to explicitly give the schema name, which means they are effectively
not in your search path.

Plain \d is defined to show you only tables that are visible in your
search path, and not clutter the display with those that aren't.
You can use something like \d *.* if you want to see all tables without
regard to search path.

            regards, tom lane