search_path for schemas

Started by Ferruccio Zamunerabout 23 years ago2 messagesgeneral
Jump to latest
#1Ferruccio Zamuner
nonsolosoft@diff.org

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ferruccio Zamuner (#1)
Re: search_path for schemas

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