openGauss每日一练第15天 | 学习心得体会

1.创建表和约束

drop table if exists test;

create table test(
	id 		bigint,
	name 	varchar(50) not null,
	age  	int default 20,
	primary key(id)
    );

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

--查看表的定义
\d+ test
                                 Table "public.test"
 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:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
--查看表的模式和所有者
\dt+ test;
 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';
 table_name 
------------
 test001
 test002
 test003
 invoice
 new01
 new02
 test
(7 rows)

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

--查看约束名称、约束类型
select conname, connamespace, contype, conkey     
  from pg_constraint 
 where conrelid in (  select oid
					  from pg_class
                      where relname='test');
  conname  | connamespace | contype | conkey 
-----------+--------------+---------+--------
 test_pkey |         2200 | p       | {1}
(1 row)

--\d table_name查看约束名称、约束类型  
\d test
             Table "public.test"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 20
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

\x
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                |