本篇内容主要讲解“PostgreSQL中的删除列操作是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中的删除列操作是什么”吧!
创建数据表
[local:/data/run/pg12]:5120 pg12@testdb=# create table t_drop(id int); CREATE TABLE [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_drop select generate_series(1,10000000); INSERT 0 10000000 [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 346 MB (1 row)
新增列
[local:/data/run/pg12]:5120 pg12@testdb=# \timing on Timing is on. [local:/data/run/pg12]:5120 pg12@testdb=# ALTER TABLE t_drop ADD COLUMN c1 text DEFAULT md5( random()::text ); ALTER TABLE Time: 45769.146 ms (00:45.769) [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 651 MB (1 row) Time: 0.840 ms [local:/data/run/pg12]:5120 pg12@testdb=#
新增列后,占用空间达到了651MB.
删除列
[local:/data/run/pg12]:5120 pg12@testdb=# alter table t_drop drop c1; ALTER TABLE Time: 2.886 ms [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 651 MB (1 row) Time: 1.788 ms [local:/data/run/pg12]:5120 pg12@testdb=#
删除列,但空间没有释放.
数据字典
[local:/data/run/pg12]:5120 pg12@testdb=# \d pg_attribute Table "pg_catalog.pg_attribute" Column | Type | Collation | Nullable | Default ---------------+-----------+-----------+----------+--------- attrelid | oid | | not null | attname | name | | not null | atttypid | oid | | not null | attstattarget | integer | | not null | attlen | smallint | | not null | attnum | smallint | | not null | attndims | integer | | not null | attcacheoff | integer | | not null | atttypmod | integer | | not null | attbyval | boolean | | not null | attstorage | "char" | | not null | attalign | "char" | | not null | attnotnull | boolean | | not null | atthasdef | boolean | | not null | atthasmissing | boolean | | not null | attidentity | "char" | | not null | attgenerated | "char" | | not null | attisdropped | boolean | | not null | attislocal | boolean | | not null | attinhcount | integer | | not null | attcollation | oid | | not null | attacl | aclitem[] | | | attoptions | text[] | C | | attfdwoptions | text[] | C | | attmissingval | anyarray | | | Indexes: "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname) "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum) [local:/data/run/pg12]:5120 pg12@testdb=# select attrelid,attname,atttypid,attisdropped from pg_attribute where attrelid = 't_drop'::regclass; attrelid | attname | atttypid | attisdropped ----------+------------------------------+----------+-------------- 994249 | tableoid | 26 | f 994249 | cmax | 29 | f 994249 | xmax | 28 | f 994249 | cmin | 29 | f 994249 | xmin | 28 | f 994249 | ctid | 27 | f 994249 | id | 23 | f 994249 | ........pg.dropped.2........ | 0 | t (8 rows) Time: 0.896 ms [local:/data/run/pg12]:5120 pg12@testdb=#
查看数据字典,发现删除的c1列变为pg.dropped.2,逻辑标记为删除.
使用vacuum/vacuum full回收空间.
[local:/data/run/pg12]:5120 pg12@testdb=# vacuum t_drop; VACUUM Time: 2510.368 ms (00:02.510) [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 651 MB (1 row) Time: 0.718 ms [local:/data/run/pg12]:5120 pg12@testdb=# vacuum full t_drop; VACUUM Time: 7996.658 ms (00:07.997) [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 346 MB (1 row) Time: 1.258 ms [local:/data/run/pg12]:5120 pg12@testdb=#
但数据字典仍保留删除列的信息
[local:/data/run/pg12]:5120 pg12@testdb=# select attrelid,attname,atttypid,attisdropped from pg_attribute where attrelid = 't_drop'::regclass; attrelid | attname | atttypid | attisdropped ----------+------------------------------+----------+-------------- 994249 | tableoid | 26 | f 994249 | cmax | 29 | f 994249 | xmax | 28 | f 994249 | cmin | 29 | f 994249 | xmin | 28 | f 994249 | ctid | 27 | f 994249 | id | 23 | f 994249 | ........pg.dropped.2........ | 0 | t (8 rows) Time: 0.757 ms [local:/data/run/pg12]:5120 pg12@testdb=#
新增列,查看数据字典
[local:/data/run/pg12]:5120 pg12@testdb=# ALTER TABLE t_drop ADD COLUMN c1 text DEFAULT md5( random()::text ); ALTER TABLE Time: 24483.254 ms (00:24.483) [local:/data/run/pg12]:5120 pg12@testdb=# select attrelid,attname,atttypid,attisdropped from pg_attribute where attrelid = 't_drop'::regclass; attrelid | attname | atttypid | attisdropped ----------+------------------------------+----------+-------------- 994249 | tableoid | 26 | f 994249 | cmax | 29 | f 994249 | xmax | 28 | f 994249 | cmin | 29 | f 994249 | xmin | 28 | f 994249 | ctid | 27 | f 994249 | id | 23 | f 994249 | ........pg.dropped.2........ | 0 | t 994249 | c1 | 25 | f (9 rows) Time: 1.067 ms [local:/data/run/pg12]:5120 pg12@testdb=#
到此，相信大家对“PostgreSQL中的删除列操作是什么”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
