postgresql-查看表结构（命令和sql方式）、修改表结构、外键

关注

原创最新推荐文章于 2026-01-31 19:00:00 发布 5.1k 阅读 AI 写同款· GEO 优化 ›

声明：本PostgreSQl实用指南系列为刘兴(http://deepfuture.iteye.com/)原创，未经笔者授权，任何人和机构不能转载

SQL方式查看表结构(表名是student)
mydb=# SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a
.atttypmod AS lengthvar,a.attnotnull AS notnull
mydb-# FROM pg_class c,pg_attribute a,pg_type t
mydb-# WHERE c.relname = 'student' and a.attnum > 0 and a.attrelid = c.oid and a
.atttypid = t.oid
mydb-# ORDER BY a.attnum;
attnum | field | type   | length | lengthvar | notnull
--------+-------+---------+--------+-----------+---------
      1 | name | varchar |     -1 |        24 | f
      2 | age   | int4    |      4 |        -1 | f
      3 | city | int4    |      4 |        -1 | f
(3 rows)

修改表结构

mydb=# alter table student alter sex type varchar(5);
ALTER TABLE

mydb=# select * from citys
mydb-# ;
name | id
------+----
长沙 | 1
湛江 | 2
上海 | 3
(3 rows)

mydb=# select * from student;
    name    | age | city | sex
------------+-----+------+-----
deepfuture | 20 |    1 |
未来       | 20 |    2 |
张三       | 21 |    1 |
(3 rows)

增加主键

mydb=# alter table citys add primary key (id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "citys_pkey" f
or table "citys"
ALTER TABLE

增加外键
mydb=# alter table student add constraint cityfk foreign key (city) references c
itys(id);
ALTER TABLE

增加和修改表记录

mydb=# insert into student values ('王国',18,2);
INSERT 0 1
mydb=# insert into student values ('王国',18,3);
INSERT 0 1
mydb=# update student set name='干哈' where name='王国';
UPDATE

mydb=# update student set name='艾丝凡' where name='干哈' and city=3;
UPDATE 1
mydb=# select * from student;
    name    | age | city | sex
------------+-----+------+-----
deepfuture | 20 |    1 |
未来       | 20 |    2 |
张三       | 21 |    1 |
干哈       | 18 |    2 |
艾丝凡     | 18 |    3 |
(5 rows)

mydb=# select * from citys;
name | id
------+----
长沙 | 1
湛江 | 2
上海 | 3
(3 rows)

违反外键增加表记录
mydb=# insert into student values('萨芬',19,5);
ERROR: insert or update on table "student" violates foreign key constraint "cit
yfk"
DETAIL: Key (city)=(5) is not present in table "citys".
STATEMENT: insert into student values('萨芬',19,5);
ERROR: insert or update on table "student" violates foreign key constraint "cit
yfk"
DETAIL: Key (city)=(5) is not present in table "citys".

在外键允许下增加表记录
mydb=# insert into student values('萨芬',19,3);
INSERT 0 1

mydb=#