Thread: Dealing with schema in psql utility?

Hello all.

In the psql utility, how can I:
1) get a list of the schema that have been created in the current
database
2) View the list of tables in a schema other than "public"?
3) Rename a schema (is there an "alter schema" statement)?

I can't seem to find any way to actually see a list of the schemas that
I've created. And when I create a table in another schema with "create
schemaname.tablename (...)" it doesn't show up in the list of tables
for the database (\d), even though the list has a "schema" column,
which I presumed meant that it would show all of the objects in all
schemas in the database (wrong).

--Dan

On Thu, 20 Feb 2003, Dan Delaney wrote:

> 1) get a list of the schema that have been created in the current
> database

select * from pg_namespace ;

> 2) View the list of tables in a schema other than "public"?

Fist get the oid of the schema that you want to get the table list for :

select oid from pg_namespace where nspname = 'schema_name';

then

select relname from pg_class where relnamespace = oid; ## above oid.

> 3) Rename a schema (is there an "alter schema" statement)?

You can do it as superuser

update pg_namespace set nspname='new_schema_name' where
nspname='schema_name';


     _/_/     _/_/    _/_/  Tariq Muhammad
    _/  _/  _/   _/ _/  _/  tariq@libertyrms.info
   _/   _/ _/_/_/  _/_/_/   v:416-646-3304 x 111
  _/   _/ _/   _/ _/  _/    c:416-455-0272
 _/_/_/  _/_/_/  _/  _/     p:416-381-1457
_________________________________________________
     Liberty Registry Management Services Co.

On Thursday, February 20, 2003, at 02:23 PM, Tariq Muhammad wrote:
> select * from pg_namespace ;
> Fist get the oid of the schema that you want to get the table list for
> :
> select oid from pg_namespace where nspname = 'schema_name';
> select relname from pg_class where relnamespace = oid; ## above oid.
> update pg_namespace set nspname='new_schema_name' where
> nspname='schema_name';

You've got to be kidding ;-)

Seriously though, isn't the purpose of schemas to make organization of
tables more convenient? It seems like dealing with schemas is a big
hassle. It'd be nice to have something like "\d", except for schemas,
to list the schemas that you've created, and an "alter schema"
statement to modify them.

--Dan

Tariq Muhammad <tmuhamma@libertyrms.com> writes:
> On Thu, 20 Feb 2003, Dan Delaney wrote:
>> 1) get a list of the schema that have been created in the current
>> database

> select * from pg_namespace ;

7.4 psql will also offer "\dn".  We didn't get around to making this
happen for 7.3.

>> 2) View the list of tables in a schema other than "public"?

Try "\dt myschema.*"

>> 3) Rename a schema (is there an "alter schema" statement)?

On the to-do list, but not done yet.  As Tariq says, an ALTER command
will serve for the moment.

            regards, tom lane