openGauss每日一练第15天

openGauss的逻辑架构 表管理

1.创建表和约束

omm=# create table testtab01 (id bigint , name varchar(50) not null ,age int default 20 ,primary key (id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "testtab01_pkey" for table "testtab01"
CREATE TABLE

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

omm=# \d+ testtab01
                              Table "public.testtab01"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description 
--------+-----------------------+------------+----------+--------------+-------------
 id     | bigint                | not null   | plain    |              | 
 name   | character varying(50) | not null   | extended |              | 
 age    | integer               | default 20 | plain    |              | 
Indexes:
    "testtab01_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 
------------
 testtab01
(1 row)

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

omm=# select conname ,connamespace,contype from pg_constraint where conrelid in (select oid from pg_class where relname='testtab01');
-[ RECORD 1 ]+---------------
conname      | testtab01_pkey
connamespace | 2200
contype      | p

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

omm=# \x
Expanded display is on.
omm=# select * from information_schema.tables where table_name='testtab01';
-[ RECORD 1 ]----------------+-----------
table_catalog                | omm
table_schema                 | public
table_name                   | testtab01
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                |