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 onTiming is on.[local:/data/run/pg12]:5120 pg12@testdb=# ALTER TABLE t_drop ADD COLUMN c1 text  DEFAULT md5( random()::text );ALTER TABLETime: 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 TABLETime: 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;VACUUMTime: 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;VACUUMTime: 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 TABLETime: 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中的删除列操作是什么"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！