本篇内容主要讲解“PostgreSQL中的删除列操作是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中的删除列操作是什么”吧!

创建数据表

[local:/data/run/pg12]:5120pg12@testdb=#createtablet_drop(idint);CREATETABLE[local:/data/run/pg12]:5120pg12@testdb=#insertintot_dropselectgenerate_series(1,10000000);INSERT010000000[local:/data/run/pg12]:5120pg12@testdb=#[local:/data/run/pg12]:5120pg12@testdb=#SELECTpg_size_pretty(pg_relation_size('t_drop'));pg_size_pretty----------------346MB(1row)

新增列

[local:/data/run/pg12]:5120pg12@testdb=#\timingonTimingison.[local:/data/run/pg12]:5120pg12@testdb=#ALTERTABLEt_dropADDCOLUMNc1textDEFAULTmd5(random()::text);ALTERTABLETime:45769.146ms(00:45.769)[local:/data/run/pg12]:5120pg12@testdb=#SELECTpg_size_pretty(pg_relation_size('t_drop'));pg_size_pretty----------------651MB(1row)Time:0.840ms[local:/data/run/pg12]:5120pg12@testdb=#

新增列后,占用空间达到了651MB.

删除列

[local:/data/run/pg12]:5120pg12@testdb=#altertablet_dropdropc1;ALTERTABLETime:2.886ms[local:/data/run/pg12]:5120pg12@testdb=#SELECTpg_size_pretty(pg_relation_size('t_drop'));pg_size_pretty----------------651MB(1row)Time:1.788ms[local:/data/run/pg12]:5120pg12@testdb=#

删除列,但空间没有释放.

数据字典

[local:/data/run/pg12]:5120pg12@testdb=#\dpg_attributeTable"pg_catalog.pg_attribute"Column|Type|Collation|Nullable|Default---------------+-----------+-----------+----------+---------attrelid|oid||notnull|attname|name||notnull|atttypid|oid||notnull|attstattarget|integer||notnull|attlen|smallint||notnull|attnum|smallint||notnull|attndims|integer||notnull|attcacheoff|integer||notnull|atttypmod|integer||notnull|attbyval|boolean||notnull|attstorage|"char"||notnull|attalign|"char"||notnull|attnotnull|boolean||notnull|atthasdef|boolean||notnull|atthasmissing|boolean||notnull|attidentity|"char"||notnull|attgenerated|"char"||notnull|attisdropped|boolean||notnull|attislocal|boolean||notnull|attinhcount|integer||notnull|attcollation|oid||notnull|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]:5120pg12@testdb=#selectattrelid,attname,atttypid,attisdroppedfrompg_attributewhereattrelid='t_drop'::regclass;attrelid|attname|atttypid|attisdropped----------+------------------------------+----------+--------------994249|tableoid|26|f994249|cmax|29|f994249|xmax|28|f994249|cmin|29|f994249|xmin|28|f994249|ctid|27|f994249|id|23|f994249|........pg.dropped.2........|0|t(8rows)Time:0.896ms[local:/data/run/pg12]:5120pg12@testdb=#

查看数据字典,发现删除的c1列变为pg.dropped.2,逻辑标记为删除.
使用vacuum/vacuum full回收空间.

[local:/data/run/pg12]:5120pg12@testdb=#vacuumt_drop;VACUUMTime:2510.368ms(00:02.510)[local:/data/run/pg12]:5120pg12@testdb=#SELECTpg_size_pretty(pg_relation_size('t_drop'));pg_size_pretty----------------651MB(1row)Time:0.718ms[local:/data/run/pg12]:5120pg12@testdb=#vacuumfullt_drop;VACUUMTime:7996.658ms(00:07.997)[local:/data/run/pg12]:5120pg12@testdb=#SELECTpg_size_pretty(pg_relation_size('t_drop'));pg_size_pretty----------------346MB(1row)Time:1.258ms[local:/data/run/pg12]:5120pg12@testdb=#

但数据字典仍保留删除列的信息

[local:/data/run/pg12]:5120pg12@testdb=#selectattrelid,attname,atttypid,attisdroppedfrompg_attributewhereattrelid='t_drop'::regclass;attrelid|attname|atttypid|attisdropped----------+------------------------------+----------+--------------994249|tableoid|26|f994249|cmax|29|f994249|xmax|28|f994249|cmin|29|f994249|xmin|28|f994249|ctid|27|f994249|id|23|f994249|........pg.dropped.2........|0|t(8rows)Time:0.757ms[local:/data/run/pg12]:5120pg12@testdb=#

新增列,查看数据字典

[local:/data/run/pg12]:5120pg12@testdb=#ALTERTABLEt_dropADDCOLUMNc1textDEFAULTmd5(random()::text);ALTERTABLETime:24483.254ms(00:24.483)[local:/data/run/pg12]:5120pg12@testdb=#selectattrelid,attname,atttypid,attisdroppedfrompg_attributewhereattrelid='t_drop'::regclass;attrelid|attname|atttypid|attisdropped----------+------------------------------+----------+--------------994249|tableoid|26|f994249|cmax|29|f994249|xmax|28|f994249|cmin|29|f994249|xmin|28|f994249|ctid|27|f994249|id|23|f994249|........pg.dropped.2........|0|t994249|c1|25|f(9rows)Time:1.067ms[local:/data/run/pg12]:5120pg12@testdb=#

到此，相信大家对“PostgreSQL中的删除列操作是什么”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！