第15天 | openGauss逻辑结构：表管理3

第15天 | openGauss逻辑结构：表管理3
1.创建表和约束

drop table if exists test;

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

omm=# drop table if exists test;
NOTICE:  table "test" does not exist, skipping
DROP TABLE
omm=# 
omm=# create table test(id bigint primary key ,name varchar(20) not null);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE

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

omm=# \d test
            Table "public.test"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(20) | not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# \d+ test
                                Table "public.test"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | bigint                | not null  | plain    |              | 
 name   | character varying(20) | not null  | extended |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# \dt+ test
                                    List of relations
 Schema | Name | Type  | Owner |  Size   |             Storage              | Description 
--------+------+-------+-------+---------+----------------------------------+-------------
 public | test | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(1 row)

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

\d+ pg_tables

omm=# \d+ pg_tables
                         View "pg_catalog.pg_tables"
    Column     |           Type           | Modifiers | Storage | Description 
---------------+--------------------------+-----------+---------+-------------
 schemaname    | name                     |           | plain   | 
 tablename     | name                     |           | plain   | 
 tableowner    | name                     |           | plain   | 
 tablespace    | name                     |           | plain   | 
 hasindexes    | boolean                  |           | plain   | 
 hasrules      | boolean                  |           | plain   | 
 hastriggers   | boolean                  |           | plain   | 
 tablecreator  | name                     |           | plain   | 
 created       | timestamp with time zone |           | plain   | 
 last_ddl_time | timestamp with time zone |           | plain   | 
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, 
    pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, 
    c.relhasindex AS hasindexes, c.relhasrules AS hasrules, 
    c.relhastriggers AS hastriggers, 
        CASE
            WHEN pg_check_authid(po.creator) THEN pg_get_userbyid(po.creator)
            ELSE NULL::name
        END AS tablecreator, 
    po.ctime AS created, po.mtime AS last_ddl_time
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
   LEFT JOIN pg_object po ON po.object_oid = c.oid AND po.object_type = 'r'::"char"
  WHERE c.relkind = 'r'::"char";

omm=# select tablename,tableowner from pg_tables where schemaname='public';
   tablename   | tableowner 
---------------+------------
 tab_t1        | omm
 tab_newtest1  | omm
 tab_newtest2  | omm
 tab_newtest3  | omm
 tab_newtest4  | omm
 tab_newtest41 | omm
 tab_newtest42 | omm
 test          | omm
(8 rows)

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

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

omm=# select conname,contype from pg_constraint where conrelid in(select oid from pg_class where relname='test');
  conname  | contype 
-----------+---------
 test_pkey | p
(1 row)

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

 select schemaname from pg_tables where tablename='test';

omm=# select schemaname from pg_tables where tablename='test';
 schemaname 
------------
 public
(1 row)