学习目标
学习查看表的相关信息
课程学习
1.测试环境准备
–首先创建一张测试表。
drop table if exists test;
create table test(
id bigint,
name varchar(50) not null,
age int default 20,
primary key(id)
);
2.在gsql中查看表的定义、模式和所有者
–在gsql中，使用\d tableNmae命令查看表的定义。
\d+ test;
–在gsql中，使用\d tableNmae命令查看表的模式和所有者。
\dt+ test;
3.查看某个模式下有哪些表
–查看public模式下的所有表名：
SELECT table_name FROM information_schema.tables WHERE table_schema=‘public’;
4.查看一个表下有哪些约束
–查看约束名称、约束类型
select conname, connamespace, contype, conkey
from pg_constraint
where conrelid in ( select oid
from pg_class
where relname=‘test’);
–或者使用gsql的元命令\d tableName很方便地查看一个表上有哪些约束
\d test
5.查看一个表属于数据库的哪个模式
\x
SELECT * FROM information_schema.tables WHERE table_name=‘test’;
课程作业
1.创建表和约束
drop table if exists test;
create table test1(
id bigint,
name varchar(50) not null,
age int default 20,
primary key(id)
);
omm=# drop table if exists test;
NOTICE: table “test” does not exist, skipping
DROP TABLE
omm=# create table test1(
omm(# id bigint,
omm(# name varchar(50) not null,
omm(# age int default 20,
omm(# primary key(id)
omm(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index “test1_pkey” for table “test1”
CREATE TABLE
2.使用\d tableNmae命令查看表的定义、模式和所有者
使用\d tableNmae命令查看表的定义
\d+ test1;
omm=# \d+ test1
Table “public.test1”
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:
“test1_pkey” PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
使用\d tableNmae命令查看表的模式和所有者
\dt+ test1;
omm=# \dt+ test1
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------±------±------±------±--------±---------------------------------±------------
public | test1 | table | omm | 0 bytes | {orientation=row,compression=no} |
(1 row)
omm=# SELECT table_name FROM information_schema.tables WHERE table_schema=‘public’;
table_name
test1
(1 row)
3.查看某个模式下有哪些表
SELECT table_name FROM information_schema.tables WHERE table_schema=‘public’;
omm=# select conname, connamespace, contype, conkey
omm-# from pg_constraint
omm-# where conrelid in ( select oid
omm(# from pg_class
omm(# where relname=‘test1’);
conname | connamespace | contype | conkey
------------±-------------±--------±-------
test1_pkey | 2200 | p | {1}
(1 row)
4.查看一个表下有哪些约束
select conname, connamespace, contype, conkey
from pg_constraint
where conrelid in ( select oid
from pg_class
where relname=‘test1’);
\d test1
omm=# select conname, connamespace, contype, conkey
omm-# from pg_constraint
omm-# where conrelid in ( select oid
omm(# from pg_class
omm(# where relname=‘test1’);
conname | connamespace | contype | conkey
------------±-------------±--------±-------
test1_pkey | 2200 | p | {1}
(1 row)
omm=#
omm=# \d test1
Table “public.test1”
Column | Type | Modifiers
--------±----------------------±-----------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
“test1_pkey” PRIMARY KEY, btree (id) TABLESPACE pg_default
5.查看一个表属于数据库的哪个模式
\x
SELECT * FROM information_schema.tables WHERE table_name=‘test1’;
omm=# \x
Expanded display is on.
omm=# SELECT * FROM information_schema.tables WHERE table_name=‘test1’;
-[ RECORD 1 ]----------------±----------
table_catalog | omm
table_schema | public
table_name | test1
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 |
