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