Relationship among database,schema, and table in PostgreSQL-Alibaba Cloud Developer Community

Logically, schema and table are all located under the database.

First, create a table in the postgres database (equivalent to a public schema):

[pgsql@localhost bin]$ ./psql -d postgres
psql (9.1.2)
Type "help" for help.

postgres=# createtable tab200(id integer);
CREATETABLE
postgres=# \q

then, create a table in the tester database (equivalent to a public schema):

[pgsql@localhost bin]$ ./psql -d tester
psql (9.1.2)
Type "help" for help.

tester=# createtable tab200(id integer);
CREATETABLE
tester=# \q

then, view it in another database:

[pgsql@localhost bin]$ ./psql
psql (9.1.2)
Type "help" for help.

pgsql=# SELECT*FROM information_schema.tables WHERE table_name='tab200';
 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_de
fined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+----------------------+--------
-------------------+--------------------------+------------------------+--------------------+----------+---------------
(0 rows)

pgsql=# \q

then, view the database in postgres and tester respectively:

[pgsql@localhost bin]$ ./psql -d tester
psql (9.1.2)
Type "help" for help.

tester=# SELECT*FROM information_schema.tables WHERE table_name='tab200';
 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_de
fined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+----------------------+--------
-------------------+--------------------------+------------------------+--------------------+----------+---------------
 tester        |public| tab200     | BASE TABLE|||||| YES                | NO       | 
(1 row)

tester=# \q[pgsql@localhost bin]$ ./psql -d postgres
psql (9.1.2)
Type "help" for help.

postgres=# SELECT*FROM information_schema.tables WHERE table_name='tab200';
 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_de
fined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+----------------------+--------
-------------------+--------------------------+------------------------+--------------------+----------+---------------
 postgres      |public| tab200     | BASE TABLE|||||| YES                | NO       | 
(1 row)
this article is transferred from brother Jian's data garden blog, original link: http://www.cnblogs.com/gaojian/p/3171301.html,如需转载请自行联系原作者
Selected, One-Stop Store for Enterprise Applications
Support various scenarios to meet companies' needs at different stages of development

Start Building Today with a Free Trial to 50+ Products

Learn and experience the power of Alibaba Cloud.

Sign Up Now