What is the delete column operation in PostgreSQL

This article mainly explains "what is the operation of deleting columns in PostgreSQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what is the delete column operation in PostgreSQL"!

Create a datasheet

[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 (1meme 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 ('tweedrop')) Pg_size_pretty-346 MB (1 row)

New column

[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 (45769.146 pg12@testdb=# SELECT pg_size_pretty) [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT pg_size_pretty (pg_relation_size ('tweedrop')) Pg_size_pretty-651MB (1 row) Time: 0.840 ms [local:/data/run/pg12]: 5120 pg12@testdb=#

After adding the column, the occupied space reached 651MB.

Delete column

[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 ('tweedrop')); pg_size_pretty-651 MB (1 row) Time: 1.788 ms [local:/data/run/pg12]: 5120 pg12@testdb=#

Delete the column, but the space is not freed.

Data dictionary

[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 | | | | notnull | atttypmod | integer | | notnull | attbyval | boolean | | notnull | attstorage | "char" | | notnull | attalign | "char" | notnull | attnotnull | boolean | | notnull | atthasdef | boolean | | notnull | atthasmissing | boolean | notnull | 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=# |

Looking at the data dictionary, it is found that the deleted C1 column becomes pg.dropped.2 and the logical mark is deleted.

Use vacuum/vacuum full to recycle space.

[local:/data/run/pg12]: 5120 pg12@testdb=# vacuum tours Time: 2510.368 ms [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT pg_size_pretty (pg_relation_size ('tweedrop')); 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 (00local:/data/run/pg12 07.997) [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT pg_size_pretty (pg_relation_size ('tweedrop')); pg_size_pretty-346 MB (1 row) Time: 1.258 ms [local:/data/run/pg12]: 5120 pg12@testdb=#

However, the data dictionary still retains the information about deleting columns.

[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=# |

Add columns to view data dictionary

[local:/data/run/pg12]: 5120 pg12@testdb=# ALTER TABLE t_drop ADD COLUMN C1 text DEFAULT md5 (random ():: text); ALTER TABLETime: 24483.254 ms (00text DEFAULT md5 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=#. I believe you have a better understanding of "what the column deletion operation is in PostgreSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!