openGauss每日一练第15天 | openGauss逻辑结构：表管理3

1.创建表和约束

omm=# create table customer (
omm(# id integer,
omm(# name varchar(10) not null,
omm(# age integer default 20,
omm(# primary key (id)
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
CREATE TABLE

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

omm=# \d+ customer
                               Table "public.customer"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description 
--------+-----------------------+------------+----------+--------------+-------------
 id     | integer               | not null   | plain    |              | 
 name   | character varying(10) | not null   | extended |              | 
 age    | integer               | default 20 | plain    |              | 
Indexes:
    "customer_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
omm=# \dt+ customer 
                                      List of relations
 Schema |   Name   | Type  | Owner |  Size   |             Storage              | Description 
--------+----------+-------+-------+---------+----------------------------------+-------------
 public | customer | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(1 row)

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

## 查看public下的表omm=# select table_name from information_schema.tables where table_schema = 'public';
 table_name 
------------
 test
 customer
(2 rows)

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

omm=# select conname,connamespace,contype,conkey from pg_constraint where conrelid in ( select oid from pg_class where relname = 'customer');
    conname    | connamespace | contype | conkey 
---------------+--------------+---------+--------
 customer_pkey |         2200 | p       | {1}
(1 row)

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

omm=# select * from information_schema.tables where table_name = 'customer';
-[ RECORD 1 ]----------------+-----------
table_catalog                | omm
table_schema                 | public
table_name                   | customer
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=# \dt customer 
List of relations
-[ RECORD 1 ]-----------------------------
Schema  | public
Name    | customer
Type    | table
Owner   | omm
Storage | {orientation=row,compression=no}