本篇内容主要讲解“PostgreSQL中的删除列操作是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL...
本篇内容主要讲解“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中的删除列操作是什么”有了更深的了解，不妨来实际操作一番吧！这里是捷杰建站网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
服务热线 19970861797
