openGauss每日一练第15天 | 查看表信息

打卡第十五天，表信息查看

• 使用\d \dt+ tableNmae命令查看表的定义、模式和所有者

• 查看public模式下的所有表名
  

SELECT table_name FROM information_schema.tables WHERE table_schema='public';

• 查看表约束名称、约束类型

select conname, connamespace, contype, conkey     
  from pg_constraint  where conrelid='test'::regclass;

或用\d 可查看

• 查看一个表属于数据库的哪个模式

 SELECT * FROM information_schema.tables WHERE table_name='test';

课程作业

1.创建表和约束

omm=# create table tt (id int primary key,col char(20) not null);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tt_pkey" for table "tt"
CREATE TABLE

2.使用\d tableNmae命令查看表的定义、模式和所有者

omm=# \dt+ tt
                                    List of relations
 Schema | Name | Type  | Owner |  Size   |             Storage              | Description 
--------+------+-------+-------+---------+----------------------------------+-------------
 public | tt   | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(1 row)

omm=# \d+ tt
                             Table "public.tt"
 Column |     Type      | Modifiers | Storage  | Stats target | Description 
--------+---------------+-----------+----------+--------------+-------------
 id     | integer       | not null  | plain    |              | 
 col    | character(20) | not null  | extended |              | 
Indexes:
    "tt_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

3.查看某个模式下有哪些表

omm=# SELECT table_name FROM information_schema.tables WHERE table_schema='public';
 table_name 
------------
 test
 tt
(2 rows)

4.查看一个表下有哪些约束

omm=# select conname, connamespace, contype, conkey     
omm-#   from pg_constraint  where conrelid='tt'::regclass';
 conname | connamespace | contype | conkey 
---------+--------------+---------+--------
 tt_pkey |         2200 | p       | {1}
(1 row)

omm=# 

5.查看一个表属于数据库的哪个模式

omm=# \x
Expanded display is on.
omm=#  SELECT * FROM information_schema.tables WHERE table_name='tt';
-[ RECORD 1 ]----------------+-----------
table_catalog                | omm
table_schema                 | public
table_name                   | tt
table_type                   | BASE TABLE
self_referencing_column_name | 
reference_generation         | 
user_defined_type_catalog    | 
user_defined_type_schema     | 
user_defined_type_name       | 
is_insertable_into           | YES
is_typed                     | NO
commit_action                | 

omm=#