学习目标

学习查看表的相关信息

课程作业

1.创建表和约束

--创建表和约束
newdb1=# create table test(id bigint,name varchar(50) not null,age  int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
复制

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

newdb1=# \d+ test
                                 Table "testsm.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 newtbs1
Has OIDs: no
Options: orientation=row, compression=no
复制

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

newdb1=#   SELECT table_name FROM information_schema.tables WHERE table_schema='testsm';
 table_name
------------
 t1
 testt1
 testt2
 test
(4 rows)
复制

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

--可以看到约束是主键

newdb1=# \d test
             Table "testsm.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 newtbs1

newdb1=# select conname, connamespace, contype, conkey
newdb1-#   from pg_constraint
newdb1-#  where conrelid in (  select oid
newdb1(#                   from pg_class
newdb1(#                  where relname='test');
  conname  | connamespace | contype | conkey
-----------+--------------+---------+--------
 test_pkey |        24702 | p       | {1}
(1 row)
复制

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

newdb1=# select table_schema from information_schema.tables where table_name = 'test';
 table_schema
--------------
 testsm
(1 row)
复制