openGauss每日一练第15天 | 表的相关信息

学习查看表的相关信息

1.创建表和约束

drop table if exists tab1;
create table tab1(
c1 number(20),
c2 varchar(50) not null,
c3  int default 20,
primary key(c1)
     );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tab1_pkey" for table "tab1"
CREATE TABLE  

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

\d+ tab1
\dt+ tab1;

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

SELECT table_name FROM information_schema.tables WHERE table_schema='public';
  
-[ RECORD 1 ]----
table_name | test
-[ RECORD 2 ]----
table_name | tab1

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

select conname, connamespace, contype, conkey     
  from pg_constraint 
 where conrelid in (  select oid
                  from pg_class
                 where relname='tab1');

-[ RECORD 1 ]+----------
conname      | tab1_pkey
connamespace | 2200
contype      | p
conkey       | {1} 

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

\x 
SELECT * FROM information_schema.tables WHERE table_name='tab1';