Thread: BUG #1803: Incomplete table list in psql

BUG #1803: Incomplete table list in psql

From
"DKnoto"
Date:
The following bug has been logged online:

Bug reference:      1803
Logged by:          DKnoto
Email address:      dknoto@wiml.waw.pl
PostgreSQL version: 8.0.3
Operating system:   Linux 2.6.11.10
Description:        Incomplete table list in psql
Details:

Hi,
I have database with two schemas: S1 and S2. In each schema exist table T1
but psql show only table in first schema listet in SEARCH_PATH.
Best regards
DKnoto.

Re: BUG #1803: Incomplete table list in psql

From
Michael Fuhr
Date:
On Wed, Aug 03, 2005 at 10:29:38AM +0100, DKnoto wrote:
> I have database with two schemas: S1 and S2. In each schema exist table T1
> but psql show only table in first schema listet in SEARCH_PATH.

Is this what you mean?

CREATE SCHEMA s1;
CREATE TABLE s1.t1 (x integer);

CREATE SCHEMA s2;
CREATE TABLE s2.t1 (y integer);
CREATE TABLE s2.t2 (z integer);

SET search_path TO s1, s2;

\dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 s1     | t1   | table | mfuhr
 s2     | t2   | table | mfuhr
(2 rows)

Note what the psql documentation says about the \d command and an
object's visibility:

  A pattern that contains an (unquoted) dot is interpreted as a schema
  name pattern followed by an object name pattern.  For example, \dt
  foo*.bar* displays all tables in schemas whose name starts with foo
  and whose table name starts with bar.  If no dot appears, then the
  pattern matches only objects that are visible in the current schema
  search path.

  Whenever the pattern parameter is omitted completely, the \d
  commands display all objects that are visible in the current schema
  search path.  To see all objects in the database, use the pattern *.*.

psql uses pg_table_is_visible() to determine tables' visibility,
and the documentation for that function says:

  A table is said to be visible if its containing schema is in the
  search path and no table of the same name appears earlier in the
  search path.  This is equivalent to the statement that the table can
  be referenced by name without explicit schema qualification.

--
Michael Fuhr