openGauss每日一练第15天

今日目标：查看表的相关信息

查看表定义

\d[S+] <表名> //describe table, view, sequence, or index，表有哪些约束

查看表的模式和所有者

\dt[S+] [PATTERN] list tables

information_schema

信息模式本身是一个名为information_schema的模式。这个模式自动存在于所有数据库中。信息模式由一组视图构成，它们包含定义在当前数据库中对象的信息。这个模式的拥有者是初始数据库用户，并且该用户自然地拥有这个模式上的所有特权，包括删除它的能力。

information_schema.schemata

The view schemata contains all schemas in the current database that are owned by a currently enabled role.

information_schema.tables

The view tables contains all tables and views defined in the current database. Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege).

系统表

PG_DATABASE

系统表存储关于可用数据库的信息。

PG_TABLESPACE

PG_TABLESPACE系统表存储表空间信息。

pg_class

PG_CLASS系统表存储数据库对象信息及其之间的关系

PG_CONSTRAINT

PG_CONSTRAINT系统表存储表上的检查约束、主键和唯一约束。

课后作业

1 创建表和约束

omm=# create table test(
omm(# id bigint,
omm(# name varchar(100) not null, 
omm(#age int default 20,
omm(# primary key (id)
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omm=# \dt
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | test | table | omm   | {orientation=row,compression=no}
(1 row)

omm=#
复制

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

\d <表名> //查看表定义

\d+ <表名> //

\dt <表名> //查看表的模式及所有者

\dt+ <表名> //查看表的模式及所有者

omm=# \d test
             Table "public.test"
 Column |          Type          | Modifiers  
--------+------------------------+------------
 id     | bigint                 | not null
 name   | character varying(100) | not null
 age    | integer                | default 20
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

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

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

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

select table_name from information_schema.tables where table_schema =<模式名>;

--查询public模式下有哪些表
omm=# select table_name from information_schema.tables where table_schema = 'public';
 table_name 
------------
 test
(1 row)

-- 查询db4ai模式下有哪些表
omm=# select table_name ,table_schema from information_schema.tables where table_schema = 'db4ai';
 table_name | table_schema 
------------+--------------
 snapshot   | db4ai
(1 row)

-- 查询cstore模式下有哪些表
omm=# select table_name ,table_schema from information_schema.tables where table_schema = 'cstore';
 table_name | table_schema 
------------+--------------
(0 rows)

omm=#

复制

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

select conname,connamespace,contype,conkey from pg_constraint where conrelid in (select oid from pg_class where relname=‘表名’);

pg_class部分字段

​ relname: 表、索引、视图等对象的名称。

or 使用一下命令

\d+ <表名>

omm=# select conname,connamespace,contype,conkey from pg_constraint where conrelid in (select oid from pg_class where relname='test');
(1 row)

omm=#   conname  | connamespace | contype | conkey 
-----------+--------------+---------+--------
 test_pkey |         2200 | p       | {1}

omm=#
复制

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

select * from information_schema.tables where table_name =<表名>;

omm=# select * from information_schema.tables where table_name ='test';
-[ RECORD 1 ]----------------+-----------
table_catalog                | omm
table_schema                 | public
table_name                   | test
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=# 
复制