1.创建表和约束
omm=# drop table if exists test;
omm=# create table test(id bigint,name varchar(50) not null,age int default 20,primary key(id));
2.使用\d tableNmae命令查看表的定义、模式和所有者
--在gsql中，使用\d tableNmae命令查看表的定义。
omm=# \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
omm=# \d+ test;
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
omm=# Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+------------+----------+--------------+-------------
id | bigint | not null | plain | |
omm=#
--在gsql中，使用\d tableNmae命令查看表的模式和所有者。
omm=# \dt+ test;
(1 row)
omm=# List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+---------+----------------------------------+-------------
public | test | table | omm | 0 bytes | {orientation=row,compression=no} |
3.查看某个模式下有哪些表
--查看public模式下的所有表名：
omm=# select table_name from information_schema.tables where table_schema='public';
table_name
------------
test
(1 row)
4.查看一个表下有哪些约束
--查看约束名称、约束类型
omm=# 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)
--或者使用gsql的元命令\d tableName很方便地查看一个表上有哪些约束
omm=# \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.查看一个表属于数据库的哪个模式
omm-# \x
omm=# select * from information_schema.tables where table_name='test';
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 |
omm=# -[ RECORD 1 ]----------------+-----------
table_catalog | omm
table_schema | public
table_name | test
table_type | BASE TABLE
