溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“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中的刪除列操作是什么”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
